Erwin Brandstetter wrote:
CREATE TABLE mankind
(
man_id integer primary key,
people_id integer NOT NULL, -- references table people .., but that's irrelevant here ..
king boolean NOT NULL DEFAULT false
);
The trouble with this is that it models "kingship" as an attribute of every
man. (What, no female rulers allowed?) The overhead of being "not king" is
carried in every "mankind" record. This may suffice for your particular model,
but if you were designing for evolution you'd have a problem. Every new
attribute of "mankind" would need a new column in the table - "isDuke",
"isNoble", "isHogSlopCleaner".
I would model "kingship" (or other attributes) in a separate table and use
PRIMARY KEY to enforce, or a TRIGGER - there is a large, possibly unbounded
set of ways to do this.
Here's one attempt, feel free to rate it good, bad or ugly (I am ignoring my
mistrust of auto-incremented integer surrogate keys):
CREATE TABLE mankind
(
man_id INTEGER PRIMARY KEY
-- kings belong to nations, not vice versa
-- , other information about a man
);
CREATE TABLE nationalities
(
man_id INTEGER FOREIGN KEY REFERENCES mankind ( man_id ),
nation_id INTEGER FOREIGN KEY REFERENCES nations ( nation_id ),
PRIMARY KEY ( man_id, nation_id )
);
CREATE TABLE nations
(
nation_id INTEGER PRIMARY KEY
, king INTEGER FOREIGN KEY REFERENCES mankind ( man_id )
-- , other information about a nation
, FOREIGN KEY ( king, nation_id )
REFERENCES nationalities ( man_id, nation_id )
);
The circular foreign-key relationships might be problematic - would someone
comment on that?
To handle that I would ensure that any transaction that updates
"nations (king)" checks that the pretender's "man_id" is already correctly
entered in "nations".
--
Lew
