Oh my, it took me a ton of text to finally come up with a better idea.
5.) The Sun King solution
"L'etat c'est moi!". The model is as simple as can be:
CREATE TABLE nation
(
nation_id SERIAL PRIMARY KEY
);
CREATE TABLE man
(
man_id SERIAL PRIMARY KEY,
nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE
);
Instead of drawing his man_id from the sequence, a king copies the
nation_id. Kingship can be tested by (man.man_id = nation.nation_id)
or (man.man_id = man.nation_id).
(Yeah, he is bound to come to mind here: http://en.wikipedia.org/wiki/Sun_King)
If you know the man_id of the king, you also know the nation_id, and
vice versa.
The caveat is, you have to make sure that the two sequences for nation
and man yield mutually exclusive values. One absolutely reliable way
would be to attach both primary keys to one sequence. This just works.
But, you don't have to stop at that. If you can guarantee that nation
will never burn more that, say, 100 000 nation_id's, and sequence wrap-
around is otherwise no concern, you can keep two separate sequences,
start nation_id at 1 and man_id at 100 000. Now you also know a king
when you see one: (man_id < 100 000) is king.
If the kingship of a nation is passed around, though, this can be a
problem. You could guard yourself against that with ON UPDATE CASCADE
for every foreign key constraint referencing man.man_id. But it would
be asking for trouble, still.
If you can meet both conditions - I have such cases here -, then go
with this one. Fastest, simplest, smallest.
Regards
Erwin
