Quantity Contract and Purchase Order dilemma

[SNES]

I am a newbie looking at designing a simple database for my manufacture-based company, part of the job it does is maintaining a Purchase Order Table, a typical one and it suits all the customers we have, except for 1 particular customer which typically issues a Quantity Contract - a contractual agreement to purchase a number of specific Product with a specific Qty, this contract would eventually be superceded(materialized) by a single OR a sequence of Purchase Orders, without which a delivery cannot be performed.

I'm stuck at the design of the tblPurchaseOrder and tblQuantityContract, and it's relationship.

Seek a kind soul for enlightenment. Thanks.

[BitBlt]

Part of the problem (as I understand it, and sorry if I misinterpreted) is that the Quantity Contract is related to the Customer first. Therefore, if you want referential integrity between tblPurchaseOrder and tblQuantityContract, you have to either have a nullable foreign key into tblPurchaseOrder from tblQuantityContract, or you have to have a child table to resolve between tblPurchaseOrder and tblQuantityContract.

If you have the nullable foreign key, then each PurchaseOrder can be related to only one QuantityContract. That automatically limits the PurchaseOrder to a single product at a time (not very flexible). If you go further and have the foreign key to the PurchaseOrderLineItem (assuming you have one) that gives you a better fit but you have to programmatically ensure how much of the QuantityContract is satisfied by a specific PurchaseOrderLineItem. Still not ideal.

I suggest you go with a child table to resolve between PurchaseOrderLineItem and QuantityContract. QuantityContract has a non-identifying relationship to Customer. It doesn't require you to maintain a column in the PurchaseOrder (or -LineItem) for Customers that don't use QuantityContracts, and lets you test for existence rather than have to do a bunch of null-testing for every PurchaseOrder.

Hope this helps some! Good luck!

[ChrisPadgham]

Can a purchase order have more than one product on it.

if not, put a foreign key (ContractId) in the tblPurchaseOrder table.
if so, put a foreign key (ContractId) in the tblPurchaseOrderLine table.

[SNES]

thank you both for your inputs, let me spend a bit of time on this.