CODEWITHSUNDEEP
database-design
Bruno
Bruno

Reputation: 6469

How would I normalize this table

How do I normalize the following table:

Table Order Detail:
-------------------
Order#
Item#
Desc
ShipDate1
Qty1
ShipDate2
Qty2
ShipDate3
Qty3
ShipDate4
Qty4
ShipDate5
Qty5
....
ShipDate20
Qty20

Upvotes: 0

Views: 105

Answers (2)

Yuck
Yuck

Reputation: 50865

You'd want to have three tables: Item, Order, and OrderDetail:

OrderDetail: OrderId, ItemId, Quantity, ShipmentDate
Order: Id, ...
Item: Id, Desc, ...

A better way would be to separate the order detail from shipping detail:

OrderDetail: Id, OrderId, ItemId, Quantity
Order: Id, ...
Item: Id, Desc, ...
OrderShipment: Id, OrderDetailId, ShipmentDate, Quantity

OrderDetail.Id is a surrogate key for the table. It's used again in the OrderShipment.OrderDetailId field. I've included Quantity on the OrderShipment table so that you can support partial order fulfillment.

The item description can be found by joining the OrderDetail and Item tables. You can get order header information by joining to the Order table.

In this design, you might have twenty records instead of that many columns:

OrderId ItemId Quantity
10      123    5
10      489    20
10      238    39

Upvotes: 3

Tony
Tony

Reputation: 2774

Yuck's definition is a start. I would add an Id for Order Detail as well in case the same item needs to appear on an order more than once (for instance, to accommodate pricing differences, delivery options, engraving, etc.).

I also found the question a little ambiguous, as quantities are repeated, but item is not.

Upvotes: 0

Related Questions