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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster