ER Model to Relational Database
In the previous cheatsheet, we've learnt to build an ER model for a given scenario. Now, let's convert this ER model to Relational Database. Let's consider the same e-commerce application.
E-commerce Application
In a typical e-commerce application,
- Customer has only one cart. A cart belongs to only one customer
- Customer can add products to cart
- Cart contains multiple products
- Customer can save multiple addresses in the application for further use like selecting delivery address
Entity Type to Table
Primary key: A minimal set of attributes (columns) in a table that uniquely identifies rows in a table.
In the following tables, all the ids are primary keys as they uniquely identify each row in the table.
Relationships
Relation Between Customer and Address - One to Many Relationship
- A customer can have multiple addresses.
- An address is related to only one customer.
We store the primary key of a customer in the address table to denote that the addresses are related to a particular customer. This new column/s in the table that refer to the primary key of another table is called Foreign Key.
Here,
Relation Between Cart and Customer - One to One Relationship
- A customer has only one cart.
- A cart is related to only one customer.
This is similar to one-to-many relationship. But, we need to ensure that only one cart is associated to a customer
Relation Between Cart and Products - Many to Many Relationship
- A cart can have many products.
- A product can be in many carts.
Here, we cannot store either the primary key of a product in the cart table or vice versa. To store the relationship between the cart and product tables, we use a Junction Table.
E-commerce Usecase: ER Model to Relational Database
Following ER model is represented as the below tables in the relational database.
ER Model
Relational Database
