Database Design Advice

[William Blunn]

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