Invoice Normalization

Doball

Is the normalisation below correct, if not could you point me in the right direction and tell me what I've done wrong?

Un-Normalised Data
(OrderNo, CustomerName, PayeeAddress, ShippingAddress, OrderDate, ShipDate, ItemNo, Description, Quantity, UnitPrice)

1NF

ORDER
(OrderNo, CustomerID, PayeeAddress, ShippingAddress, OrderDate, ShipDate, Quantity)

ITEM
(ItemNo, Description, UnitPrice)

2NF

ORDER
(OrderNo, CustomerID, PayeeAddress, ShippingAddress, OrderDate, ShipDate, ItemNo)

ITEM
(ItemNo, Description, Quantity, UnitPrice)

ITEMDESCRIPTION
(ItemNo, Description, Quantity)

3NF

ORDER
(OrderNo, ItemNo, OrderDate, ShipDate)

ITEM
(ItemNo, UnitPrice)

ITEMDESCRIPTION
(ItemNo, Description, Quantity)

CUSTOMER
(CustomerName, PayeeAddress, ShippingAddress)

bubblenut

I think that description should be in item and you don't need a item description however you do need an orderline table which would have the order_id, the item_id and the qty this would be a link table between order and item handle the many:many relationship.
HTH
Rob
NB Although reducing through normal forms is usefull to understand how the data is normalised it is necesary to analyse the 3NF carefully and make sure it does actually make sense according to the original data.

johnnyv

If you split into 2 or more tables you need a unique key in the tables

Un-Normalised Data
(OrderNo, CustomerName, PayeeAddress, ShippingAddress, OrderDate, ShipDate, ItemNo, Description, Quantity, UnitPrice)

Order table
OrderNo(primary key), CustomerName, PayeeAddress, ShippingAddress, OrderDate, ShipDate

OrderDetails table
OrderNo(primary key), ItemNo(primary key), Description, Quantity, UnitPrice

Thats as far as i would go because if you normalise anymore you have to do a lot more querys to the database which can become a real chore.

barand

I'd go the whole hog:-

Order table
OrderNo(primary key), CustNo, OrderDate, ShipDate

Customer table
CustNo(primary key), CustomerName, PayeeAddress, ShippingAddress

OrderItems table
OrderNo(primary key), ItemNo(primary key), ProdNo, Quantity,
orderPrice*

Product table
ProdNo(primary key), Description, UnitPrice

*Unit price here not necessarily the same as currently on the product file - pick up current price at time order placed (could be a special offer price, earlier price before price change etc)