Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
Erwin Brandstetter [EMAIL PROTECTED] writes: I postulate further that a king only be king of his own people (rules out multiple kingships, too). That's not how it's worked in the past :) If you have a nation table wouldn't you just have a king_id column in that table which is a foreign key reference to man_id? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
On Jun 5, 8:35 am, [EMAIL PROTECTED] (Gregory Stark) wrote: Erwin Brandstetter [EMAIL PROTECTED] writes: I postulate further that a king only be king of his own people (rules out multiple kingships, too). That's not how it's worked in the past :) Yeah i know. :) That's why I had to postulate this one explicitly. If you have a nation table wouldn't you just have a king_id column in that table which is a foreign key reference to man_id? Have a look at my model 3.) above .. Regards Erwin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
On Jun 5, 5:10 am, Lew [EMAIL PROTECTED] wrote: Erwin Brandstetter wrote: CREATE TABLE king ( king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE CASCADE ON DELETE CASCADE, nation_id INTEGER UNIQUE, FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); I like this. On Jun 5, 5:10 am, Lew [EMAIL PROTECTED] wrote: Erwin Brandstetter wrote: CREATE TABLE king ( king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE CASCADE ON DELETE CASCADE, nation_id INTEGER UNIQUE, FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); I like this. On a second inspection, I had a typo in the code above, and the second foreign key is redundant. So we get: CREATE TABLE king ( man_id INTEGER PRIMARY KEY, nation_id INTEGER UNIQUE, FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); (...) We are still avoiding circular references. I'm not so sure we need to avoid that. Yeah, I don't think we have to avoid it. But as it comes at no cost, I'd take it. I have commented on possible complications arising from circular references above. Regards Erwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
Hi Lew! Thank you for your comments. I have elaborated on them. On Jun 3, 7:22 pm, Lew [EMAIL PROTECTED] wrote: (...) The trouble with this is that it models kingship as an attribute of every man. (What, no female rulers allowed?) Yeah, saddening, isn't it? Actually, for simplicity's sake I restricted my model to a male, monarchistic world. 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. You are right, of course. (I switch to nation instead of people in my examples like you did, as the term seems clearer.) However, in your SQL model, you extracted nationality instead of kingship. If every man has to be member of exactly one nation (which I postulate), nationality can reside with the man. (we need man.nation_id instead of nation.man_id) That leaves only the kingship to be allocated. I postulate further that a king only be king of his own people (rules out multiple kingships, too). So the king needs only to have 1 attribute: man_id. To make room for other roles, as you mentioned, I include a role_id. However, roles must be as unique like the kingship. To enforce uniqueness of one king (or other role) per nation I include the seemingly redundant nation_id and impose a UNIQUE (nation_id, role_id) on it. To enforce that a man can only become king of his own people, I wrap both (man_id, nation_id) in a FOREIGN KEY constraint on man. PostgreSQL therefore requires a corresponding (redundant) UNIQUE (nation_id, role_id) on man. !NOTE that I do NOT reference table nation, so we have no circular foreign-key constraints! 0.) Lets number the models: CREATE TABLE nation ( nation_id INTEGER PRIMARY KEY ); CREATE TABLE man ( man_id INTEGER PRIMARY KEY, nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TABLE role -- role is non-reserved word in postgresql or SQL2003, but reserved in SQL99 ( man_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE CASCADE ON DELETE CASCADE, nation_id INTEGER, role_id INTEGER, UNIQUE (nation_id, role_id) FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); This makes sense if we have a lot of men per nation and an unknown number of unique roles per nation. I will simplify this model step by step now, along with simplified conditions: 1.) First, lets get rid of multiple roles. My model only needs kingship. So I replace table role with the following table king (the rest is the same). : CREATE TABLE king ( king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE CASCADE ON DELETE CASCADE, nation_id INTEGER UNIQUE, FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); 2.) Now we can further simplify the structure. Skip the table king and merge kingship as an attribute into table man. This makes sense with one (or a small number of ) known role(s). Adds a field to _every_ man and gets rid of one tuple per king and the overhead for that extra table. Whether this is preferable over 1.) depends on the typical number of men per nation. If there is more than just a few, you should stick to 1.). If there is only a few, however, you gain something. Note, how we reference nation(nation_id) twice (!), but only one time is NOT NULL. We are still avoiding circular references. CREATE TABLE man ( man_id INTEGER PRIMARY KEY, nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE CASCADE ON DELETE CASCADE, king_id INTEGER UNIQUE REFERENCES nation (nation_id) ON UPDATE CASCADE ON DELETE CASCADE, CHECK ((nation_id = king_id)) -- needed to make sure a man can only become king of his own people. ); 3.) As an improvement over 2.) we can merge kingship into nation (as you suggested). Note the ON DELETE SET NULL clause, that allows a king to die. Actually I would pass on kingship to another man (or NULL if none are left) per trigger, much like in my initial post: trg_mankind_delaft(). Note also that king_id isn't NOT NULL, so we need to be prepared for nations without a king (king_id IS NULL). To enforce a king we'd set it NOT NULL DEFAULT 0, but then we'd need a dummy man with man_id = 0 to serve referential integrity and that's where the circular references begin to bite. Because the dummy man needs a nation first. This could only be solved by entering a dummy nation and a dummy man before enforcing referential integrity. We also need triggers BEFORE INSERT AND UPDATE to check that the king is member of the nation IF NEW.king_id IS NOT NULL AND nation_id IS DISTINCT FROM NEW.nation_id FROM man WHERE man_id = NEW.king_id THEN RAISE EXCEPTION 'Usurper!'; END IF; Now we have to store only one field per nation and not
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
On Jun 2, 2:43 am, [EMAIL PROTECTED] (Erwin Brandstetter) wrote: raise warning '%', kings; And remove this line of debug code. /Erwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] There can be only one! How to avoid the highlander-problem.
RETURN was missing in the AFTER triggers. here is the corrected version: - begin of code 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 ); Only one king per people /* But no partial UNIQUE INDEX, because my solution needs temporary duplicates. Peoples will have to trust the triggers. I _could_ implement it with a DEFERRED table constraint, IF partial indices were supported with table constraints, but they are not in pg 8.1.x or 8.2.x. Pseudo-Code example: ALTER TABLE mankind ADD CONSTRAINT mankind_people_uni_king_idx UNIQUE (people_id) WHERE king[ = true] DEFERRABLE INITIALLY DEFERRED; I create (a non-unique) index anyway, to speed up the triggers. */ CREATE INDEX mankind_king_idx ON mankind (people_id) WHERE king; trigger BEFORE UPDATE To keep it simple we make world racist. Men cannot migrate. CREATE OR REPLACE FUNCTION trg_mankind_upbef() RETURNS trigger AS $BODY$ BEGIN IF NEW.people_id OLD.people_id THEN -- NOT NULL allows RAISE EXCEPTION 'This is a racist world! Men cannot migrate.'; END IF; IF NEW.man_id OLD.man_id THEN-- NOT NULL allows RAISE EXCEPTION 'A man has only one life and cannot change his identity.'; END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER upbef BEFORE UPDATE ON mankind FOR EACH ROW EXECUTE PROCEDURE trg_mankind_upbef(); trigger AFTER UPDATE CREATE OR REPLACE FUNCTION trg_mankind_upaft() RETURNS trigger AS $BODY$ DECLARE kings int4; BEGIN IF NEW.king OLD.king THEN -- NOT NULL allows kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id AND king; raise warning '%', kings; IF kings = 1 THEN --do nothing; ELSIF kings 1 THEN RAISE EXCEPTION 'You must make another man king to get rid of the old king!'; ELSIF kings 1 THEN UPDATE mankind SET king = FALSE WHERE people_id = NEW.people_id AND man_id NEW.man_id-- God save the new king! AND king; END IF; END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER upaft AFTER UPDATE ON mankind FOR EACH ROW EXECUTE PROCEDURE trg_mankind_upaft(); trigger BEFORE INSERT CREATE OR REPLACE FUNCTION trg_mankind_insbef() RETURNS trigger AS $BODY$ BEGIN IF NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = NEW.people_id) THEN NEW.king := true;-- firstborn is always king. END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER insbef BEFORE INSERT ON mankind FOR EACH ROW EXECUTE PROCEDURE trg_mankind_insbef(); trigger AFTER INSERT CREATE OR REPLACE FUNCTION trg_mankind_insaft() RETURNS trigger AS $BODY$ DECLARE kings int4; BEGIN kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id AND king; IF kings = 1 THEN --do nothing; ELSIF kings 1 THEN UPDATE mankind SET king = FALSE WHERE people_id = NEW.people_id AND man_id NEW.man_id -- God save the new king! AND king; ELSIF kings 1 THEN -- actually, should never occur, because of trigger BEFORE INSERT UPDATE mankind SET king = TRUE WHERE man_id = NEW.man_id;-- the new man is as good a king as any. END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER insaft AFTER INSERT ON mankind FOR EACH ROW EXECUTE PROCEDURE trg_mankind_insaft(); trigger AFTER DELETE (if old king dies) CREATE OR REPLACE FUNCTION trg_mankind_delaft() RETURNS trigger AS $BODY$ BEGIN -- We trust the triggers and do not check if there was another king, as there can be only one. -- AND NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = OLD.people_id AND king) IF OLD.king THEN UPDATE mankind SET king = true WHERE man_id = (SELECT man_id FROM mankind WHERE people_id = OLD.people_id LIMIT 1); END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER delaft AFTER DELETE ON mankind FOR EACH ROW EXECUTE PROCEDURE trg_mankind_delaft(); - end of code /Erwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
