Help designing a database schema for a pizza store

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.

---

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.

---

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.

---

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