Database Design Advice

[Smylers]

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