Le mercredi 18 avril 2012 à 00:06 +0200, Nils Gösche a écrit :
> Bartosz Dmytrak wrote:
>
> >
> The reason I like this particular way of modeling the data is that I have a
> guarantee that there won't be an entry in both derived tables at the same
> time for the same row in the base table; also, I can have further constraints
> and foreign keys from and to the base table.
>
use a trigger on each of the derived tables, that cancels any insert if
the same id already exists in the other table?
> Of course, I could also omit the type field and simply live with the
> possibility of having two rows in the derived tables referring to the same
> row of the base table. But it would be nice if I could rule that out with
> simple constraints.
You don't say how your data gets inserted, but considering how
complicated your preferred option looks, I have to ask why you can't use
something as simple as :
CREATE TABLE base (
id int PRIMARY KEY,
some_data int NOT NULL,
type integer NOT NULL DEFAULT 1
);
-- type 1 = derived1, type 2 = derived2
CREATE TABLE derived1 (
id int PRIMARY KEY,
data1 int NOT NULL,
FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);
CREATE TABLE derived2 (
id int PRIMARY KEY,
data2 text NOT NULL,
FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);
You'll have to build the queries according to the value of type, but
this should give you the features you mention?
--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour
le service juridique
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
