What are Database Relationships? How we represent the link between database tables using an Entity Relationship Diagram

database relationships

Home > database design > relationships

To join related tables in an Entity Relationship diagram we use a straight line. If we have two tables and one table contains the key from another table (the primary key) then we draw a line linking tables.

First we can determine if there is an association between entities, e.g.

  • students take courses
  • courses contain students

So we know there exist student table relationships with other tables that include the course. How do the student and course tables relate to each other? Two entities are related by the degree

  • a course contains one or more students
  • an instructor is assigned one and only one department

Lets see what types of database relationships exist.

Type of Relationship

How the tables relate to each other is called the cardinality of a relationship. Lets explain.

One-to-many relationship

The most common type of database relationship between entities is called a one-to-many relationship.

The first table is connected to the second table by one line. Sometimes the number one is also used to show the is the ‘one’ in the ‘one-to-many’.

The second table has three lines like a crow’s foot. This represents the ‘many’.

We can check this database relationship by going from one table to the other both ways

one-to-many relationship between two entities

Good Design: One to Many Relationship

  • Look at the relationship both ways
  • Each and every lecturer is assigned to one and only one department
  • Each and every department contains one or many lecturers
  • This is a 1 to many (1:M) relationship (this is good!)
a one to many relationship example

Zero-to-many relationship

A database relationship that is similar to the one-to-many is the zero-to-many.

This is the same ‘many’ with the three-line crow’s foot, but the first table has a circle and often the number 0.

A example of the difference would be a customer-order relationship. It  could be 1:M (one-to-many) as customers can have many orders.

But a customer could have no orders so the design can show that a customer could exist without any orders.

zero to many relationship in a entity relationship diagram

One-to-one relationship

We don’t use one-to-one database relationships in database design as it could be one table if this is possible.

In practice, sometimes when we implement a database we can create lists that we want to keep separate. For example, provinces or counties have a one-to-one relationship with their country.

one-to-one relationship between two entities

Many-to-many relationship

Although these are problematic, many-to-many database relationships are very common.

We represent each side of our relationship line with a crow’s foot (three lines).

many-to-many relationship between two entities

Bad Design: Many to Many Relationship

Many students take many courses. So we can’t store the courses as part of the student table, or students as part of the course table.

But if we try to add the course  primary key into the student table as a foreign key, or voce versa, we will encounter design issues.

This is the number one design issue with database relationships.

many-to-many relationship between two entities

Solution: A junction table

We will describe junction tables in detail later but for now we deal with the many-to-many problem by creating another table.

For example, when a student joins a course they ‘register’ for the course. So we can create a registration table.

In design terms we have solved the problem as now we have two ‘one-to-many’ relationships.

  • student to registration one-to-many relationship
  • course to registration one-to-many relationship
junction table in an entity relationship diagram

Database Design Summary

  • identify and model entities  (tables)
  • identify and model attributes  (columns)
  • identify unique identifiers  (for each table)
  • identify and model relationships  (between tables)

resolve any design issues

  • use junction tables rather than Many-to-Many
  • if you have 1 to 1, then do you need it?
  • entities  ->  tables
  • attributes  ->  columns
  • unique identifiers  ->  primary keys
  • relationships  ->  foreign keys

Database Design Implementation

  • no spaces in names  
    • (SQL doesn’t like them!)
  • keys – use INT type 
    • faster
    • reduce errors, consistent format (10-digit phone number)
    • can use CHAR for small/appropriate tables (e.g. CA,AL,AK)
  • text
    • VARCHAR(50) preferred

Table and column names

  • must start with a letter
  • followed by letters, numbers, or underscores
  • not to exceed a total of 30 characters in length
  • not SQL reserved keywords
  • e.g. “select”, “create”, “insert”, etc.

TASK 3

Design a Database

your task is to create an Entity Relationship (ER) Diagram

  • create entities, attributes and relationships
  • create your ER diagram
    • start with your tables / entities
    • start with your column names / attributes
    • underline primary keys
  • relationships
  • also consider
    • data types (integer, date, varchar)
    • any restraints (set amount >= 0)
      • you are allowed to use AI to create test data
  • create test data
  • to see if your data types are correct
  • save test data in excel

could you create an ER diagram for a business, like an online store ?

end of lesson

return to the main page on relational database design

FAQ on Table Relationships

What are the three types of database relationships?

The three main types of database relationships are:

  1. One-to-One: One record in the first table relates to only one record in the second table.

  2. One-to-Many: One customer in the customers table can place many records in the orders table. This is the most common relationship in a relational database.

  3. Many-to-Many: Many records in one table are linked to many records in another table. This usually needs a third table, often called a junction table, to connect them.

What are the 4 types of relationship in DBMS?

In some database management system documentation, relationships are described in four types:

  1. One-to-One relationship

  2. One-to-Many relationship

  3. Many-to-Many relationship

  4. Self-Referencing relationship (a record in one table relates to another record in the same table, sometimes called recursive).

What are the key relationships in database design?

The key relationships in database design are one-to-one, one-to-many, and many-to-many. These defined relationships are forced by business rules and flow naturally from the data. For example, a single product may appear in many records in the orders table, while one customer can place many orders. These relationships make sure data is organized correctly across different tables and maintain data integrity.

What are the 4 types of database?

The four types of databases are:

  1. Relational databases (such as MySQL, SQL Server, or SQLite) where data is stored in tables and relationships define how tables connect.

  2. Document databases (store data as JSON or XML documents).

  3. Key-value databases (store data using a unique identifier for each record and its value).

  4. Graph databases (focus on associations between tables and records, often used for data that is highly connected).

Why do we need relationships between tables in a database?

Relationships between your tables ensure data integrity and prevent redundancy. Without relationships, the same data might be stored in different tables, leading to errors. For example, linking the customers table and the orders table avoids repeating customer information in every order record.

What is a junction table in database design?

A junction table is a third table that connects two other tables in a many-to-many relationship. For example, a products table and an orders table can be linked with an order details table. Each record in the junction table connects one record in the first table (the product) with one record in the second table (the order).

How does normalization affect relationships?

Normalization is the process of organizing data into different tables to reduce redundancy and ensure data integrity. Relationships define how these tables connect. For example, instead of keeping customer details in every order row, normalization separates them into a customers table and relates them to many records in the orders table using a foreign key.

What is referential integrity in database relationships?

Referential integrity means that every record in the second table must match a valid record in the first table. For example, every record in the orders table must match one customer in the customers table. This rule, supported by a database management system like MySQL or SQL Server, ensures data accuracy throughout the database.

How do business rules affect database relationships?

Business rules define how data is related. For example, a customer can place many orders, but each order relates to only one customer. These rules are part of data modeling and schema design. They help you create a relationship that matches real-world needs and ensures data is consistent across different tables.

What are best practices for handling relationships in a relational database?

  • Always define relationships clearly in your schema.
  • Use primary keys and foreign keys to connect tables in your database.
  • Avoid tables without relationships unless absolutely necessary.
  • Normalize your design to prevent redundant data.
  • Test queries that pull data from multiple tables to make sure the overall database structure works.
  • Follow documentation, such as IBM documentation or MySQL guides, for database configuration application and management systems.

Leave a Reply