DA Project: Beverage Shop Database Design Work

Date: Nov. 2020
Client: Cal Poly Course Project
Tag: Data Analysis|Database | Schools

In the Cal Poly Database management course, we have to design a database model for a specific shop. I teamed with 2 other students. We decided to design a beverage shop database design.

Background

Since Covid-19 has been spreading in our city, the customer traffic has dropped a lot in our boba shop. Our management had made a decision to start the boba delivery service to replace the original service model to minimize the risk of infection for both our employees and customers. Before we start delivering any boba, we need to know who ordered the boba, where and when the boba should be delivered, what products are included in the order, what is the final price of the order, if the order is paid for, etc. We also need to track delivery statuses and see their reviews about our boba to improve our services. At the same time, we collect payment information for accounting purposes. Suppose that we have our own app that our customers can use to place orders for boba delivery. In the future, we might work with Ubereat, Doordash, and Grubhub to increase customer volume.

Business Rule

The business rule would help us to know the heart of the business model of the company. Also, this is the first thing we have to define before we start. In this project, the business rule would be:

• Customers with or without VIP membership may purchase a cup of drink with different preferences.

How would customers and employees make use of the database?

In this part, we would make some supposes that how would people and employees look up the data on the database, just as what we work on the SQL query. The following would be some transactions:

• The customer chooses the product of his or her desire from the menu, which includes all the boba milk tea, and coffee;
• The customer can check if there is any offer/ promotion going on for the items of his or her desire;
• The customer can leave any comments about the customization or special requirement;
• The customer then can proceed to checkout on the app, where the customer puts down his or her information, including name, phone number, address, etc;
• Then the order detail page will pop up and show the order time, estimated delivery time, final price, and the customer can proceed to the payment with credit or debit cards;
• After placing the order, the customer then can check the order status of his or her order;
• The customer can leave a review of his or her experience with our product and service.

ER Model Design Detailed

For the detailed information of each entity, there should contain Name, Schema, and Definition. Take the order detail table as an example, the detailed information would be like the following :

Name: Order detail

Schema: order_id, customer_id, VIP_Id, order_date, order_quantity (product_id, counts), order_total

Definition: An order is the purchase request to be served by the employees. Each order has its unique order_id, the custoimer_id, order_date, VIP_Id, order_quantity, and order_total, which calculate the total sales price of the order.

ER — Model

Dimension Entity Design Detailed

The table grain would be that a single row of the fact table represents a single transaction of a customer’s boba delivery. Then, we would make the detailed information of the entity like :

Customer dimension:

Customer_id: Surrogate Key SK
Customer_name: SCD Type 1
Address: SCD Type 1
City: SCD Type 1
State: SCD Type 1
Phone_number: SCD Type 1

The surrogate key would help each entity to link back to the fact table. SCD has types 1,2,3 that would represent different data types.

Secondly, we have to check the Role-Playing Dimension, Hierarchies, Degenerate Dimensions, Junk dimensions, and others. Role-playing Dimensions are not included in our design, though we thought about making a separate date/ time dimension as a role-playing Dimension. However, it would be more intuitive to include the original date and timestamps in their original dimensions.

We have hierarchies in the customer dimension, where city < state. We also have hierarchies in the offer dimension with attributes such as Data_active_from, Time_active_from, Data_active_to, Time_active_to. We did not include any degenerate dimensions or junk dimensions in our design.

Dimension Model

Oracle XE

After we finished designing both tables, we would like to use SQL queries to create tables and insert data into the Oracle XE. At last, with these efforts, the database could work.