Restaurant Management System ERD (With Database Structure)

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

 

 

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.

 

 

 

PART 1) USERS

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary key.
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

COLUMN	DATA TYPE	NOTES
item_id	INTEGER	Primary key.
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”

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Primary key.
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

COLUMN	DATA TYPE	NOTES
order_id	INTEGER	Primary and foreign key.
item_id	INTEGER	Primary and foreign key.
item_name	VARCHAR	Name of the item ordered.
item_price	VARCHAR	Price each.
item_quantity	INTEGER	Quantity ordered.

Items ordered. Once again, we are recording the item name and price for “data integrity”.

 

3C) ORDER TOTALS

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
order_id	INTEGER	Foreign key.
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

COLUMN	DATA TYPE	NOTES
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

COLUMN	DATA TYPE	NOTES
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_amount	DECIMAL	Transaction amount.
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.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart