ERD Diagram For eCommerce Website (With Database Structure)

Welcome to a sharing of an ERD of an eCommerce website. So you are planning to build your own eCommerce website? Or just curious what goes on behind one? Well, here’s my “not a janky school project” version, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

 

E-COMMERCE WEBSITE ERD

Yikes. What in the world is this “brain damage system structure”? Keep calm and study slowly, I have divided this into 5 sections:

• Products & Categories
• Product Options
• Users
• Shopping Cart
• Orders

We will walk through these one by one in the next section.

 

FOR REFERENCE ONLY

Here’s a quick disclaimer before the trolls start screaming “this is not accurate” – This diagram is only for reference and it is subjective.

• If you enforce “a product must be attached to at least one category”, then it is a one-to-many relationship; If one product can only have one category, then it is one-to-one.
• An order must have at least one total entry – The grand total. If you decide to put all the totals in the orders table, feel free to remove the totals table.
• If you don’t need “product customizations”, remove all the product option tables.

Yes, you are building your own system here. Make changes and decide what works best for you.

 

 

 

PART 1) PRODUCTS & CATEGORIES

1A) PRODUCTS TABLE

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Primary key.
product_name	VARCHAR	The product name.
product_description	VARCHAR	Optional, item description.
product_price	DECIMAL	Product price.
product_status	VARCHAR	Product status.

To address the elephant in the room, an eCommerce website must have… products. This table should be pretty self-explanatory, but just a couple of extra notes here:

• The product status flag is optional, but it’s good to have. Use this to control items that are “OK” (can be ordered), “out of stock” (show item but cannot order), and “obsolete” (hide).
• Product prices are kind of tricky if you are dealing with multiple currencies. Personally, I just fix them to one currency. Most online payment gateways are capable of auto conversion anyway.
• Feel free to capture more information as required. E.G. Product image.

 

1B) CATEGORIES TABLE

COLUMN	DATA TYPE	NOTES
category_id	INTEGER	Primary key.
category_name	VARCHAR	Category name.
category_description	VARCHAR	Optional category description.

Another straightforward table. But if you want “sub-categories”, a possible way is to add a “parent ID” column… But beware, this can end up with endless loops and very deep sub-sub-sub-categories if you are not careful.

 

1C) PRODUCT TO CATEGORIES TABLE

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Composite primary key, foreign key.
category_id	INTEGER	Composite primary key, foreign key.

To tie products to categories.

 

 

PART 2) PRODUCT OPTIONS

2A) OPTION GROUPS & VALUES TABLES

COLUMN	DATA TYPE	NOTES
option_group_id	INTEGER	Primary key.
group_name	VARCHAR	Option group name.

COLUMN	DATA TYPE	NOTES
option_value_id	INTEGER	Primary Key.
value_name	VARCHAR	Option value name.

First off, what the heck are group and value? A few examples to explain that:

• Group – Size. Values – Small, Medium, Large.
• Group – Color. Values – Red, Green, Blue.
• Group – Shape. Values – Circle, Square.

Think you catch the drift.

 

2B) PRODUCT OPTIONS TABLE

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Composite primary key, foreign key.
option_group_id	INTEGER	Composite primary key, foreign key.
option_group_type	VARCHAR	Unique, user’s email address.

Attaches option groups to products, but take note of the “type” here. A few ideas of how you can set this:

• Must select one value from this group. E.G. Size. Cannot be both big and small at the same time?
• Can select multiple values from this group. E.G. Color. Maybe mix the colors that the customer has chosen.
• Optional to select a value from this group.
• No values – An open text field.

 

2C) PRODUCT VALUES TABLE

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Composite primary key, foreign key.
option_group_id	INTEGER	Composite primary key, foreign key.
option_value_id	INTEGER	Composite primary key, foreign key.

Attach values to product/group.

 

 

PART 3) SHOPPING CART TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Composite primary key and foreign key.
product_id	INTEGER	Composite primary key and foreign key.
product_options	VARCHAR	Chosen product options. Composite primary key and foreign key.
quantity	INTEGER	Quantity.

• The user ID and product ID are straightforward here. But what is difficult to deal with, are the product options. Personally, I like to JSON encode the selected options. For example group 1, values 2 and 3 – {"1":[2,3]}.
• Some people may prefer to keep the options in a separate table, which is OK by all means… But it’s just complicated.
• If you want to open the shopping cart to the general public:
  • Add a “cart ID”. Preferable VARCHAR, a random unique string.
  • Set cart ID, product ID, and product options as the primary key.
  • Change the user ID to a foreign key instead.

 

PART 4) USER TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary key.
user_role	VARCHAR	User’s role.
user_name	VARCHAR	User’s name.
user_email	VARCHAR	User’s email. Set this to unique to prevent duplicates.
user_passwrod	VARCHAR	Password, encrypt this in your own system.

Some other people may prefer to have different tables for customers and admin, but I don’t see the need. Just add a user role column to differentiate between “u”sers and “a”dmin. Done.

 

PART 5) ORDERS

5A) ORDERS TABLE

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Primary key.
user_id	INTEGER	Foreign key.
user_name	VARCHAR	Customer’s name.
user_email	VARCHAR	Customer’s email.

This is the “main orders table”, pretty self-explanatory. But for those of you who are thinking “isn’t the name and email redundant” – It’s not. Long story short, imagine that the name and email are not captured at the time of checkout:

• A user places a large order without paying.
• Then change the account name and email to prank a “friend”.

Call it learning from hard lessons. This is data integrity, not redundancy.

P.S. Add more fields as required – Billing address, shipping address, etc…

 

 

5B) ORDER HISTORY TABLE

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Composite primary key and foreign key.
history_time	DATE TIME	Composite primary key.
history_status	VARCHAR	Status code.
history_notes	VARCHAR	Optional notes.

To keep track of the order history.

• Invent your own status codes. Maybe pending, processing, en route, delivered, canceled, etc…
• Add the user ID if you want more accountability.

 

5C) ORDER TOTALS TABLE

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
order_id	INTEGER	Foreign key.
total_name	VARCHAR	Sub-total, grand total, discount, coupon, whatever.
total_amount	DECIMAL	Amount.

Some people may prefer putting the totals in the orders table itself, it is OK by all means once again. I just found it easier and better to create a separate totals table – Add another “total shortcode” column, and this turns into gold for data mining.

 

5D) ORDER PRODUCTS TABLE

COLUMN	DATA TYPE	NOTES
item_id	INTEGER	Primary key.
order_id	INTEGER	Foreign key.
item_name	VARCHAR	Product name.
item_price	DECIMAL	Product price, each.
item_quantity	INTEGER	Ordered quantity.

Now, take extra note that the product ID is not included in this table; In the diagram above, this table is not “directly linked” to products. Same old reason – We capture the product name and price at the time of writing. For those who are lost, imagine that we only capture the product ID here.

• The customer checks out a “T-Shirt”, product ID 888. All is good.
• The admin later changes product ID 888 to “Long Sleeve Shirt”.
• The customer gets the wrong product and is charged the wrong price.

Once again, learn from mistakes. Keeping the item name and price at the time of checkout is called “data integrity”.

 

 

5E) ORDER PRODUCT OPTIONS TABLE

COLUMN	DATA TYPE	NOTES
item_option_id	INTEGER	Primary key.
item_id	INTEGER	Foreign key.
option_group	VARCHAR	Option group name.
option_value	VARCHAR	Option group value.

Lastly, a table to store the selected product options. Take note, this is not “linked” to the option groups and option values tables – Data integrity.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart