Database Design Advice

Smylers Smylers at
Thu Nov 7 16:02:37 GMT 2013

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

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

The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more:
Please sign this petition:
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND

More information about the mailing list