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

 

ER DIAGRAM

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.

 

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.

 

 

TABLE DETAILS

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

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.

 

EXTRA BITS & LINKS

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

 

INFOGRAPHIC CHEAT SHEET

Food Ordering System ERD (click to enlarge)

 

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. Required fields are marked *