Database Design Advice

[Smylers]

William Blunn writes:

> 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

Yeah, I'm assuming that too! (Currently this database only exists on
paper.)

> 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)

Makes sense — thank you for the suggestion.

> I used this approach in anger at a former employer and it worked well.

Good to know.

I'm now reading up on window functions, prompted by your other mail.

Smylers
-- 
The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more: http://civilsocietycommission.info/
Please sign this petition: http://38d.gs/1cJ9ViA
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND