Hello. I'm designing a database schema, and am interested in any wisdom folk can share over a few aspects of it:
• A document can have versions. There's a document table and a document_version_contents table, where each document_version_contents record references document.id and has a version number. The pair {document_id, version} are unique, and that can be enforced in the DB. But version should start at 1 for each document and be consecutive. That is, if there is a record for document_id = 3846, version = 6 then there should also be records for version 1 to 5 of that document ID. Is there a way of enforcing that at the DB level? We're using Postgres, if that makes a difference. • The primary key of document_version_contents could be {document_id, version} (with tables linking to it specifying both of those), or I could create document_version_contents.id field, an arbitrary auto-increment number, which other tables could use as a foreign key. The advantage of the arbitrary id field is that it means other tables only need to link to one field. The advantage of using the pair of unique fields that exist anyway is that the values in them are meaningful: when working on the DB, or writing a report pulling data from it, it's easy to see which records correspond with which document; in programs using the DB, look-ups use values it has handy anyway. Anybody think I should pick one or t'other? Or has experience of going with one then regretting it? • 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?) Thank you for any advice. 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