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