Re: referential integrity and defaults, DB design or trick

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