Database Design Advice
bill+london.pm at blunn.org
Thu Nov 7 14:57:14 GMT 2013
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.
More information about the london.pm