Re: disjoint union types - Mailing list pgsql-general

On Wed, Oct 10, 2007 at 12:10:10PM -0500, Erik Jones wrote:
> On Oct 10, 2007, at 11:42 AM, Sam Mason wrote:
>> On Wed, Oct 10, 2007 at 05:02:36PM +0100, Ian Barber wrote:
>>> CREATE TABLE shapes (
>>>  shape_id serial PRIMARY KEY,
>>>  area real not null
>>> );
>>>
>>> CREATE TABLE circle (
>>>  radius real not null
>>> ) INHERITS (shapes);
>>
>> Postgres implements inheritance in a strange way (the way it is at the
>> moment makes sense from an implementation, but not users', point of
>> view), you can end up with a circle and square both with shape_id=1 if I
>> don't take a lot of care.
>
> It doesn't take much care at all to avoid that:  don't use SERIAL for the
> primary key of the parent.  Instead use an explicity "id integer NOT NULL
> DEFAULT nextval('some_seq'), that way all of the child tables will use the
> same sequence as the parent.  That being said, I'm not convinced that table
> inheritance is what's needed here.  I'll wait until you post the example
> you mention below before commenting further.

The main problem I have with inheritance is that the unique constraints
are only checked per-table.  If I have:

  CREATE TABLE a ( id INTEGER NOT NULL PRIMARY KEY );
  CREATE TABLE b ( ) inherits a;
  INSERT INTO a VALUES (1);

I can run this without anything having any complaints.

  INSERT INTO b
    SELECT * FROM a;

If I'm careful about getting data into the database then this isn't a
problem, but, to me, this is exactly the thing I shouldn't have to be
careful about because it's the database's job to keep track of this.  As
I said before, I understand, from an implementation point of view, why
it has this behaviour; it just isn't very nice from a users'.


The shape example I used before was me attempting to simplify the
problem, I think I took things too far.  My original motivation for
wanting to do this sort of thing was a (sort of) stock control problem.
I've got various types of items that I want to store information about.
I want to treat the union of these different types in a uniform manner,
for example I keep track of whether they are in their canonical location
or are "checked out" and in use.

To do this I had something like:

  CREATE TABLE books ( id SERIAL PRIMARY KEY, name TEXT );
  CREATE TABLE computer ( id SERIAL PRIMARY KEY, name TEXT );

  CREATE TABLE stock (
    id SERIAL PRIMARY KEY,
    tag INTEGER NOT NULL,
    bookid INTEGER REFERENCES books
      CHECK ((tag = 1) = (bookid IS NOT NULL)),
    computerid INTEGER REFERENCES computer
      CHECK ((tag = 2) = (computerid IS NOT NULL)),
    barcode TEXT UNIQUE
  );

This, for example, allows me to rely on the database to check that the
barcode uniquely identifies each piece of equipment.  It also doesn't
require touching more than is needed when scanning for the actual item's
detail as the tag is there to specify where to look.  We've recently
added barcodes to non-"stock" items and it took me a while to realise
that a similar rotation of things allows me to store a single barcode
in a similar way.  For example, I could drop the barcode from the stock
table and table like:

  CREATE TABLE barcodes (
    id SERIAL PRIMARY KEY,
    tag INTEGER NOT NULL,
    stockid INTEGER REFERENCES stock
      CHECK ((tag = 1) = (stockid IS NOT NULL)),
    plateid INTEGER REFERENCES testplates
      CHECK ((tag = 2) = (plateid IS NOT NULL)),
    barcode TEXT UNIQUE
  );

and I can get back to wherever I want to.  The annoyance is that it's
a bit of a fiddle, schema wise, to do this translation.  So that's my
reason for asking if there were other ways of doing this sort of thing.


  Sam