Normalisation Help

[Matt88]

Hello. I am trying to understand normalisation at the moment. I'm still a bit lost but I have tried to normalise this example. Is it right?

0NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode, ItemID1, ItemName1, ItemPrice1, QuantityOrdered1, ItemID2, ItemName2, ItemPrice2, QuantityOrdered2, ItemID3, ItemName3, ItemPrice3, QuantityOrdered3)

==================================================

First Answer

1NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)

2NF
CUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)

3NF
CUSTOMER(CustomerName*, HouseNumber, Postcode)
ADDRESS(Street, Town, Postcode*)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderNumber, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)




or is it

Second Answer


1NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)

2NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)

3NF
CUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)


Thank you for helping

 

[KenHigg]

Matt, I know I'm going to get gigged on this from the normalization hard liners but I think there are times when replication as opposed to normalization is prefferred. For instance, one could argue that saving the customer address in the order table is a good thing if the customer's address ever changes. This way you'd have history of where the order was actually sent to...

etc, etc,... Some of the more popular software systems such as SAP use replication in strategic areas.

Normalize 'till it hurts then denormaize 'till it works...

Just my 2 cents -

 

[The_Doc_Man]

Everyone who has seen my posts knows that I'm a pragmatist, not a purist.

CUSTOMER(CustomerName*, HouseNumber, Postcode)
ADDRESS(Street, Town, Postcode*)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderNumber, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)

Well, I might do this if I thought addresses would change or if I wanted to be able to track changes of customer address or if multiple customers shared the same address. If I wanted to include histories, dates would have to go into some of these to show when the given data became obsolete. Also, if there are discounts, you aren't finished in expanding the item tables either. But this is closer to your answer 2, I think.

CUSTOMER(CustID*, CustomerName, AddrID[FK to ADDRESS])
ADDRESS(AddrID*, HouseNumber, Street, Town, Postcode)
ORDER(OrderID*, OrderDate, CustID [FK to Customer], ShipTo[FK to AddrID])
ORDERLINE(OrderID [FK to ORDER], ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)