Search code examples
sqloracle-databaseoracle-sqldeveloperoracle-sql-data-modeler

How to buy more pieces of one product - best practices

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?

Solution

  • 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.