From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: many to one of many modeling question |
Date: | 2008-01-08 11:34:53 |
Message-ID: | 20080108113453.GW11262@frubble.xen.chris-lamb.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jan 07, 2008 at 02:32:26PM -0500, Kevin Hunter wrote:
> Something along the lines of
>
> CREATE TABLE o_1 ( id SERIAL ... );
> CREATE TABLE o_2 ( id SERIAL ... );
> CREATE TABLE o_3 ( id SERIAL ... );
> CREATE TABLE comments (
> id SERIAL,
> obj_id INTEGER ...
> FOREIGN KEY (obj_id) REFERENCES ONE OF o_1(id), o_2(id), o_3(id)
> );
>
> This obviously won't syntactically work, but you perhaps get the drift ...
I've done this sort of thing before:
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
otype INTEGER NOT NULL CHECK (otype IN (1,2,3)),
o1id INTEGER REFERENCES o_1, CHECK ((otype = 1) = (o1id IS NOT NULL)),
o2id INTEGER REFERENCES o_2, CHECK ((otype = 2) = (o2id IS NOT NULL)),
o3id INTEGER REFERENCES o_3, CHECK ((otype = 3) = (o3id IS NOT NULL))
);
that way everything is contained in one table. It's a bit fiddly to
use though if you want to do things like get all the comments, and some
identifier out from each object:
SELECT c.id, c.description, o1.identifier,o2.barcode,o3.prodcode
FROM comments c
LEFT JOIN o_1 o1 ON c.o1id = o1.id
LEFT JOIN o_2 o2 ON c.o2id = o2.id
LEFT JOIN o_3 o3 ON c.o3id = o3.id;
Not too bad though.
Sam
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2008-01-08 12:01:07 | RE: [GENERAL] Problema al cargar polígonos |
Previous Message | Pedro Briones García | 2008-01-08 11:18:43 | Problema al cargar polígonos |