Database Design Advice

[Abigail]

On Thu, Nov 07, 2013 at 01:03:00PM +0000, Smylers wrote:
> 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.


I'm not familiar with Postgres, but I've used triggers to enforce 
business rules like this in the past (using Sybase). In Sybase,
triggers are run before a transaction is committed, and you can
rollback a transaction frow within a trigger.

On an insert, you'd check whether the number of documents with the
same document_id, and version less than the to be inserted version is
1 less than the version. You'd disallow updates of the version number,
and on deletions, you check if no higher version exists with the same
document_id.


> • 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?

It will depend on what queries you actually are going to perform. 
Both have their advantages and disadvantages, but only when you know
your queries, you know which solution has the bigger advantage.

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


Triggers ;-)


Abigail