Re: referential integrity and defaults, DB design or trick

On Thu, 20 Dec 2007 09:55:29 -0600
Erik Jones <erik(at)myemma(dot)com> wrote:

>
> On Dec 20, 2007, at 8:50 AM, Ivan Sergio Borgonovo wrote:
>
> > On Wed, 19 Dec 2007 17:24:52 +0100
> > Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
> >
> >> 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,

this should be cid for coherency in notation.

> >> 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
>
> Ivan, after reading both of your posts I'm still not sure what you
> mean or are trying to do. What do you mean by a singularity? By
> propriety do you mean property? Can you give an example with more
> descriptive names than i, p, and c?

OK... provided I'm not at risk of opening a flame war against bottom
posting ;)

First I beg you pardon for relying too much on my spell checker
(property and propriety in Italian are spelt the same, and missing
any red warning I went further).

Yep property as being yellow.

* i has a default property i.name "he is hungry by default"
i.name=hungry

* i has a set of other property he can chose from in p (thirsty,
happy...)

* c is the chosen property of the day (c actually will contain more
than a p)

c can contain:
1) sorry not chosen yet
2) pid
3) hey today I'm hungry as usual

I want to be sure that c can contain just the above.
1) and 2) are easy null + references p(pid)

storing 3) is a bit tricky (for me at least):

a) put a singularity in p that means 3) and that won't get overwriten
by serial (this means making the logic depends on data not on schema
and references...) + risk to lose/forget around that singularity
(the p row whose pid is -1).

b) add another column to c (usedefault) that split 1), 2) from 3)
but that oblige me to take care of coherence of values in pid and
usedefault and opening the possibility for not atomic operation on
(userdefault,pid).

I could write a trigger that:
for every change of pid try to adjust userdefault and for every
change of userdefault check if it is coherent and raise an error. But
then I'd have to handle the error.
I could access the table just through a function that map
_pid>0 -> usedefault=false, pid=_pid
_pid=null -> usedefault=null,pid=null
_pid<0 -> usedefault=true, pid=null

I was wondering if there is any standard technique to deal with
similar problems or some postgresish way to deal with them.

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