Database design for storing an unknown number of rows in table

Question

Hi. Sorry if the title is hard to understand.. :S

I have a table with an order, and this order should be able to have an unknown number of pizzas. How do I implement this?

I have a table for pizzas, and a row for PizzaID. The order table should have an unkown number of pizzaIDs.. :S

Thanks

Answer 1

You should link them the other way around. The pizza should indicate which order it's associated with, as each pizza will only come from one order.

For example, your pizza record might look something like this:

PizzaId, **OrderId**, BaseType, StuffedCrust, PizzaType

Then you can simply query the database for all pizzas related to order n.

Answer 2

You need a table for Orders, a table for Pizzas and a junction table associating Pizzas with Orders

Pizza                 
-----  
Id
Name             

Order
-----
Id
Date_Of_Order

Pizza_Order
-----------
Order_Id
Pizza_Id
Quantity

Answer 3

Add an 'OrderID' column to the Pizzas table, and make it a foreign key to the 'ID' column in the Orders table.

This is fairly basic relational database design stuff, you might want to look at some basic tutorials.

Answer 4

You need three tables:

• Pizzas (with PizzaID + other columns that define a particular pizza)
• Orders (with OrderID + additional columns that you need like customer, order datetime etc...
• PizzaOrders (with OrderID, PizzaID and Quantity) - several rows will have the same OrderID, but different PizzaID values + number of these pizzas.

So you have a many-to-many relationship between Pizzas and Orders tables.

Answer 5

I would create 3 tables here

One for orders

One table orderItems that is 1-n linked to te orders table by storing the orderID and n-1 to Pizzas by Storing the PizzaID. You should also store the price information in this table, since the price of a pizza might change over time, and that could affect your old orders

and another table Pizzas that will contain all available Pizza Items (Call it MenueItems if you plan to expand to pasta and beverages later on ;) )