Online Clothing Store ERD (With Database Structure)

Welcome to a sharing of an ERD for an online clothing store. So you have picked up a project for a clothing store? Or maybe you are stuck with this as a school assignment? Well, here is a sharing of my own simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

CLOTHING STORE ERD

To keep things simple, I have grouped this system into 3 sections.

• Users – I don’t think this needs an explanation. Staff and customers.
• Products – Clothes and apparel available for sale.
• Sales – Orders and order history.

 

FOR REFERENCE ONLY

I can hear the “keyboard-banging experts” – “This is not accurate”, “dumb, worst”, “no good”. By any means, this is only a simple example for your reference.

• All sales will have one history entry upon checkout. If that is not the case, change it on your own.
• All sales will have multiple total entries – Subtotal, tax, grand total. If that is somehow not the case, change it.
• All sales will have at least one item. Change it if you allow “special orders” with no items.
• If you want to track stock levels, go ahead and add another “stock movement”.

Yep, you are building your own system. You have to decide what is “clever” and “makes sense” for yourself.

 

 

 

PART 1) USERS

1A) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_type	VARCHAR	Up to you to decide – “Admin”, “Manager”, “Delivery”, etc…
staff_name	VARCHAR	Full name.
staff_email	VARCHAR	The user’s email. You can set this to unique to prevent duplicates.
staff_password	VARCHAR	Login password.

Captain Obvious to the rescue – The internal staff members.

 

1B) CUSTOMER

COLUMN	DATA TYPE	NOTES
customer_id	INTEGER	Primary key.
customer_name	VARCHAR	Full name.
customer_email	VARCHAR	The customer’s email. You can set this to unique to prevent duplicates.
customer_tel	VARCHAR	Customer’s contact number.
customer_address	VARCHAR	Customer’s address.
customer_password	VARCHAR	Login password.

I usually like to keep a single “users” table with a “user type” field. But we keep customers in a separate table now, because they have a different dataset.

P.S. You may want to create another “address book”, customers may have multiple addresses – Home, office, billing, PO box, etc…

 

 

PART 2) PRODUCTS

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Primary key.
product_name	VARCHAR	Product name.
product_description	VARCHAR	Product description.
product_price	DECIMAL	Price each.
product_stock	INTEGER	Remaining stock on hand.

To address the elephant in the room, products that are available for sale. Some people may be wondering, how about things like “size” and “color”? The simplest I can think of is another “variations” table:

• Product ID (PK FK)
• Group (PK) – Color, size, pattern, etc…
• Option (PK) – Red, green, blue, S, M, L, etc…

But yep, that will add quite a bit of complexity. So let’s just keep things simple here.

 

PART 3) SALES

3A) SALES

COLUMN	DATA TYPE	NOTES
sales_id	INTEGER	Primary key.
sales_date	DATE TIME	Timestamp of sale.
sales_status	VARCHAR	Up to you to decide – “pending”, “canceled”, “delivered”, etc…
customer_id	INTEGER	Foreign key, the customer making the purchase.
customer_name	INTEGER	Customer’s name.
customer_email	VARCHAR	Customer’s email.
customer_tel	VARCHAR	Customer’s contact number.
customer_address	VARCHAR	Delivery address.

The “main sales table”, just a few small notes here:

• We are keeping the customer’s name, email, tel, and address for the sake of data integrity. The customer can change the account particulars later, but orders will always be “accurate at the time of checkout”.
• You may want to add more sets of information for “bill to” and “deliver to”. Customers may purchase using their company’s credit card, but deliver to another client as a gift.

 

 

3B) SALE ITEMS

COLUMN	DATA TYPE	NOTES
sales_id	INTEGER	Primary key and foreign key.
product_id	INTEGER	Primary key and foreign key.
product_name	VARCHAR	Product name.
product_price	DECIMAL	Price each.
product_qty	INTEGER	Quantity.

Items ordered. Once again, we are keeping the product name and price, to make sure that they are “accurate at the time of checkout”.

 

3C) SALE TOTALS

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
sale_id	INTEGER	Foreign key.
total_name	VARCHAR	Sub-total, discount, tax, voucher, grand total, etc…
total_amount	DECIMAL	Total amount.

For those who are thinking “We can calculate the totals using the product price above” – There’s nowhere for you to store the tax, discount, vouchers, etc…  We need a totals table.

 

3D) SALE HISTORY

COLUMN	DATA TYPE	NOTES
sales_id	INTEGER	Primary and foreign key.
history_date	DATE TIME	Primary key, timestamp.
history_status	VARCHAR	Up to you to decide – “pending”, “canceled”, “delivered”, etc…
history_notes	VARCHAR	Notes, if any.
staff_id	INTEGER	Foreign key, if any. Staff who attended made this history entry.

Finally, a “history” table to track everything related to the order – Payment, packing, delivery, cancellations, etc…

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart