Online Food Ordering System ERD (Simple Version)

Welcome to a quick sharing of an ERD of an online food ordering system. Need to build an online portal for a restaurant? Well, here is my own simple version of the system, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

 

ONLINE FOOD ORDERING SYSTEM

From the “color code”, let us generalize the system into 3 groups of entities:

• Users – The elephant in the room. The customers and restaurant staff.
• Products – Products (food items) that are available for order.
• Order – What the customers ordered.

 

RELATIONSHIP CARDINALITY

Before the toxic trolls start to scream “the diagram is not accurate” and “this is stupid”, here’s a quick disclaimer on the relationship between the entities – This is only for reference and it is subjective.

For example, some may prefer to separate “users” into “staff” and “customers”. This way, the complexity increases, and more rules have to be made. E.G. Staff accounts cannot purchase from the portal. Yep, you are building your own system now. Make your own changes and decide your own rules.

 

 

 

PART 1) USERS

1A) USERS TABLE

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary key.
user_name	VARCHAR	User’s full name.
user_email	VARCHAR	Unique, user’s email.
user_tel	VARCHAR	User’s telephone number.
user_password	VARCHAR	Password. Encrypt in your own system.
user_role	VARCHAR	User’s role. Invent your own – “C”ustomer, “M”anager, “S”taff.

Some people prefer to have separate tables for every single role. I find that kind of confusing… Why do that, when a single user_role field can get the job done?

 

1B) ADDRESS BOOK TABLE

COLUMN	DATA TYPE	NOTES
addr_id	INTEGER	Primary key.
user_id	INTEGER	Foreign key.
addr_name	VARCHAR	Address recipient – Person or company name.
addr_email	VARCHAR	Email address.
addr_tel	VARCHAR	Telephone number.
addr_street	VARCHAR	Full street address.

This is a small convenience to help the users fill in the “deliver to” form in your system. Take note that it is not linked to the orders table for a good reason – More on that later.

 

 

PART 2) PRODUCTS

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Primary Key.
product_name	VARCHAR	Name of the product.
product_img	VARCHAR	Image of the product.
product_desc	VARCHAR	Product description.
product_price	FLOAT	Product price.
product_status	VARCHAR	Product status.

• Take note, not linked to the order items table. For a good reason.
• I personally don’t like to delete items, it destroys data integrity… Thus the purpose of the product status flag. You can also use it in other ways – “A”vailable, “N”o stock, “F”eatured, “D”eleted.

 

PART 3) ORDERS

3A) ORDERS TABLE

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Primary Key.
user_id	INTEGER	Foreign Key.
user_name user_email user_tel user_address	VARCHAR	Customer’s name, email, telephone, and address.
bill_name bill_email bill_tel bill_address	VARCHAR	Billing name, email, telephone, and address.
ship_name ship_email ship_tel ship_address	VARCHAR	Shipping name, email, telephone, and address.

• Why are there 3 sets of addresses? Consider this:
  • John orders food for his client, but pays with his company credit card.
  • Customer – John. Bill To – John’s company. Ship To – John’s client.
• So why don’t we link the address book to orders? Why not use 3 address book id instead?
  • John registers his home address in the address book today – Let’s say address book ID 999.
  • John makes an order with it, no problem.
  • But John changes the address book entry one month later, huge problem if the system used address book ID as the foreign key.
  • That messes up all previous entries, and thus, recording 3 different sets of addresses at the time of checkout is not redundancy – It’s data integrity.

 

 

3B) ORDER ITEMS TABLE

COLUMN	DATA TYPE	NOTES
item_id	INTEGER	Primary key.
order_id	INTEGER	Foreign key.
product_name	VARCHAR	Product name.
product_price	FLOAT	Product price (each).
item_qty	INTEGER	Quantity ordered.

This table should be pretty self-explanatory. Also, to answer why this is not linked back to the products table – It’s the same reason as with address book entries.

• Product ID 999 is called “burger” today.
• 1 month later, an admin changed it to “fries”.
• If we used product ID as a key here, all the past entries will be messed up.
• So once again – Recording the product name and price at the point of checkout is for data integrity.

 

3C) ORDER TOTAL TABLE

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
order_id	INTEGER	Foreign key.
total_name	VARCHAR	Total name.
total_price	FLOAT	Total price.

This is yet another self-explanatory table. While we can manually re-calculate the totals from the items, it is better to keep a totals table – Tax rates can change over time, as with service charges, discounts, etc…

 

 

3D) ORDER HISTORY TABLE

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Primary key.
history_time	DATE TIME	Primar key, defaults to current time.
user_id	INTEGER	Foreign key.
history_status	VARCHAR	Status code. Invent your own – “P”reparing, “D”elivering, “S”uccessful, “F”ailed, “C”anceled.
history_notes	VARCHAR	Notes, if any.

Lastly, use this table to help keep track of “who did what” to the orders. If you want, you can also add delivery staff to the users table, and use this to track delivery.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart