referential integrity and defaults, DB design or trick

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: referential integrity and defaults, DB design or trick
Date: 2007-12-19 16:24:52
Message-ID: 20071219172452.330d5fbe@webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've something like this:

create table i (
iid serial primary key,
name varchar(32)
);
create table p (
pid serial primary key,
iid int references i(iid) on delete cascade,
name varchar(32)
);
create table c (
bid serial primary key,
pid int references p(pid) on delete set null
);

insert into i values(default,'i');

-- default proprieties (singularity)
insert into p values(-1,null,'default p');

insert into p values(default,1,'this p');
insert into p values(default,1,'that p');

insert into c values(default,null);
insert into c values(default,1);
insert into c values(default,-1);

let's say I'd like to associate c with a name (the propriety)

a null c.pid means I still have to assign a propriety or the
previously assigned propriety is not anymore available.

I'd like to have a way to say take the propriety from i and the above
is what I came out with.
But that introduces a singularity.

Any better design? I don't like to write a schema that needs data
inside to have a meaning.

If not how can I protect the singularity from accidental delete?
Most of the db will be accessed through functions and this is a step.

An alternative design could be
create table c (
bid serial primary key,
usedefault boolean,
pid int references p(pid) on delete set null
);
where
usedefault=true -> use default
usedefault=false -> use i.pid
usedefault is null -> not yet assigned

but then I'll have to find a way to keep usedefault and pid coherent
(I can't have usedefault=false and pid=null).
And having a trigger doesn't make it look nicer, since if I pass
not coherent values I'll have to deal with the error.
The first technique doesn't have this problem (just others... but it
is simpler).

Any good link about DB design and how to deal with similar problems
that has some postgres spice?

thx

and yep pgsql community is great.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-12-19 17:01:17 Re: Getting char * from timestamp in a composite type
Previous Message Michael Akinde 2007-12-19 16:22:59 Re: Getting char * from timestamp in a composite type