On 07/11/2013 13:03, Smylers wrote:
A purchase consists of ordering one product from a supplier. Each
product is only available from a single supplier. So a record in the
purchase table just needs to store a product ID, and by linking
through the product table that defines the supplier too.
A purchase will also be handled by a particular contact at the
supplier. Again, the purchase table can link to the supplier_contact
table, which in turn links to the supplier.
Except there's now two (indirect) links from the purchase table to the
supplier table. Can the database enforce that they both go to the same
supplier? (That is, that a purchase handled by a particular contact
must be for a product sold by that contact's company?)
I assume your purchase table has foreign key constraints
product_id -> product (id)
contact_id -> contact(id)
And you want to ensure that for a given purchase row that
product(supplier_id) = contact(supplier_id)?
You could add supplier_id to the purchase table.
Now this will create a denormalisation. To ensure that your data does
not become inconsistent, you need to extend your foreign key constraints
on the purchase table to be:
(product_id, supplier_id) -> product(id, supplier_id)
(contact_id, supplier_id) -> contact(id, supplier_id)
This will guarantee that you have no non-existent (product_id,
supplier_id) or (contact_id, supplier_id) tuples in the purchase table.
So the data integrity ill-effects of denormalisation are resolved.
Having done this, any row in the purchase table will now point to a
product and a contact relating to the (one) supplier mentioned in the
purchase row itself, which means that the product and contact will
always relate to the same supplier.
I used this approach in anger at a former employer and it worked well.
Regards,
Bill
