Welcome to a sharing of an ERD of a restaurant management system. Now, a “restaurant” can cover a lot of things – Customers, staff, orders, transactions, menu, and more. So instead of coming up with another “complex ERD”, here is a sharing of my own simple version – Read on!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
RESTAURANT MANAGEMENT SYSTEM ERD
To keep things simple, I have grouped this system into 4 sections.
- Menu Items – To address the elephant in the room, every restaurant definitely has a menu.
- Users – Staff and customers.
- Orders – “Sales”.
- Transactions – Somewhat optional, as this crosses into a “financial system”. Good to have nonetheless.
FOR REFERENCE ONLY
I can hear the “experts” banging on their keyboards. “This is inaccurate”, “this is so dumb”, “I can do better”. By all means, this ERD is only a simple example for reference.
- Every order will have at least one history entry – Be it “pending online payment” or “walk-in order”. If that is not the case, change it by yourself.
- An order must have at least one item. If you allow “special empty orders”, go ahead and change it yourself.
- If your menu items can be “customized”, go ahead and add another “item option”.
- If you are running a chain restaurant, go ahead and include “locations”.
- You can also add “discount coupons” if you want.
Yes, you are making your own system now – You have to 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
|user_type||VARCHAR||Up to you to decide – “A”dmin, “S”taff, “D”elivery, “C”ustomer, etc…|
|user_name||VARCHAR||The user’s full name.|
|user_email||VARCHAR||The user’s email. Set this to unique to prevent duplicates.|
|user_tel||VARCHAR||The user’s telephone number.|
|user_password||VARCHAR||The user’s password.|
Some people like to create multiple “user tables” – Admin, customer, manager, staff, delivery, etc… Now, that only makes sense if all of these have different datasets. Otherwise, it is much easier to just stick with “user type”.
PART 2) MENU ITEMS
|item_name||VARCHAR||Name of the menu item|
|item_description||VARCHAR||A description of the menu item.|
|item_image||VARCHAR||Image of the menu item.|
|item_price||VARCHAR||Price of the item.|
Don’t think this needs an explanation. Menu items that the restaurant offers.
PART 3) ORDERS
3A) “MAIN ORDERS”
|customer_id||INTEGER||Foreign key, user ID. Take note that this is “optional”, walk-in customers may not have a user ID… Unless it’s a “member’s only” restaurant.|
|customer_name||VARCHAR||Customer’s name, if any.|
|customer_email||VARCHAR||Customer’s email, if any.|
|customer_address||VARCHAR||Delivery address, if any.|
|order_type||VARCHAR||Up to you to decide. Maybe “w”alk-in, “t”ake away, “o”nline, “d”elivery.|
|order_status||VARCHAR||Up to you to decide. Maybe “pending” payment, “processing”, “delivered”, “canceled”.|
Orders should be pretty self-explanatory too. Some of you sharp code ninjas will be thinking “why are recording the customer’s name and email, isn’t it redundant”? No, this is “data integrity”. Even if the customer changes the account name/email later, we will use the particulars at the time of checkout.
P.S. You may want to add more fields for “bill to”. That is, a customer may be ordering on behalf of a company, paying with the company’s credit card. The invoice will be in the customer’s name but billed to the company.
3B) ORDER ITEMS
|order_id||INTEGER||Primary and foreign key.|
|item_id||INTEGER||Primary and foreign key.|
|item_name||VARCHAR||Name of the item ordered.|
Items ordered. Once again, we are recording the item name and price for “data integrity”.
3C) ORDER TOTALS
|total_name||VARCHAR||Name of the total entry. For example, sub-total, discount, grand total.|
|total_amount||DECIMAL||Total amount. Can be negative for discounts and rebates.|
Yes, we can calculate the totals from the order items. But that does not include things like discounts, taxes, and service charges. Thus, this is necessary.
3D) ORDER HISTORY
|order_id||INTEGER||Primary and foreign key.|
|history_time||DATE TIME||Primary key, timestamp of the history entry.|
|history_status||VARCHAR||As “main orders” above, up to you to decide.|
|history_notes||VARCHAR||Notes, if any.|
|staff_id||INTEGER||Related user ID, if any. For example, cashier who created the order, delivery staff who delivered the order, manager who canceled the order, etc…|
The “timeline” of orders, good for data mining – How fast are orders being prepared, delivered, how many cancellations, etc…
PART 4) TRANSACTIONS
|tx_id||INTEGER||Primary key. Transaction ID.|
|order_id||INTEGER||Foreign key. Related order, if any.|
|staff_id||INTEGER||Foreign key. Related staff, if any.|
|tx_time||DATE TIME||Transaction timestamp.|
|tx_type||VARCHAR||Up to you to decide – “cash”, “credit card”, “online”, “voucher”.|
|tx_notes||VARCHAR||Notes, if any.|
Finally, a restaurant is not just about “items and orders”. There are many other things – Buying ingredients, staff salary, maintenance, utility bills, waste management, and more. Yep, this crosses into “bookkeeping and finance”. Feel free to omit this if the restaurant has its own accounting software.
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
- 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!