DB Design #2 - Table Relationships
Establishing table relationships and understanding keys are the next important steps after entity segregation.
Welcome to the second post of the #DBDesign series!
In our previous post, we discussed requirement analysis and entity segregation. We also established a scenario for which we will be working on a database model in the DBDesign series.
In this week’s article, we will look into establishing table relationships. We will also alter our DB model implementing what we discuss about these concepts.
All-in-one table
This is the All-in-one table that we designed before entity segregation.
The parts of the table highlighted in green have duplicate data stored repeatedly. This data is redundant and uses excessive memory.
By the end of the previous article, we segregated the entities for our scenario into the following:
Customers
CustomerDetails
Products
Product categories
Orders
OrderDetails
Payments
Example of an entity table
Let us see the Customers entity table to understand how it will help us in reducing redundancy.
This is our Customers table which is a separate entity.
We will keep adding new rows to this table when a new customer signs up.
The “customerId” is our Primary Key. This key will be unique for each customer.
We can now eliminate redundant customer data like name, location, and emailId from other tables. Note that we will have a lot of information about a customer and this is just a subset of what we will actually have.
In our ORDERS table, we will have only “customerId” row. We have to find a way to link that customerId to the corresponding customer data in our entity table.
Similarly we will have all other entity tables defined with primary keys.
Table relationships
Before we look at table relationship, check out the “All-in-one” table that we had in my previous article here.
Once we segregate our entities and define them, we have to find a way to establish a relationship between them.
As the name suggests, relational databases have table relationships which enable us to effectively scale our data and reduce redundancy.
Let us look at the types of table relationships that we can have in relational databases.
Types of table relationships
There are three major types of table relationships we can establish.
One to One
One to Many
Many to Many
One to One relationship
In a one-to-one relationship, each record in one table is directly related to exactly one record in another table.
This relationship is typically achieved by referencing the primary key of one table as a foreign key in the other table.
One-to-one relationships are often used to break down large tables into smaller, more manageable parts.
Example:
In our scenario, we can create a table dedicated for all information about a customer. So when we need to fetch data like a customer’s email ID, address, date of birth, etc. of a particular customer, we can reference it using the Primary key of the customers table — customer_id.
Each customer can have only one corresponding set of information and vice versa.
Here, the primary key of the "Customer" table (e.g., customer_id) becomes the foreign key in the "CustomerDetails" table, establishing a one-to-one relationship.
One to Many relationship
In a one-to-many relationship, single record in a table can be associated with multiple records in another table.
This relationship is achieved by referencing the primary key of the "one" table as a foreign key in the "many" table.
Many-to-one relationships are common when modeling hierarchies or when multiple entities share a common attribute.
Example:
Let's consider two tables, "Customers" and "Orders." Multiple orders can be placed by a single customer, but each order can have only one customer associated with it.
The primary key of the "Customers" table becomes the foreign key in the "Orders" table, establishing a one-to-many relationship.
Many to Many relationship
In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table.
This relationship requires the use of an intermediary table, often called a junction table or an associative table.
The junction table contains foreign keys referencing the primary keys of the two related tables, allowing for the establishment of a many-to-many relationship.
Example:
Consider two tables, "Orders" and "Products." Multiple products can be bought in a single order, and a single product can be present in multiple orders.
To represent this relationship, a junction table called "OrderProducts" is created, which contains the foreign keys referencing the primary keys of the "Orders" and "Products" tables.
The "OrderProducts" table serves as a bridge between the two, facilitating the many-to-many relationship.
Hope you got an understanding of the different methods to establish table relationships. With this, we can come up with an overview of all the tables with their relationships.
Next week, we’ll dive deeper into types of SQL keys that can be used to optimise our database.
Until next time!