Database Term Project: Open Table

Date: October-November 2022


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...


Make/Cancel a reservation

make plans accordingly


See the number of reservations for the past month for a specific restaurant

assess the popularity of the restaurant


Favorite a restaurant

save the restaurants that I want to try in the future


Search availabilities based on date & time

make plans


See the list of guest reservations for a given day

arrange for supplies and tables accordingly


Get the contact information of customers with reservations for a particular day

contact the customer in regards to the reservation


Add menu items

appeal customers with my restaurant’s dishes


See customers who make reservations frequently

provide special offers to increase customer loyalty


Provide summary statistics for which days of the week were most popular

arrange human and material resources more efficiently


Offer a coupon for discounted food

attract more customers

Conceptual Model

Conceptual Model

Fig 1. Conceptual Model for Open Table database project

Relational Model

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