Design a database schema for a pizza ordering system

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.