Database Term Project: Open Table
Date: October-November 2022
Overview
For the final project for 67-262: Database Design and Development we were required to select a popular company from a list provide to us. Me and my partner, Tianze Shou, elected to do our project on the popular table reservation company Open Table. In our write up we researched Open Table's Buisness model, the pain points they are addressing, and their competitors. More about our buisness analysis can be read here. For the purposes of this page I will be discussing the user stories we created queries for, the Conceptual, The Relational Models, and Normalization.
For this analysis to make sense first we must gain a base level understanding of what Open Table does. Their mission statement states “from helping restaurants grow and run their businesses, to enabling diners to discover and book the perfect table every time they dine, our story is one of connection — among diners, restaurants, and their communities.” From my understanding this is stating that their goal is to help small restaurants and businesses grow and make it easier and more convenient to book tables as well as find places to eat every time a user is trying to go out to eat.
User Stories
- Diner : A customer who is looking for a place to eat food, Can go to many different restaurants.
- Restaurant Manager: Runs the restaurant and manages the waiters
- Waiters: Serves customers food and make sure the restaurant is clean and runs smoothly
As a "Role" |
I want to ... |
So that I can... |
|---|---|---|
Diner |
Make/Cancel a reservation |
make plans accordingly |
Diner |
See the number of reservations for the past month for a specific restaurant |
assess the popularity of the restaurant |
Diner |
Favorite a restaurant |
save the restaurants that I want to try in the future |
Diner |
Search availabilities based on date & time |
make plans |
Waiter |
See the list of guest reservations for a given day |
arrange for supplies and tables accordingly |
Waiter |
Get the contact information of customers with reservations for a particular day |
contact the customer in regards to the reservation |
Manager |
Add menu items |
appeal customers with my restaurant’s dishes |
Manager |
See customers who make reservations frequently |
provide special offers to increase customer loyalty |
Manager |
Provide summary statistics for which days of the week were most popular |
arrange human and material resources more efficiently |
Manager |
Offer a coupon for discounted food |
attract more customers |
Conceptual Model
Fig 1. Conceptual Model for Open Table database project
Relational Model
Fig 2. Relational Model, note: bolded attributes are primary keys, underlined attributes are foregin keys. Some attributes are both PKs and FKs.
For the relational model we designed above all of our entities are in 3NF meaning no repeats attributes, no partial dependencies, or transitive dependencies. We wanted to eliminate all redunency and inconsistant dependencies when designing our models.
Assumptions & Clarifications
- We did not use a composite primary key (id, restaurant_id) for Tables table since we want to use the attribute id as a serial type, which will be enough to uniquely identify each instance. We also want to avoid the complication when a serial integer is involved in a composite PK. The con is that id would become very large if this system were to scale.
- One user can only make reservation for one table at one restaurant at one time point
- A party can only reserve a table if their n_guests is lower than the max capacity of said table
- One restaurant cannot have two menu items with the same name
- Tables are not being moved and combined around a restaurant. There is a set number of tables for each restaurant
- One diner cannot make more than one reservation for the same time/date
- Reservations can be made on the hour and the table opens up exactly 1 hour after the reservation