2

We have tables pizza and orders

Logical model

SQL data modeler generates relational model like this:

Relational model

We forgot, that the customer can buy 2 or more of the same pizza. We found that we can add column pieces to table pizza_orders or we can create special primary key for table Pizza_Orders (Pizza_Orders_Id). Which solution is better? Or is there other better solution?

Pivoman
  • 4,435
  • 5
  • 18
  • 30
  • In your `Pizza_Orders` table, why can't an `Order_id` appear more than once for each pizza? I'm not sure I see a problem here. – Tim Biegeleisen Dec 28 '15 at 12:17
  • 1
    I need to buy 2 of the same pizza (like 2 pizza salami). And I can't do insert into table pizza_orders(14, 2) and then again insert into table pizza_orders(14, 2) – Pivoman Dec 28 '15 at 12:22
  • 1
    This is clear now, thanks. – Tim Biegeleisen Dec 28 '15 at 12:23
  • What you need to do is add another entry into PIZZA for the new pie. It doesn't matter that its ingredients match those on another pie - there will be many pies in PIZZA which represent the same type of pie. It's a new pie, create another entry in PIZZA, then your mapping remains clean. – Bob Jarvis - Слава Україні Dec 28 '15 at 12:24
  • May I ask whether the entire order including all types and numbers of pizzas will be known at the time the order is generated? – Tim Biegeleisen Dec 28 '15 at 12:26
  • 3
    A quantity field in pizza_orders, which is part of the primary key, seems to me to be the best way to go. Something else you appear to have forgotten is how to handle price changes. – Dan Bracuk Dec 28 '15 at 12:30
  • @DanBracuk This is what my answer says! – Tim Biegeleisen Dec 28 '15 at 12:31
  • But it was lower than the bottom of my monitor. – Dan Bracuk Dec 28 '15 at 12:32
  • Ok, thanks a lot to all, but why it must be "quantity" part of primary key? – Pivoman Dec 28 '15 at 12:35
  • 2
    I don't think the quantity needs to be part of the primary key because a given order will only be associated with a given type of pizza once. – Tim Biegeleisen Dec 28 '15 at 12:38
  • I agree that quantity should probably go into PIZZA_ORDERS, but it shouldn't be part of the primary key. – Jeffrey Kemp Dec 30 '15 at 05:46
  • The alternative (which has one advantage of the ideas proposed) is to remove the primary key from PIZZA_ORDERS and use a surrogate key instead. That way the PIZZA_ORDERS table can also represent customised pizzas (e.g. "supreme pizza but no anchovies". Just keep adding more records to the table for each pizza the customer orders. – Jeffrey Kemp Dec 30 '15 at 05:48

2 Answers2

4

Modify your Pizza_Orders table to include a field for quantity:

Pizza_Orders:

Orders_Order_id
Pizza_Pizza_id
Pizza_Pizza_quantity

Doing insertions into this table would be easy if the entire order were known at once. In other words, if the number and types of all pizzas generally be known, then insertions should not be a problem. This also solves the problem of an order with more than one type of the same pizza.

Note that the primary key in this new Pizza_Orders table remains just the combination of order number and pizza number and does not involve the quantity. The reason for this is that an order number and pizza type can only appear in at most one record, regardless of the quantity.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
3

You should add column quantity to table Pizza_Orders. I think you missed the size of pizza. There is no option to choose the size while ordering, though it is important for giving order.

Suvro
  • 352
  • 2
  • 13
  • Plus 1 because at the time of the comment, @Suvro did not have a high enough reputation to post comments. – Dan Bracuk Dec 28 '15 at 12:36
  • It is school project, so I will let it on that easy way without size of pizza. But thanks! – Pivoman Dec 28 '15 at 12:38
  • I would expect different size pizzas would probably cost different amounts, so they would be represented as different pizzas in the PIZZA table. – Jeffrey Kemp Dec 30 '15 at 05:49