Cyber Cafe Management System ERD (With Database Structure)

Welcome to a sharing of an ERD for a cyber cafe management system. So you are working on a project for a cyber cafe? Or maybe got this one as a school project? Well, here is a sharing of my own simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

CYBER CAFE MANAGEMENT SYSTEM ERD

To keep things simple, I have grouped this system into 3 sections.

• Users – The internal staff members and customers.
• Stations – Computers and their usage.
• Sales – Because a cyber cafe can sell other stuff such as food, drinks, games, and merch.

 

FOR REFERENCE ONLY

I can hear angry banging on keyboards – “Stupid dumb worst”, “this is not accurate”, “I can do better”. By any means, this is only a simple example. This is based on the assumption of a cyber cafe that is “walk-in only”, and not operating online.

• If you want to open for online registration and booking – Go ahead and make your own changes.
• If you want to “link” usage to sales, go ahead and do it.
• If you want to add “branches”, then do it.
• Sales will definitely have at least one item. If you want to allow “special sales with no items” for some reason, go ahead and change it.
• Lawfully and logically speaking, a sale will have multiple total entries – Sub total, tax, discount, vouchers, service charge, grand total. If that is somehow not the case, change it.

You are building your own system now, you have to decide your own rules and make the “smart choices”.

 

 

 

PART 1) USERS

1A) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_name	VARCHAR	Full name.
staff_email	VARCHAR	Email. Set this to unique to prevent duplicate registrations.
staff_status	VARCHAR	Up to you to decide – “Admin”, “Manager”, “Cashier”, “Staff”, “Left”, etc…
staff_password	VARCHAR	Login password.

The internal staff members. Some people like to create different tables for admin, manager, cashier, staff, and more… Don’t do that. Unless all of them have different datasets, just stick with a single “staff type” or “staff status” field.

 

1B) MEMBERS

COLUMN	DATA TYPE	NOTES
member_id	INTEGER	Primary key.
member_name	INTEGER	Customer’s name.
member_email	VARCHAR	Customer’s email.
member_tel	VARCHAR	Customer’s telephone.
member_since	DATE TIME	Member since.

Take note – Member registration is optional, there is no login password. Use this for things like “reward points” and newsletters, but remember to give members a choice to opt out.

 

 

PART 2) COMPUTER STATIONS

2A) STATIONS

COLUMN	DATA TYPE	NOTES
station_id	INTEGER	Primary key.
station_name	VARCHAR	Station name. For example, single A-1, room V-2.
station_description	VARCHAR	A short description of the station. For example, hot seat, VIP room, etc…
station_status	VARCHAR	Up to you to decide – Open, damaged, in use, etc…

To address the elephant in the room, the computer stations in the cyber cafe.

 

2B) STATION USAGE

COLUMN	DATA TYPE	NOTES
station_id	INTEGER	Primary and foreign key.
use_start	DATE TIME	Primary key. Start date and time.
use_end	DATE TIME	End date and time.
use_notes	VARCHAR	Notes, if any.
member_id	INTEGER	Foreign key, the member who used this station. If any.
staff_id	INTEGER	Foreign key, the staff who issued the station.

To keep track of the station usage. Take note, this only records the start and end date/time. The charges are up to you to decide – By 15-minute blocks, hourly, or maybe even a package.

 

PART 3) PRODUCTS & SALES

3A) PRODUCTS

COLUMN	DATA TYPE	NOTES
product_id	INTEGER	Primary key.
product_name	VARCHAR	Product name.
product_price	DECIMAL	Price of the product.
product_status	VARCHAR	Up to you to decide – “In stock”, “out of stock”, “reserve only”, “pre-order”, etc…

As above, a cyber cafe can offer more than just computer stations. To cater to all kinds of possibilities, we will use “products”.

 

 

3B) SALES

COLUMN	DATA TYPE	NOTES
sale_id	INTEGER	Primary key.
sale_time	DATE  TIME	Timestamp of the sale.
staff_id	INTEGER	Foreign key, staff who processed this sale.
member_id	INTEGER	Foreign key, optional. Member making this purchase.

This should be pretty self-explanatory, the “main” sales table.

 

3C) SALE ITEMS

COLUMN	DATA TYPE	NOTES
sale_id	INTEGER	Primary and foreign key.
product_id	INTEGER	Primary and foreign key. The product being sold.
product_name	VARCHAR	Product name.
prodct_price	DECIMAL	Price each.
item_quantity	VARCHAR	Quantity sold.

The items being sold. Take note that we are recording the product name and price, this is not redundant but for integrity; If the product table is updated later, this will still retain the name and price accurately at the time of sale.

 

 

3D) SALE TOTALS

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary key.
sale_id	INTEGER	Foreign key.
total_name	VARCHAR	Sub-total, discount, voucher, tax, service charge, grand total, etc…
total_price	DECIMAL	The amount.

Yep, we can calculate the totals from the sale items. But we still need this table to keep track of the tax rate, service charge, vouchers, and whatever else.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart