Determine the Database Schema for a Pizza Ordering System
A leading fast-moving consumer goods (FMCG) company is planning to expand into the pizza business by opening a nationwide chain of pizza parlors.
You are the database architect for the project and have been asked to design a database schema for the pizza ordering system.
Start by clarifying the possible entities in the schema or a pizza ordering system. Anchor to your own experience if stuck (maybe order a pizza while brainstorming!)
Interviewers are looking for clear, logical thinking, user-centricity in design, and the ability to anticipate needed changes. Is your design adaptable to changing business requirements?
Database schemas including payments can become incredibly complex. Did you remember to clarify what's out-of-scope for this question? For example - do we need to store multiple payment methods / preferences?
Clarifying Questions
Always begin by clarifying some functional (and non-functional) requirements. Multiple entities are involved here, and our goal is to identify and define the relationships between these entities to ensure we have considered all possible functional scenarios.
Given this prompt, we recommend clarifying the set of entities we have. For example:
- Pizza: ID, Name, type, description?
- Order: ID, Number, order description, order type, delivery address, delivery address type(home/business)
- Customer: ID, name, phone, email, address
- Payments: ID, date, time, amount, method (card/cash), various field1/field2/field3 (can be used to store the credit card/debit card number, expiry dates of the cards, name on the card, etc.)
- Coupons: ID, type, description
Depending on what answers you get, follow-up until you've arrived at a discrete set of requirements. For example, if you will accept coupons or vouchers, can single orders have multiple vouchers?
Lets assume you've defined the following:
- The entities and attributes which we have discussed in the previous section are exhaustive.
- An order can have a single voucher, not more.
- An order can have multiple pizzas.
- A customer can have multiple orders.
- An order can have only one payment.
A customer might choose to store various payment types, such as credit cards, debit cards etc. in his/her account.
For simplification, we would consider the customer account and storage of payment methods / other preferences out-of-scope of this interview question.
Our Solution
Let's begin!
Step 1: Define the pizza entity
The pizza entity will have several attributes with relevant types. For example, ID is an integer, while name, type, and description is varchar.
Step 2: Define the orders entity
We discussed that an order might have multiple pizzas. In database terms, this is called a “1 to many" relationship. This is being denoted by the “1” on top of the “Order” entity and the “*” below the “Pizza” entity.
Step 3: Define the customers entity
Previously we decided that storing details about a customer’s account, such as username/password/payment methods is out-of-scope for this question. In the real world, however, this is important and shouldn't be skipped. As we build out the customer entity, note the 1-to-many (1-*) relationship between customer and order; a requirement we defined above.
Step 4: Define the payments entity
A single order can have one and only one payment associated with it. Thus, orders and payments will have a 1 to 1 (or 1-1) relationship. As we discussed previously, we have a few fields that can be used to store further payment information like card number, expiry date, and name on the card, etc.
It is considered "best practice" to always include a few extra fields in complex entities like payments because the company might decide to accept new payment methods in the future. Expanding our diagram to include payments, we have:
Step 5: Define the coupons entity
Coupons are very important for any company to retain existing customers and attract new customers. Functionally, every order can contain a maximum of 1 coupon. Expanding our diagram to include coupons, we have:
Conclusion
Creating entity-relationship diagrams or database schemas can be very simple, like we see here, or very complex, in complex domains like finance and banking. It is of utmost importance to clarify every requirement before starting the design since any changes later on can be very costly in terms of effort - and when in doubt, include some empty fields for future use.
Pizza Ordering System
Sample query Dine-in order revenue per chair and dine-in order value per server for all locations in Dec ‘24
with chairs_by_loc as ( select location_id, sum(table_size) as total_chairs from table group by location_id ), server_by_loc as ( select location_id, count(server_id) as total_servers from server where end_date IS NULL or end_date>=’01-JAN-2025’ group by location_id ), dine_in_rev_by_loc as ( select location_id, sum(total_amount) as total_amount from order where order_type=’DINE_IN’ and order_date>=’01-DEC-2024’ and order_date<’01-JAN-2025’ ) select loc.state, loc.city, loc.location_desc, x.total_amount/y.total_chairs as rev_per_chair, x.total_amount/z.total_servers as rev_per_server from dine_in_rev_by_loc x join location loc on x.location_id=loc.location_id join chairs_by_loc y on x.location_id=y.location_id join server_by_loc z on x.location_id=z.location_id where loc.end_date IS NULL order by 1,2,3;