Designing a Relational Database for Little Panda, a takeaway restaurant

Designing a Relational Database for Little Panda

Little Panda is a takeaway who wants to open up its business to accept online orders. To do this, it needs a database to store its food menu, customer data and orders.

Key Requirements to design the relational database that runs on MySQL

• Customers must register before they can make orders. They must
  provide enough details for home delivery.

• Menu item prices may change. Customers are charged prices at the
  time of order.

• Little Panda needs to know the status of an order so that they can follow up. e.g. either it is “waiting to be cooked”, “cooked and to be delivered”, or delivered, etc. You can assume all orders are paid before they enter the
  system.

• Order details must be stored for accounting purpose, even after they are
  completed.

Scope of Work and Defining Relationships

Having considered the Little Panda case study and the requirements, I have come up with the following steps:
1. Design of the Entity Relation Diagram
2. Explaining the Design and stating assumptions
3. Stating Limitations where necessary
4. Defining Relationships Between Tables
5. Schema Normalization and Final Design

Relationships between entities

From the ER derived based on the requirements, given entities A and B, the identified relationships between them are represented with the keys below.

I. The Entity Relationship Diagram

II. Explaining the Design and Stating Assumptions

1. From the ER diagram, there is a one to many relationship between the customer and the payment entities. This is on the assumption that a customer can make many payments, but one payment cannot be linked to many customers. Customer_ID the primary key in the customer entity will be a secondary key in the payment entity, which has Payment_ID as primary key.
2. Between the customer and order entities, there is a many to many relationship. This is based on the premise that a customer can place many orders and same type of order can belong to many customers. However, the address can be extended to a bridge table as a customer can place an order using more than one address. The Customer_ID and Order_ID are primary keys in their respective entities, but will be used as a foreign keys in the address table.
3. The relationship between customer and order also on the premise that a customer can order for someone living in a different address different from the address used in registering.
4. There is a many to many relationship between the order and menu entities. This was derived from an order having many items from a menu and one item in a menu linked to more than one order. The Menu_ID and Order_ID are the primary keys in their respective entities. On the other hand, the order details can be used to bridge the two tables taking their respective primary keys as a foreign key.
5. The order table has a status attribute which can be used to always know the point in the process where the order is.
6. The order and payment entities are presumed to have a one to one relationship, as any payment made can only be linked to an order.
7. An order from a customer can contain various items from the menu.
8. The address of a customer contains the necessary information to deliver an order to the customer.

III. Limitations Identified

1. The address of the customer is considered atomic and can be used to deliver to the required customer.

IV. Defining Relationships between tables

With the ER diagram defined in figure 1 above, this section is to show the sets of tables derived and their relationships.

1. The one to many relationship between customer and payment is shown below and respective primary and foreign keys assigned.

1. The many to many relationship between the customer and order will be taken care of by adding an address table, because of the assumptions made.

1. The one to one relationship between the payment and order entities are shown with the tables below. They have their respective primary keys, however the Order_ID is a foreign key in the payment table.

1. The order and menu entities which are represented with a joint table, to account for the many to many relationship. The order details table will be used as joint table for this table and will contain primary keys of order and menu tables, as foreign keys.

V. Schema Normalization and Final Design

1. CUSTOMER

From the customer table below, the Customer_ID is the primary key, where as the customer name, customer email and customer phone are other attributes.

Customer Table Normalization:

• The Customer table is in 1NF as all attributes are atomic.

• For 2NF, there is a full functional dependency of Cusomer_Name,
  Customer_Email and Customer_Phone on the Customer_ID primary key. They are non-prime attributes which are fully functionally dependent on the primary key Customer_ID as represented below.

  Customer_ID-> Customer_Name {full functional dependency}

  Customer_ID-> Customer_Email {full functional dependency}

  Customer_ID-> Customer_Phone {full functional dependency}

• The table is in 3NF as there is no transitive dependencies on the primary key.

2. PAYMENT

The payment table below has Payment_ID as the primary key. The
Total_Amount, Payment_Datetime, Payment_Method are other dependent
attributes. The Payment_ID uniquely identifies all the payment entries made

Payment Table Normalization:

• The Payment table is in 1NF as all attributes are atomic.

• For 2NF, there is a full functional dependency of Total_Amount,
  Payment_Datetime and Payment_Method on the Payment_ID primary key.
  They are non-prime attributes which are fully functionally dependent on the primary key Payment_ID as represented below.

  Payment_ID -> Total_Amount {full functional dependency}

  Payment_ID -> Payment_Datetime {full functional dependency}

  Payment_ID -> Payment_Method {full functional dependency}

• The table is in 3NF as there is no transitive dependencies on the primary key.

3. ORDER

The order table below has Order_ID as the primary key. The Order_Datetime and Order_Status are other dependent attributes. The Order_ID uniquely identifies all the order entries made. On the other hand, an order details table will be subsequently introduced, to identify the link to content of each order
made.

Order Table Normalization:

• The Order table is in 1NF as all attributes are atomic.

• For 2NF, there is a full functional dependency of Order_Datetime and Order_Status on the Order_ID primary key. They are non-prime attributes which are fully functionally dependent on the primary key Order_ID as represented below.

  Order_ID Order_Datetime {full functional dependency}

  Order_ID Order_Status {full functional dependency}

• The table is in 3NF as there is no transitive dependencies on the primary key.

4. ADDRESS

There is an assumption that a customer can order to multiple address at different point in time. The address table will be used to identify a customer’s order, with their respective address. The Customer_ID and Order_ID are the composite keys from the customer and order tables, where they serve as primary keys.

Address Table Normalization:

• The Address table is in 1NF as all attributes are atomic.
• For 2NF, there is a full functional dependency of Address_Line on the composite keys, as shown below.

Customer_ID, Order_ID -> Address_Line {full functional dependency}

• The table is in 3NF as there is no transitive dependencies on the primary key

5. ORDER DETAIL

The order detail table is a joint table between the order and menu tables. The Order_Quantity and Total_Price are dependent on the Order_ID and Menu_ID which are composite keys.

Address Table Normalization:

• The Order table is in 1NF as all attributes are atomic.

• For 2NF, there is a full functional dependency of Order_Quantity and Total_Price on the composite keys, as shown below.

  Order_ID, Menu_ID -> Order_Quantity {full functional dependency}
  Order_ID, Menu_ID -> Total_Price {full functional dependency}

• The table is in 3NF as there is no transitive dependencies on the primary key.

6. MENU

The menu table below has Menu_ID as the primary key. The Menu_Name, Menu_Price and Menu_Description are other dependent attributes. The Menu_ID uniquely identifies each menu item.

Payment Table Normalization:

• The Menu table is in 1NF as all attributes are atomic.
• For 2NF, there is a full functional dependency of Menu_Name, Menu_Price and Menu_Description on the Menu_ID primary key. They are non-prime attributes which are fully functionally dependent on the primary key Menu_ID as represented below. Menu_ID -> Menu_Name {full functional dependency} Menu_ID -> Menu_Price {full functional dependency} Menu_ID -> Menu_Description {full functional dependency}
• The table is in 3NF as there is no transitive dependencies on the primary key.

The final design schema in figure 2 below, shows the identified and normalized tables with their various primary keys and attributes.

FINAL SCHEMA DESIGN

Feel free to leave a comment on any part of the document that interests you.