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
First, here is the entire ER diagram and a quick overview of the system.
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.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
1A) USERS TABLE
|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
|addr_name||VARCHAR||Address recipient – Person or company name.|
|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
|product_name||VARCHAR||Name of the product.|
|product_img||VARCHAR||Image of the product.|
- 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
|VARCHAR||Customer’s name, email, telephone, and address.|
|VARCHAR||Billing name, email, telephone, and 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
|product_price||FLOAT||Product price (each).|
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
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
|history_time||DATE TIME||Primar key, defaults to current time.|
|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.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
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!