Database Relationships

Database Relationships are the Backbone of all Relational Databases

A relationship is established between two database tables when one table has a foreign key that references the primary key of another table. This is the basic concept behind the term relational database.

How a Foreign Key Works to Establish a Relationship

Let's review the basics of primary and foreign keys. A primary key uniquely identifies each record in the table. It is a type of candidate key that is usually the first column in a table and can be automatically generated by the database to ensure that it is unique.

A foreign key is another candidate key (not the primary key) used to link a record to data in another table.

For example, consider these two tables that identify which teacher teaches which course. 

Here, the Courses table's primary key is Course_ID. Its foreign key is Teacher_ID:

Courses
Course_IDCourse_NameTeacher_ID
Course_001BiologyTeacher_001
Course_002MathTeacher_001
Course_003EnglishTeacher_003

 

You can see that the foreign key in Courses matches a primary key in Teachers:

Teachers
Teacher_IDTeacher_Name
Teacher_001Carmen
Teacher_002Veronica
Teacher_003Jorge

 

We can say that the Teacher_ID foreign key has helped to establish a relationship between the Courses and the Teachers tables.

Types of Database Relationships

Using foreign keys, or other candidate keys, you can implement three types of relationships between tables:

One-to-one: This type of relationship allows only one record on each side of the relationship.

The primary key relates to only one record — or none — in another table. For example, in a marriage, each spouse has only one other spouse. This kind of relationship can be implemented in a single table and therefore does not use a foreign key.

One-to-many: A one-to-many relationship allows a single record in one table to be related to multiple records in another table.

Consider a business with a database that has Customers and Orders tables. A single customer can purchase multiple orders, but a single order could not be linked to multiple customers. Therefore the Orders table would contain a foreign key that matched the primary key of the Customers table, while the Customers table would have no foreign key pointing to the Orders table.

Many-to-many: This is a complex relationship in which many records in a table can link to many records in another table. For example, our business probably needs not only Customers and Orders tables, but likely also needs a Products table. Again, the relationship between the Customers and Orders table is one-to-many, but consider the relationship between the Orders and Products table. An order can contain multiple products, and a product could be linked to multiple orders: several customers might submit an order that contains some of the same products. This kind of relationship requires at minimum three tables. 

What are Database Relationships Important?

Establishing consistent relationships between database tables helps ensure data integrity, contributing to database normalization. For example, what if we did not link any tables through a foreign key and instead just combined the data in the Courses and Teachers tables, like so:

Teachers and Courses
Teacher_IDTeacher_NameCourse
Teacher_001CarmenBiology, Math
Teacher_002VeronicaMath
Teacher_003JorgeEnglish

 

This design is inflexible and violates the first principle of database normalization, First Normal Form (1NF), which states that each table cell should contain a single, discrete piece of data.

Or perhaps we decided to simply add a second record for Carmen, in order to enforce 1NF:

Teachers and Courses
Teacher_IDTeacher_NameCourse
Teacher_001CarmenBiology
Teacher_001CarmenMath
Teacher_002VeronicaMath
Teacher_003JorgeEnglish

 

This is still a weak design, introducing unnecessary duplication and what is called data insertion anomalies, which just means that it could contribute to inconsistent data. For example, if a teacher has multiple records, what if some data needs to be edited, but the person performing the data editing does not realize that multiple records exist?

 The table would then contain different data for the same individual, without any clear way to identify it or avoid it.

Breaking this table into two tables, Teachers and Courses (as visualized above), creates the proper relationship between the data and therefore helps ensure data consistency and accuracy.