Conceptual Database Diagram

Retail Order Management System（coffee shop）

Table 1: Customers

Field Name	Description	Key
CustomerID	Unique identifier for each customer	PK
Name	Customer name
Phone	Contact phone number
Email	Email address

Table 2: Employees

Field Name	Description	Key
EmployeeID	Unique identifier for each employee	PK
Name	Employee name
Position	Job title or role
Phone	Contact phone number

Table 3: Products

Field Name	Description	Key
ProductID	Unique identifier for each product	PK
ProductName	Name of the product
Price	Unit price
Category	Product category

Table 4: Orders

Field Name	Description	Key
OrderID	Unique identifier for each order	PK
CustomerID	Customer who placed the order	FK → Customers.CustomerID
EmployeeID	Employee who handled the order	FK → Employees.EmployeeID
ProductID	Product included in the order	FK → Products.ProductID
OrderDate	Date the order was placed
Quantity	Number of items ordered
TotalPrice	Total cost of the order

Relationships Summary

• One Customer can place multiple Orders
• One Employee can handle multiple Orders
• One Product can appear in multiple Orders
• Relationships are established using foreign keys to avoid data duplication

This conceptual database diagram represents a coffee shop management system. It organizes operational data into four interrelated tables: Customers, Employees, Products, and Orders. Each table is defined by a unique primary key, while relationships between entities are established through foreign key references. The Orders table functions as the central structure, linking customer purchases, staff involvement, and menu items while ensuring data consistency and avoiding redundancy.