Can some genius please tell me if my database is in 3rd normal form?


Relationship diagram (http://www.boomspeed.com/martinking/Relationships1.gif)

Thanks for the help Martin.

Why is the ProductID linked to the TransactionID in tblTransactions, instread of the ProductID?

It was just a mistake. Sorry, I should have mentioned that.

In the highlighted table you have a field called CategoryID - is this just data relevant to the table or a foreign key?

it is not a foreign key!

Yes. Many tables can link to the same table. However, I wonder if there is some dependency between Order and purchase order and I don't know what the transactions leg is all about.

tblDeliveries and tblDeliveryDetails and the DeliveryID in tblClientOrders are suspect. I would think that deliveries would be related to the OrderDetails rather than hanging off the Order.

UnitPrice belongs in OrderDetails. I know this seems like a violation of second normal form but it isn't. Price actually depends on order date in addition to product id. If you change the price in the product table, you don't want the price change to affect old orders. Or if your company policy is to refund the difference if a price is lowered, this needs to be done as a separate operation and it probably doesn't apply to products that were shipped 10 years abo.

When a client order is placed it removes the stock from the product file. When a product falls below a curtain amount it is placed on order in the purchase order table. The purchase order is then sent to the relevant supplier.

Could you please tell me how you think that i should link the tblClientOrder, tblClientOrderDetails, tblDelivery and tblDeliveryDetails to all the other table?

Transaction should function like a till. If some one wants a product now and they are not a existing client and do not want to be one, the transaction is carried out thought the transaction table. It can also be used for refunds. There will also be another form relating to the table to be able to write off stock as damaged, stolen or wastage etc. OH, I think I need to conect transaction to tblClientOrders then, because I need to keep records of dicounts and current prices. I was going to put the client orders thought the "till" manually after they had been completed. I know know if that will work now!

What a mess, arrh!

I hope that this helps you, help me.
Thanks to everyone who has helped me so far it is very much appreciated.

Martin Kingston

Something else to think about....

'UnitsInStock' and 'UnitsOnOrder' is derived data and shouldn't be stored in the Products Table. They need to be calculated via queries.

I can't define tables and relationships without knowing any of your business rules. I don't know what the relationship (if any) between PO and order should be. I don't know what the relationship (if any) between Orders and Deliveries should be.

Is a PO something that a customer sends to you to cover global purchases and then sends individual orders that disburse the requested items? So the PO details would not afect inventory. They would simply be used for planning purposes to make sure you had inventory when needed.