Обсуждение: referential integrity and defaults, DB design or trick
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
On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo <mail@webthatworks.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, > 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 I still can't come up with something that satisfy my aesthetic. One way to use the second method would be to access the c table just through a function that will accept an int>0, int<=0 or null and convert it to * |<=0 use default, set usedefault=true, pid=null * >0 use pid set usedefault=false, pid=int * null set usedefault=null, pid=null opinions? Still I know it is very lightly related to postgres but any place I could learn something about DB design that have some example for postgres? thx -- Ivan Sergio Borgonovo http://www.webthatworks.it
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@webthatworks.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, >> 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? Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Thu, 20 Dec 2007 09:55:29 -0600 Erik Jones <erik@myemma.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@webthatworks.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
Ivan Sergio Borgonovo wrote: > * 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 > It's still not entirely clear (to me, anyway) Do you want something where every day, each user can: 1. Select their "default property" OR 2. Select from a shortlist of other properties OR 3. Not select anything CREATE TABLE properties ( pid serial, name text, PRIMARY KEY (pid) ); CREATE TABLE user_default_property ( uid int NOT NULL REFERENCES users, pid int NOT NULL REFERENCES properties, PRIMARY KEY (uid) ); CREATE TABLE user_property_choices ( uid int NOT NULL REFERENCES users, pid int NOT NULL REFERENCES properties PRIMARY KEY (uid) ); That allows you to have 0 or 1 defaults per user and 0 or 1 choices per user too. -- Richard Huxton Archonet Ltd
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@myemma.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@webthatworks.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
On Thu, 20 Dec 2007 17:53:23 +0000 Richard Huxton <dev@archonet.com> wrote: > CREATE TABLE properties ( > pid serial, > name text, > PRIMARY KEY (pid) > ); > CREATE TABLE user_default_property ( > uid int NOT NULL REFERENCES users, > pid int NOT NULL REFERENCES properties, > PRIMARY KEY (uid) > ); > CREATE TABLE user_property_choices ( > uid int NOT NULL REFERENCES users, > pid int NOT NULL REFERENCES properties > PRIMARY KEY (uid) > ); > That allows you to have 0 or 1 defaults per user and 0 or 1 choices > per user too. Thanks, this led me to insist on an idea I abandoned mislead by bad data quality, looking at written code, no matter how simple it is is better than insisting in speculating in your mind hoping you'll avoid refactoring. The default property (that is actually made by several fields) in my case is not completely homogeneous with the others, because it has a double meaning. It is cleaner to split the meanings and the data. This will even give me a chance to avoid completely the concept of default property. thanks, I think you put me on the right path. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote: > The default property (that is actually made by several fields) in my > case is not completely homogeneous with the others, because it has a > double meaning. > It is cleaner to split the meanings and the data. It usually is. A lot of the design decisions you can regret a year or two down the line are from trying to use one value for two purposes. -- Richard Huxton Archonet Ltd
On Fri, 21 Dec 2007 08:19:08 +0000 Richard Huxton <dev@archonet.com> wrote: > Ivan Sergio Borgonovo wrote: > > The default property (that is actually made by several fields) in > > my case is not completely homogeneous with the others, because it > > has a double meaning. > > It is cleaner to split the meanings and the data. > > It usually is. A lot of the design decisions you can regret a year > or two down the line are from trying to use one value for two > purposes. Unfortunately it looked a nearly perfect solution till I didn't discover there is some overlap of properties definitions. One of the column of a default property is written somewhere else, while a "not default" property has its own or doesn't have it at all. I can't see any way to write an "on update cascade" somewhere in your proposed schema. Unfortunately I'm writing a plug-in and I've no complete control over the schema. I can't separate that column, put it in another table etc... Decoupling the system as much as making all the angels of programming sing in chorus, is going to make the design less maintainable than a little hack. /* sort of... */ CREATE TABLE users ( uid serial primary key, email varchar(64) unique -- I'can't move this away, not my table! ); CREATE TABLE properties ( pid serial, name text, email varchar(64), -- but not default proprieties have their own PRIMARY KEY (pid) ); CREATE TABLE user_default_property ( uid int NOT NULL REFERENCES users, pid int NOT NULL REFERENCES properties, PRIMARY KEY (uid) ); CREATE TABLE user_property_choices ( uid int NOT NULL REFERENCES users, pid int NOT NULL REFERENCES properties PRIMARY KEY (uid) ); At this moment the best solution I can think of is hiding this behind a function. CREATE TABLE users ( uid serial primary key, email varchar(64) unique -- I'can't move this away, not my table! ); CREATE TABLE properties ( pid serial primary key, name varchar(64), email varchar(64), otherstuff text, ); create table user_property_choices ( cid serial primary key, uid int NOT NULL REFERENCES users, assigned boolean, pid int NULL REFERENCES properties ); if assigned=true -> if pid=null use default -> if pid!=null use pid if assigned=false -> not yet assigned better than using "usedefault" system since anyway there will be something to pick up and at least I won't have to deal with exceptions. Thanks again. -- Ivan Sergio Borgonovo http://www.webthatworks.it