In relational database design, a relationship is where two or more tables are linked together because they contain related data. This enables users to run queries for related data across multiple tables.

Relationships are a key element in relational database design.

Here’s an example:

In the above example, the City table has a relationship with the Customer table. Each customer is assigned a city. This is done by using a CityId field in the Customer table that matches a CityId in the City table.

While it’s certainly possible to store the full city name in the Customer table, it’s better to have a separate table that stores the city details. You can easily use a query to look up the CityName by using the CityId that’s stored for that customer.

Primary Key vs Foreign Key

Relationships are achieved using a primary key and a foreign key.

In the above example, City.CityId is the primary key, and Customer.CityId is the foreign key.

The primary key is a unique identifier. Because it is unique, the foreign key can reference it, with complete confidence that it is referencing only one record.

So in a relationship, the foreign key in one table matches the primary key in the other table. That is, any value in the foreign key field should have a matching value in the primary key field in the referenced table. The foreign key and primary key fields should also have the same data type (with a few possible exceptions).

A common convention is to give the foreign key field the same name as the primary key field that it references, but this is not a requirement.

Creating a Relationship

Once you’ve created the tables, you then need to establish the relationship. Most relational database management systems will allow you to create a relationship via the GUI. However, you can also do it using programmatically using SQL.

Types of Relationships

There are three types of relationships in database design:

• One-to-One: A row in table A can have only one matching row in table B, and vice versa.
• One-to-Many (or Many-to-One): A row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
• Many-to-Many: A row in table A can have many matching rows in table B, and vice versa.

What are the Benefits of Relationships?

Relationships are the basis of any relational database management system (RDBMS). Relationships are a very powerful tool to use in database design.

Here are some key benefits of relationships in database design:

• Reduces storage requirements
• Helps maintain data integrity (in particular, referential integrity)
• Helps increase usability for end users
• Easier data maintenance
• Helps with security
• Helps with scalability or expansion of the database

These are explained in more detail below.

Reduces storage requirements

Storing an ID is typically more efficient than storing the full text – especially if the text is long.

Helps maintain data integrity

By entering the data once, then referencing that one record, there is less room for error.

In the above example, if there was no relationship between the Customer and City tables, users would need to enter the full city name every time they add or update a customer.

This could get messy, fast. You would most likely end up with a table full of spelling mistakes, as well as capitalisation issues. One record might have Copenhagen, another Copenhagan, another COPINHAGEN, etc etc. Imagine trying to query that? If you did a query for all customers living in ‘Copenhagen’ you would miss a lot of customers.

Furthermore, you could have many tables that require city information to be stored. So, not only would you have ‘Copenhagen’ (and its misspellings) duplicated within the Customer table, you would also have the same problem duplicated throughout other tables.

Not good data integrity.

By using a relationship, you can avoid those types of headaches.

Helps increase usability for end users

By creating a relationship, you can now provide users with a widget for selecting the desired option (a drop down/combo box for example).

So, instead of having to type the full city name, they simply select the city from the drop down list.

You can do this easily, by populating the drop down list with the contents of the table.

Easier data maintenance

Updates to data only need to be done in one place. For example, if a city’s name changes (yes, it does happen), you can update it once – in the City table.

You won’t need to update thousands, or even millions, of records that hold city information, because they only store the CityId. And because the CityId stays the same, you won’t even need to look at the Customer table or any other table.

Helps with security

Sensitive data can be stored in a table that has certain privileges applied. When a user logs in, the system provides users with access to only those tables that they’re allowed to have access to.

For example, a receptionist might be able to see other employees’ details but not their salary. However, the pay clerk would have access to their salary.

Helps with scalability or expansion of the database

Having certain data in a separate table allows you to add records that aren’t necessarily needed now, but may be needed in the future. For example, you could add new cities to the City table even if no other record references it yet.