Your relational schema fails to reflect the symmetry of the relationship, doesn't account for the death of one or both partners, and makes queries for even simple things like family-in-law difficult.
You'd be well served to start constructing your ontologies in something more suited for the task, like Prolog.
Name:
Anonymous2009-03-04 16:42
id
partner_1_id
partner_2_id
Calling everything blablasomenumber_id is a sure way to confuse the hell out of yourself and/or break things inadvertently. The same applies doubly for just calling a field "id".
Use meaningful field names, such as: marriage_id
husband_id
wife_id
That both indicates specifically what "id" you're talking about in all three cases, so you don't have to backtrack or draw pictures of your joins when you have a big mess of unwieldy sql referring to the same tables multiple times with different aliases. For example, you might've aliased humans as h1, h2, h3, and h4, and binary_marriages as m1 and m2. It's far too easy in such a scenario to erroneously refer to the wrong column, e.g. h2.id=m1.id. (WHOOPS!) I have seen it happen more than enough times.
You might be able to design your queries properly and pay close attention to the names you pick for your table aliases, but the other guy on your team will inevitably prove himself to be utterly incompetent, and a good, defensive table design can mean all the difference between a well-formed query that works right and a huge fucking mess that takes an hour and ten aspirin to untangle.
Also, in your scenario, there's no specified order for the partner_ids -- so what happens if one person does INSERT INTO binary_marriages (partner_1_id, partner_2_id, marriage_date) VALUES (1848, 3179, '2009-03-04'), and then later on someone else tries to INSERT INTO binary_marriages (partner_1_id, partner_2_id, marriage_date) VALUES (3179, 1848, '2009-03-04'). Now you have two marriages. Best to specify what id is supposed to go where, and use constraints to enforce it. (In the general case, when I have some table joining two individual rows from the same source table, I add a constraint ensuring that something_1_id < something_2_id, or something to that effect.)
struct human {
long int id;
int gender; // 0=none, 1=male, 2=female, 3=herm, 4=other
char * name;
}
struct marriage {
human * member;
void * married_to_this; // set to another marriage struct if married to another human, or NULL if unmarried or already part of existing marriage
}
>>15
Feito-chan is a clone and Nanoha is a devil. I expect a revised data structure in the next two hours or I sue for discrimination and offensive close-mindedness.