This is the second of three case studies that are used to guide the reader through the six steps outlined in the Six-Step Relational Database Design™ book. The most important outputs of the six step database design process are depicted here, but the details of each of the steps and intermediary outputs are outlined in the book.
| What's on this page |
|---|
| Scenario List of entities and their corresponding attributes Entity-Relationship diagrams Relational-Model diagram SQL commands |
Scenario
A mid-sized restaurant plans to implement a digital system to streamline and manage its daily operations. The application will support key functions such as customer reservations, table management, menu offerings, order processing, and billing.
The application must be capable of:
- Managing customer reservations, including the customer, reservation times, and table assignments.
- Tracking table availability and status, indicating whether tables are free, reserved, or currently in use.
- Maintaining a digital menu, listing all available items along with their descriptions, prices, and availability status.
- Recording and tracking customer orders, including quantities, menu items selected, and special instructions (e.g. allergies, dietary restrictions such as strict vegetarian).
- Generating itemized bills, with support for splitting by order, and including applicable service charges, taxes, and the total amount due.
Additionally, the system must be password-protected and support role-based access control, ensuring that users (e.g. host/hostess, customers, waiters, kitchen staff) can only access features appropriate to their roles. User login activity must also be recorded.
To promote transparency and accountability, the application should include basic auditing features, such as tracking who created or modified records and when. Extensive logging of database changes is not required.
Your job is to design a database that will serve as the back end of this application.
List of entities and their corresponding attributes
Below is the list of entities and their corresponding attributes that is output from Step 1 of the six step database design process as described in the Six-Step Relational Database Design™ book:
| Customers |
|---|
| (PK) CustomerId |
| LastName |
| FirstName |
| Mobile |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Reservations |
|---|
| (PK) ReservationId |
| ReservationDate |
| StartTime |
| EndTime |
| NumberOfPersons |
| Status |
| SpecialInstructions |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Tables |
|---|
| (PK) TableId |
| TableNumber |
| Location |
| Capacity |
| Status |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Menu |
|---|
| (PK) MenuId |
| Category |
| MenuItemName |
| MenuItemDescription |
| IsAvailable |
| IsVegetarian |
| Price |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Orders |
|---|
| (PK) OrderId |
| OrderTimestamp |
| FulfilledTimestamp |
| SpecialInstructions |
| Status |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Bills |
|---|
| (PK) BillId |
| BillTimestamp |
| Subtotal |
| ServiceCharge |
| Taxes |
| GrandTotal |
| PaymentStatus |
| SplitType |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Users |
|---|
| (PK) UserId |
| Login |
| UserName |
| PasswordHash |
| PasswordSalt |
| IsActive |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| Roles |
|---|
| (PK) RoleId |
| RoleName |
| RoleDescription |
| Comments |
| CreatedBy |
| CreatedOn |
| LastUpdatedBy |
| LastUpdatedOn |
| IsDeleted |
| DeletedBy |
| DeletedOn |
| LoginEntries |
|---|
| (PK) LoginEntryId |
| LoggedOn |
| LoggedOff |
| IPAddress |
| CreatedOn |
| ModifiedOn |
Top
Entity-Relationship diagrams
Below are the Simplified Entity-Relationship diagrams that are output from Step 3 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Below are the Detailed Entity-Relationship diagrams that are output from Step 5 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
Relational-Model diagram
Below are the Relational-Model diagrams that are output from Step 6 of the six step database design process as described in the Six-Step Relational Database Design™ book:
Top
SQL Commands
The SQL commands below can be used to implement the design depicted above in a MySQL database. Some modifications will be necessary to execute these commands on MS SQL Server, Oracle, or any other RDBMS. Detailed implementation considerations can be found in the Six-Step Relational Database Design™ book.
CREATE TABLE Users ( UserId SERIAL, Login VARCHAR(50) NOT NULL, UserName VARCHAR(50) NOT NULL, PasswordHash VARCHAR(255) NOT NULL, PasswordSalt VARCHAR(255) NOT NULL, IsActive BOOLEAN DEFAULT '1', Role VARCHAR(25) NOT NULL, Email VARCHAR(50) NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (UserId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, UNIQUE INDEX Login (Login), UNIQUE INDEX Email (Email) ); CREATE TABLE LoginEntries ( LogEntryId SERIAL, LoggedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LoggedOff DATETIME DEFAULT NULL, IPAddress VARCHAR(46) NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UserId BIGINT UNSIGNED NOT NULL, PRIMARY KEY (LogEntryId), FOREIGN KEY UserId (UserId) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX LoggedOn (LoggedOn) ); CREATE TABLE Customers ( CustomerId SERIAL, LastName VARCHAR(35) NOT NULL, FirstName VARCHAR(35) NOT NULL, Email VARCHAR(50) NOT NULL, Mobile VARCHAR(14) DEFAULT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (CustomerId), UNIQUE INDEX Email (Email), INDEX Mobile (Mobile), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Tables ( TableId SERIAL, TableNumber SMALLINT DEFAULT 1, Location VARCHAR(50) DEFAULT 'Main', Capacity SMALLINT DEFAULT 2, Status VARCHAR(20) DEFAULT 'Available', CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (TableId), INDEX TableNumber (TableNumber), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Menu ( MenuId SERIAL, MenuItemName VARCHAR(255) NOT NULL, MenuItemDescription VARCHAR(1000) NOT NULL, IsAvailable BOOLEAN DEFAULT '1', IsVegetarian BOOLEAN DEFAULT '0', Price DECIMAL(8,2) DEFAULT '0.00', CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (MenuId), UNIQUE INDEX MenuItemName (MenuItemName), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Reservations ( ReservationId SERIAL, ReservationDate DATE NOT NULL, StartTime TIME NOT NULL, EndTime TIME DEFAULT NULL, NumberOfPersons SMALLINT DEFAULT 2, Status VARCHAR(20) DEFAULT 'Unconfirmed', SpecialInstructions VARCHAR(255) DEFAULT NULL, TableId BIGINT UNSIGNED NOT NULL, CustomerId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (ReservationId), INDEX ReservationDate (ReservationDate), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY TableId (TableId) REFERENCES Tables (TableId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY CustomerId (CustomerId) REFERENCES Customers (CustomerId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Bills ( BillId SERIAL, BillTimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, SubTotal DECIMAL(8,2) DEFAULT '0.00', ServiceCharge DECIMAL(8,2) DEFAULT '0.00', Taxes DECIMAL(8,2) DEFAULT '0.00', GrandTotal DECIMAL(8,2) DEFAULT '0.00', PaymentStatus VARCHAR(20) DEFAULT 'Unconfirmed', SplitType VARCHAR(20) DEFAULT 'Unconfirmed', ReservationId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (BillId), INDEX BillTimestamp (BillTimestamp), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ReservationId (ReservationId) REFERENCES Reservations (ReservationId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE Orders ( OrderId SERIAL, OrderTimestamp DATETIME DEFAULT CURRENT_TIMESTAMP, FulfilledTimestamp DATETIME DEFAULT NULL, SpecialInstructions VARCHAR(1000) DEFAULT NULL, Status VARCHAR(20) DEFAULT 'Placed', ReservationId BIGINT UNSIGNED NOT NULL, BillId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (OrderId), INDEX OrderTimestamp (OrderTimestamp), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY ReservationId (ReservationId) REFERENCES Reservations (ReservationId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY BillId (BillId) REFERENCES Bills (BillId) ON UPDATE CASCADE ON DELETE RESTRICT ); CREATE TABLE MenuOrders ( MenuOrderId SERIAL, Quantity SMALLINT DEFAULT 1, TotalCost DECIMAL(8,2) DEFAULT '0.00', OrderId BIGINT UNSIGNED NOT NULL, MenuId BIGINT UNSIGNED NOT NULL, CreatedBy BIGINT UNSIGNED NOT NULL, CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP, LastUpdatedBy BIGINT UNSIGNED NOT NULL, LastUpdatedOn DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, IsDeleted BOOLEAN DEFAULT '0', DeletedBy BIGINT UNSIGNED NULL, DeletedOn DATETIME NULL, PRIMARY KEY (MenuOrderId), FOREIGN KEY CreatedBy (CreatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY LastUpdatedBy (LastUpdatedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY DeletedBy (DeletedBy) REFERENCES Users (UserId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY OrderId (OrderId) REFERENCES Orders (OrderId) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY MenuId (MenuId) REFERENCES Menu (MenuId) ON UPDATE CASCADE ON DELETE RESTRICT );
| Other Case Studies |
|---|
| Case Study 1 Case Study 3 |
Follow