SQL Relationships
In this lesson, you will learn how to distribute data over several tables.
Why Should Not Store All Data In One Table?
Imagine that you want to store all the customers' information and their orders in one table. Notice how many NULL fields and repeated data you will have in every row.
| person_id | first_name | last_name | order_number | product_1_name | product_1_price | product_2_name | product_2_price | product_3_name | product_3_price | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Adam | Smith | adam.smith@example.com | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | Adam | Smith | adam.smith@example.com | 100 | Computer Screen | 160 | NULL | NULL | NULL | NULL |
| 1 | Adam | Smith | adam.smith@example.com | 215 | Keyboard | 10 | Mouse | 7 | NULL | NULL |
In general, the above table design is very bad because it contains a lot of repeated data and NULL values in every row.
This design has a lot of problems that you may not recognize now like:
- If you want to update any information, you have to update it in all rows that is mentioned in it.
- Consuming more storage space than the actual need.
- Slow server responses on searching, updating and deleting operations.
- Some limitations, for example, you cannot use unique on unique fields because the data must be repeated in all rows.
Do not ever think about putting all the project data into one table. Rather, always think that you must distribute the project data into several tables in a logical and organized manner.
How Tables Can Be Related?
To link the data placed in a table with the data placed in another table, we usually depend on the primary key or the unique column that exists in the table that has the data.
Suppose we want to store users' data and we want to know from which country is each one. Here to avoid duplicate country names, we should store countries in a specific table. Then each user can store the id of the country that he/she is from.
The records in the users table can be read as follows:
- Emily Martin is from the country that has an id equal to 1 which is Australia.
- Ayman Mostafa is from the country that has an id equal to 3 which is Lebanon.
- Daniel Schmidt is from the country that has an id equal to 2 which is Germany.
- Sarah Johnson is from the country that has an id equal to 1 which is Australia.
- Lily Laurent is from the country that has an id equal to 3 which is Lebanon.
Users and countries tables can be joined together to display the country name for each user.
The Benefits Tables Relationships
Through the relationship that we made between the users and countries tables, we can discover the following benefits.
1- No Redundant Data
Users and countries tables have no duplicated data.
2- Updates Are Applied For All
If you update any information in the countries table, it will automatically change for any table related to it.
Suppose we update these values in the countries table:
- Lebanon → Egypt
- Lebanese Pound → Egyption Pound
Note how these changes are applied automatically for the users.
3- New Data Automatically Added For Existing Records
If we add a new column in the countries table, it will be automatically known to the records in the users tables.
Suppose we add a new column called "symbol" in the countries table as follows.
Now, without any change in the users table, we can say that Emily Martin's currency symbol should be displayed as AUD.
4- Data Integrity
If we set a foreign key constraint on the country_id field in the users table and link it to the id field in the countries table, that will guarantee that only the id values in the countries table can be used as values for the country_id field in the users table.
You learn how to add a foreign key constraint later in this tutorial.
How To Display Values Placed In Several Tables Into One Table
When storing data, we saw how we could store it in separate tables in a logical and organized manner, free of any repetition.
Now, you may wonder how we can display these data as one table. Simply, by using a SELECT statement we can specify the names of the tables and columns from which we will fetch the values and mention the names of the columns that logically connect the tables.
So, users and countries tables data can be retrieved as one table like the following.
| id | first_name | last_name | country |
|---|---|---|---|
| 1 | Emily | Martin | Australia |
| 2 | Ayman | Mostafa | Egypt |
| 3 | Daniel | Schmidt | Germany |
| 4 | Sarah | Johnson | Australia |
| 5 | Lily | Laurent | Egypt |
In the next lesson, you will learn about the types of relationships between tables.
After that, you will learn how to link shared values distributed across several tables when retrieving them.
