Help with Normalisation of tables!!!

[claire fogarty]

Hi I am having trouble trying to normalise my tables, I would really appreciate some help! So far I have:

Customer Table
CustId(pk)
Cust Name
Cust Address
Cust Postcode
Cust Email

Order Table
OrderID (pk)
Order Date_Time
CustID(fk)

OrderDetails
DetailsID(pk)
Quantity
OrderNo(fk)
DeliveryID(fk)

Product
ProductID(pk)
ProductType
ProductDescription
DetailsID(fk)
PartID(fk)
Price
VatID(fk)

Part
PartID(pk)
PartName
PartDescription
SupplierID(fk)

Vat Table
VatID(pk)
VatRate

Inventory
InventoryID(pk)
Inventory Quantity
PartID(fk)

Supplier
SupplierID(pk)
Supplier Name
Supplier Address
Supplier PostCode
Supplier TelNo
Supplier VatNo

Delivery Table
DeliveryID(pk)
DeliveryDate

Help would be gratefully appreciated!

[Stewart Ross]

Other than your delivery table - which is not needed at all - what you've listed looks OK. A delivery date is an attribute of something else, most probably of the order line itself.

We'd really need to know which tables are related 1-to-many to give a fully informed answer, or more simply have access to an entity-relationship diagram (ERD) for your database, but what you've got makes sense for purchase ordering at present.

As we cannot really assist you other than in the most general of terms without doing the work for you, and purchase ordering is not a trivial application, I'd instead suggest that you read our Insights article on Database Normalisation and Table Structures which provides useful background on normalisation if you don't have such background already.

-Stewart

ps Whilst this really isn't a comment on normalisation, it would be prudent to add to your order details table additional attributes, and in particular the item price and the VAT payable. Although these can be derived through relationships to the VAT and product tables, what you need to store is the price at date of order, and the VAT at the date of order. Without this storage of actual values in the order line table the computed totals for the price of an existing order will be incorrect if the VAT rate changes or an item unit price is changed.