?

Log in

No account? Create an account
Apparently my relationships do not actually exist. - helen-louise
baratron
baratron
Apparently my relationships do not actually exist.
15 comments or Leave a comment
Comments
From: ext_221050 Date: 17th February 2011 14:27 (UTC) (Link)

Our database servers have master/slave relationships

-- ANTIPATTERN: Monogamy
CREATE TABLE SimsPeople (
sim_id SERIAL PRIMARY KEY,
[...]
mated_to BIGINT UNSIGNED,
FOREIGN KEY (mated_to) REFERENCES SimsPeople(sim_id)
);

-- SOLUTION: Use an intersection table for a many-to-many relationship
CREATE TABLE SimsPeople (
sim_id SERIAL PRIMARY KEY,
[...]
);
CREATE TABLE SimsMatings (
sim_id BIGINT UNSIGNED NOT NULL,
partner BIGINT UNSIGNED NOT NULL,
is_legally_married BOOLEAN,
PRIMARY KEY (sim_id, partner),
FOREIGN KEY (sim_id) REFERENCES SimsPeople(sim_id),
FOREIGN KEY (sim_id) REFERENCES SimsPeople(sim_id),
[...plus more constraints to enforce is_legally_married reciprocity, one is_legally_married per sim_id, etc...]
);
From: ext_221050 Date: 17th February 2011 14:31 (UTC) (Link)

Re: Our database servers have master/slave relationships

Note: is_legally_married should DEFAULT FALSE, since that's an awkward question to have to answer when INSERTing a new row. Easier to just pretend that column isn't there.

(Also, I should really change my From: name on here, eh? -HoopyCat)
mattp From: mattp Date: 17th February 2011 15:51 (UTC) (Link)

Re: Our database servers have master/slave relationships

Have you seen the fuller treatise written a couple of years ago from a DBA point of view : http://qntm.org/gay ?
From: ext_221050 Date: 19th February 2011 23:49 (UTC) (Link)

Re: Our database servers have master/slave relationships

I had not seen that, but as someone who spends Saturday afternoons preparing tax returns for fun, I will note that database schemas are the absolute last thing on the minds of lawmakers.

I don't blame the tax software for its foibles any more, that's for sure. -rt

P.S.: I'll just leave http://mytaxwise.blogspot.com/ here and see who runs away screaming first.

From: ext_221050 Date: 19th February 2011 23:52 (UTC) (Link)

Re: Our database servers have master/slave relationships

P.P.S.: I just figured out how to change my displayed name! Hooray!
baratron From: baratron Date: 17th February 2011 14:31 (UTC) (Link)

Re: Our database servers have master/slave relationships

*lol*, I almost deleted this as spam!
15 comments or Leave a comment