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

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

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 this 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.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

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

Option Groups
COLUMN DATA TYPE NOTES
option_group_id INTEGER Primary key.
group_name VARCHAR Option group name.
Option Values
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.

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.