Help designing a database schema for a pizza store

Question

Here's the script:

create table Customer
(
CustomerId int primary key identity(1,1),
Name nvarchar(64) not null,
LastName nvarchar(256) not null,
Telephone nvarchar(32),
MobilePhone nvarchar(32),
Address nvarchar(256)
)

create table Product
(
ProductId int primary key identity(1,1),
Name nvarchar(64),
Price decimal
)

create table StoreOrder
(
StoreOrderId int primary key identity(1,1),
Date datetime,
CustomerId int foreign key references Customer(CustomerId),
Total decimal
)

create table ProductStoreOrder
(
ProductStoreOrderId int primary key identity(1,1),
StoreOrderId int foreign key references StoreOrder(StoreOrderId),
ProductId int foreign key references Product(ProductId),
Quantity int
)

I'm confused on how to handle toppings. I should be able to add toppings in the database somewhere and create pizzas with N toppings, each topping should also have an associated price.

I could create a Toppings table and associate it to Product but not every product has a topping. For example, bread sticks, a diet soda, a salad, etc.

What would be the best way to handle this situation? Also, any comments on the database design so far?

Thank you for your time.

Any comments? Well, for starters, every column at the moment is nullable. It's unlikely that that's correct. — Damien_The_Unbeliever, Mar 24 '11 at 13:51

score 4 · Accepted Answer · answered Mar 25 '11 at 02:53

4

I think this is a good candidate to utilize a super/subtype DB design. Here is a quick drawing that I believe addresses the concerns you have at this point. This is just the base, you can take it and fill in the needed attributes and pricing as you are concerned about.

enter image description here

answered Mar 25 '11 at 02:53

swisscheese

1,765
4
23
25

1. How would you design schema including orders? 2. How would you scale "categories" easily, make a table for each category? – kravemir Jul 06 '15 at 17:23

score 0 · Answer 2 · answered Mar 24 '11 at 13:36

You can try something like :

Pizzas, soda, sticks, salad are products table.
Products have many Toppings (foreign key of both table in a join table, price deported in the this join table )

After you can define products "template" in an another table, to define category and if product have Toppings or other special attributes.

score 0 · Answer 3 · answered Mar 24 '11 at 13:48

0

Go with @Andy 's suggestion, or add a boolean to the product table indicating whether the product is a topping or not.

answered Mar 24 '11 at 13:48

Christian Bøhlke

779
1
7
18

Help designing a database schema for a pizza store

3 Answers3

Linked