Re: referential integrity and defaults, DB design or trick

On Thu, Dec 20, 2007 at 06:31:47PM +0100, Ivan Sergio Borgonovo wrote:
> 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,
> > >> 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 ;)

I'll not complain about that anyway.

I still don't understand what "singularity" means though. And your
table names haven't improved much. I think you mean something like
this, but I'm not sure:

CREATE TABLE props (
name TEXT NOT NULL PRIMARY KEY,
defvalue TEXT
);

CREATE TABLE items (
name TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE itemprops (
itemname TEXT NOT NULL REFERENCES items,
propname TEXT NOT NULL REFERENCES props,
value TEXT
);

(I've gone to using natural keys after discussions on this list,
especially in simple examples like this they help to concentrate the
design on the essentials)

Assuming that's the case, you could represent the following as:

> c can contain:
> 1) sorry not chosen yet

no row in itemprop for this combination

> 2) pid

the value in itemprop is non-null

> 3) hey today I'm hungry as usual

the value in itemprop is null. use something like this to get the
current values for an item:

SELECT i.itemname, i.propname,
coalesce(i.value,p.defvalue) AS curval
FROM itemprops i, props p
WHERE i.propname = p.name
AND i.itemname = 'desk';

Spelling out identifiers with longer names really helps *a lot* when
you're an outsider trying to understand someones code. It also
short-circuits a lot of the ambiguity that will inevitably exist in the
description.

Sam