Re: referential integrity and defaults, DB design or trick

[Richard Huxton <dev(at)archonet(dot)com>]

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