数据库
Gay marriage: the database engineering perspective
- en Italiano
- Liz Howard adapted this essay as a talk for DevFest Silicon Valley! Here are the slides.
There are various objections to expanding the conventional, up-tight, as-God-intended "one man, one woman" notion of marriage but by far the least plainly bigoted ones I am aware of are the bureaucratic ones.
To be blunt, the systems aren't set up to handle it. The paper forms have a space for the husband's name and a space for the wife's name. Married people carefully enter their details in block capitals and post the forms off to depressed paper-pushers who then type that information into software front-ends whose forms are laid out and named in precisely the same fashion. And then they hit "submit" and the information is filed away electronically in databases which simply keel over or belch integrity errors when presented with something so profound as a man and another man who love each other enough to want to file joint tax returns.
Speaking as a computery-type person, altering the paper forms is not my department. It's probably expensive and there are probably millions of existing incorrect forms which would need returning or recycling or burning instead of using. Or maybe it's simple. I don't know. The real question from my perspective is how you store a marriage in a computer.
Altering your database schema to accommodate gay marriage can be easy or difficult depending on how smart you were when you originally set up your system to accommodate heterosexuality only. Let's begin.
Note: By popular demand, this problem is now known as "Y2gay".
Note: this essay refers exclusively to government-recognised legal civil unions. Religious organisations are of course able to create, recognise and annul any wacky religious unions they can think of.Churches need databases too.
One
Let's start with a few really dumb systems which nobody with a brain cell would ever use. How about this?
`males` - `id` - `forename` - `surname` - `birthdate` - `wife_id` (foreign key references column `females`.`id`, may be NULL if male is unmarried) `females` - `id` - `forename` - `surname` - `birthdate` - `husband_id` (foreign key references column `males`.`id`, may be NULL if female is unmarried)
Great! Everybody is either married or unmarried and it's dead easy to see who is married just from a database lookup. A simple JOIN will give you the husband or wife.
Problem? Potential for contradictions. Duplication of information. If Male 45 (Jeff) has `wife_id` 699 then Female 699 (Elizabeth) must also have `husband_id` 45. What if she has NULL `husband_id`? Or, even better, has `husband_id` 1078 (Jeff's younger brother)? Oh, imagine the hilarity.
Two
Believe it or not, this is actually a fractionally less stupid database schema.
`males` - `id` - `forename` - `surname` - `birthdate` - `wife_id` (unique foreign key references column `females`.`id`, may be NULL if male is unmarried) `females` - `id` - `forename` - `surname` - `birthdate`
This reduces the scope for ambiguity but it has suddenly become eye-poppingly sexist. Plus, what if you want to store information pertaining to the marriage itself? Like, the date it began?
Three
`males` - `id` - `forename` - `surname` - `birthdate` - `wife_id` (foreign key references column `females`.`id`, may be NULL if male is unmarried) - `marriage_date` (may be NULL if male is unmarried) `females` - `id` - `forename` - `surname` - `birthdate`
Okay, but what if they get divorced?
Four
`males` - `id` - `forename` - `surname` - `birthdate` - `wife_id` (foreign key references column `females`.`id`, may be NULL if male is unmarried) - `marriage_date` (may be NULL if male is unmarried) - `divorce_date` (may be NULL if male is unmarried or married but not yet divorced) `females` - `id` - `forename` - `surname` - `birthdate`
Okay, but what if there's LOTS of information about the marriage? Like where it took place, who witnessed it, details of the licence? I have not been married but I'm sure the administrative tangle is quite large. All those extra fields would be attached to the `males` table, unless they were NULL. Wouldn't it be better to store marriage-related data in a dedicated table?
And the divorcees might each get married again! You'll still want to have that marriage on record, alongside the new one(s)! Completely erasing anything is a bad idea.
Five
`males` - `id` - `forename` - `surname` - `birthdate` - `marriage_id` (foreign key references column `marriages`.`id`, may be NULL if male is unmarried) `females` - `id` - `forename` - `surname` - `birthdate` - `marriage_id` (foreign key references column `marriages`.`id`, may be NULL if female is unmarried) `marriages` - `id` - `marriage_date` - `divorce_date` (NULL if marriage not ended)
This isn't quite so stupid as it could be. We are finally getting somewhere. Personally, I don't like NULLable foreign key columns so you could equally well go with:
Six
`males` - `id` - `forename` - `surname` - `birthdate` `females` - `id` - `forename` - `surname` - `birthdate` `marriages` - `id` - `husband_id` (foreign key references column `males`.`id`) - `wife_id` (foreign key references column `females`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended)
This makes slightly more sense. The `marriages` table can have much more information stored in it while `males` and `females` have all their own information in the logical place too.
Of course, the entire system is still profoundly stupid/sexist. Men and women are equal, correct? Then, any database column which the table `males` could need would be needed in the `females` column. More practically, this in turn means that all the application logic for any given person has to be bashed out twice, once for if the person is female and once for when they're male. Or at the very least a switch of some kind has to be incorporated to address the correct database table, and any change to one table must be reflected precisely on the other, and any additional table in this hypothetical database needs to be capable of referencing two different tables depending on the gender of the person being referenced, and so on...
It's asinine to do it this way. However, there is a good reason why I haven't just skipped schemas schemae schemata One to Six. There are a lot of people in the world who actually think like this. This is their for-real, no-joking conception of "marriage". They do not grasp that men and women are interchangeable, as a result of which homosexual marriages create repulsive integrity problems in their heads. "But if they're both guys, which one is the wife? Does not compute!" How sad.
Seven
`humans` - `id` - `forename` - `surname` - `birthdate` - `sex` ("male" or "female") `marriages` - `id` - `husband_id` (foreign key references a male in column `humans`.`id`) - `wife_id` (foreign key references a female in column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended)
Finally we are reaching something which is non-stupid and non-sexist enough that it might actually exist somewhere in reality. This schema is reasonably sensible assuming you live in a fairly God-fearing administrative district. There is actually a slight disadvantage from the previous schema in that to enforce a one-man-one-woman marriage, you would have to have some application logic to ensure that each `husband_id` doesn't point to a female and that each `wife_id` doesn't point to a male.
(And, I guess, you would also need to ensure that no married male changes to female, and that no married female changes to male. Or, if you were feeling nasty, that nobody ever changes sex at all. More on this later.)
Up until this point, implementing gay marriage in your schema has been remarkably difficult. But what we now have is different. To allow men and women to marry men and women respectively, all you actually have to do is remove those application-layer checks. For the sake of politeness you would most likely rename the database columns, too:
Eight
`humans` - `id` - `forename` - `surname` - `birthdate` - `sex` ("male" or "female") `marriages` - `id` - `partner_1_id` (foreign key references column `humans`.`id`) - `partner_2_id` (foreign key references column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended)
With the advent of gay marriage, however, we have a new problem. What we have now allowed is any human to marry any human. Note the conspicuous absence of the word "other" in that sentence. Marriage is a binary relation. You can't marry yourself.
Why not?
...Good question. Most would answer "this is obviously stupid" but "obvious" means "an answer springs to mind immediately" so here is mine.
To answer this you would have to step outside the database engineering scope of this essay and look at the rights and privileges that marriage confers on a human being. There are legal benefits, such as being able to visit your dying spouse in hospital or acquire power of attorney over them. These would obviously be pointless if you were your own spouse. But there are also tax breaks, which are obviously intended to benefit people - plural - who are actually committed to one another on a legal/dwelling/property/assets/children kind of level. For you to marry yourself confers no such commitment and is obviously just a tax scam. So yes-- marriage is binary. (Or, at the very least, not unary. Mathematically, "irreflexive". More on this later, too.)
So, after removing the "husband and wife" limitation, you would actually have to add in a check constraint or some new application logic to ensure that people didn't marry themselves. It would almost never be called upon but it would have to be in there, somewhere. This minor programming challenge is actually our largest obstacle.
Nine
Of course, we live in the twenty-first century, and in the words of Eddie Izzard, "there's gonna be a lot more guys with makeup during this millennium". Basically what I'm talking about is your non-conventional people, your non-male-non-female folks. Just having `sex` as a "male or female" choice is as short-sighted as having "marriage" as a "husband or wife" choice. You may need something like:
`humans` - `id` - `forename` - `surname` - `birthdate` - `sex_id` (foreign key references column `sexes`) `marriages` - `id` - `partner_1_id` (foreign key references column `humans`.`id`) - `partner_2_id` (foreign key references column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended) `sexes` - `id` - `string`
...where the latter table would contain such well-known sexes as "female", "male", "asexual", "hermaphrodite", "not stated" and leave room for juggling later, since gender roles will doubtless become more non-trivial as time passes.
In fact, the whole "gender"/"sex" thing is more complicated than this. As we all (should) know, "sex" is a strictly biological term referring primarily to the shape of the organs between your legs while "gender" is more of a mental identity or social role term, so let's include that too:
Ten
`humans` - `id` - `forename` - `surname` - `birthdate` - `sex_id` (foreign key references column `sexes`) - `gender_id` (foreign key references column `genders`) `marriages` - `id` - `partner_1_id` (foreign key references column `humans`.`id`) - `partner_2_id` (foreign key references column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended) `sexes` - `id` - `string` `genders` - `id` - `string`
...where the latter table would again include "male", "female", "not stated", "undecided" and whatever else this brave new century throws at us.
Come to think about it, all three of the gender in your head and the sex of your body and the clothes you wear are independent from one another! Why don't we just add another column for telling whether someone's a transvestite or not...
Hey! What am I saying! Isn't the whole point of this exercise to demonstrate that your shape is irrelevant to who you can marry? Drop those fields entirely!
Eleven
`humans` - `id` - `forename` - `surname` - `birthdate` `marriages` - `id` - `partner_1_id` (foreign key references column `humans`.`id`) - `partner_2_id` (foreign key references column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if marriage not ended)
Better.
As an aside, I have actually considered that laws against (or implicitly disallowing) gay marriage are, actually, sexist. For example, suppose I lived somewhere with antihomonuptial legislation. As I am a man, any woman in that district has the right to marry me. (As well they should.) But any man in that district who wanted to marry me does not have that right. The women have a right which the men do not have. Likewise, if there was a nubile woman nearby, I (and any other man) would have the right to marry that woman. But any nearby woman would not have that right. The men have a right which women do not have. Sexist!
Anti-gay-marriage laws throw a very real legislative dividing line between two sets of people on the world, and say, "all marriages must cross this line". But any law which divides men from women is clearly sexist, and, as I've stated above, closed-minded towards unconventional gender assignments who don't clearly fall on either side.
Speaking as a database engineer, the `partner_1_id` and `partner_2_id` fields make me feel edgy. I've worked on databases with an `email_1` and an `email_2` for primary and secondary email addresses and to me, these field names are just crying out for a third addition. And a fourth. And, ultimately, the capacity for arbitrarily many email addresses. Each time a new address is added the application logic ramps upwards in complexity because of the number of checks which have to be made ("So you're trying to enforce uniqueness of email addresses? Well, I hope you remember to check `email_1` against `email_2` and `email_1` against `email_3` and `email_2` against `email_3`...")
This is a check which hasn't even been mentioned yet. You have to ensure that each individual is only involved in one marriage. You can't have Jeff married to Elizabeth and Elizabeth also married to Bob. And you have to be careful about it. You have to make sure Jeff is either `partner_1` OR `partner_2` in at most one marriage. You even have to check for things like Jeff being married to Elizabeth and simultaneously Elizabeth being married to Jeff! This would be two separate marriages! That's not allowed.
Why not?
Twelve
...A very good question.
Let's take it slowly.
Polygamy.
For a marriage to involve precisely two people is as closed-minded as marriages involving people of opposing sexes. Why shouldn't a marriage involve more than two people? Admittedly, it's highly unconventional, and the sheer psychology of mere trigamy is highly complex; you have to be special to make a polymarriage work. But special people are out there and they have made it work so why not codify this legally? And electronically?
Here, "legally" is actually the biggest stumbling block. I think it would be accurate to say that much more of the existing global "legislatosaurus" is implicitly or explicitly geared towards binary marriages than is geared towards heterosexual marriages. This is not a case of changing a few words in the laws. It would be a case of radically modifying a very large chunk of law. The possibility for legal loopholes and general lack of airtightness would be major. And all of this would be in order to accommodate the legal needs of an admittedly tiny minority of people. I think it should happen (in the places where it hasn't), and the arguments against it are no better than the arguments against gay marriage, but the obstacles are larger.
But anyway. IANAL. IAADBE.
`humans` - `id` - `forename` - `surname` - `birthdate` `marriages` - `id` - `marriage_date` - `divorce_date` (NULL if marriage not ended) `marriage_partners` - `id` - `human_id` (foreign key references column `humans`.`id`) - `marriage_id` (foreign key references column `marriages`.`id`)
On paper, this would be a relatively straightforward table to create and populate. (In practice? Hahahah.) This schema effectively creates "blobs" (not the database engineering Binary Large OBjects) of people who are all collectively married to one another. Each human is a member of at most one marriage. This can be easily enforced at the database level by making `marriage_partners`.`human_id` into a unique key. A marriage could have any number of members; no coding would be needed to enforce this.
To prevent people doing the old "unary marriage trick" and marrying themselves - or, in this model, creating a single-human marriage blob - you would need to ensure that each marriage had at least two members, and this last thing would be the only major problem of application logic.
Cool. Great.
Assuming that marriages are static in population.
Here we run into a perfectly typical problem of adapting "2 things" to "N things". Up until now, Jeff and Elizabeth were either (A) married or (B) not married. If the marriage between them became annulled, (A) goes to (B). But now we can have a situation where a marriage blob is formed with Jeff and Elizabeth... then Bob may join them both at a later date. What do we put for a marriage date there? What if Bob then divorces the other two, but Jeff and Elizabeth stay together?
This would be easily done in practice. Simply have the original marriage-of-two marked annulled. Then create a new marriage-of-three starting from the same precise date. And when someone leaves, annul the marriage-of-three and create a new marriage-of-two. But it feels like a hack and it seems to be legally complex. At the end of it, Jeff and Elizabeth have technically had three marriages each despite staying civilly unified for a continuous period, and their current one is maybe years younger than it should be. Yikes!
Can we accommodate this?
Thirteen
`humans` - `id` - `forename` - `surname` - `birthdate` `marriages` - `id` `marriage_partners` - `id` - `human_id` (foreign key references column `humans`.`id`) - `marriage_id` (foreign key references column `marriages`.`id`) - `marriage_date` - `divorce_date` (NULL if still in marriage)
This schema is much more sophisticated. A marriage blob is formed. Initially, at least two people, Jeff and Elizabeth, are members - this would be enforced at the application logic level. They have rows in the `marriage_partners` table. As time passes, Bob joins, creating another row in the `marriage_partners` table with the same `marriage_id`. He may then leave, inserting a `divorce_date`. He may even join again: this would be a new `marriage_partners` row entirely. He would have had two discontinuous marriages, though they would involve the same people. Then Elizabeth might leave. She would only have one `marriage_partners` row listed, and so, technically, would only have one marriage to her name. Jeff and Bob would be left behind. And finally, neither Bob nor Jeff could leave individually; both of them would have to leave the marriage blob simultaneously. Again, this would have to be enforced at the application logic level.
(And of course you would need to ensure that at any given moment in time, each person was a member of only one marriage. ...Right?)
Pretty sneaky, sis!
I can still imagine some potential complications - for example, what if Bob and Jeff were involved in one marriage, and Elizabeth and Daphne were involved in another, and then Jeff decided to marry Elizabeth? You would need to provide for some way for Daphne to be "pulled over" to join Bob and Jeff's marriage too. Or for Bob to be "pulled over" and joined in with Elizabeth and Daphne's marriage too. Or, for equality's sake, for all four of them to dissolve their current marriages and join an entirely new, four-person marriage blob.
Again, you could hack it, but you would really have no choice but to introduce arbitrary, instantaneous temporary divorces into the system. And it would be unavoidable that there would be no continuity. At least one, and possibly both, of the original marriage blobs would be ended permanently in the merger. Because marriage is not just an irreflexive binary relation; it's a transitive, irreflexive, binary relation. If Jeff is married to Elizabeth, and Elizabeth is married to Bob, then Jeff is married to Bob.
Right?
Fourteen
(Hell's bells, he's still going.)
The legal ramifications of what I'm about to describe are unguessable. I have no idea what rights a civil union like the ones which would be possible below would have, nor do I have any idea what kind of transhuman universe would require so complex a system. This is the marriage database schema to take us up to the thirty-first century, people.
Right. Intransitive marriage.
`humans` - `id` - `forename` - `surname` - `birthdate` `binary_marriages` - `id` - `partner_1_id` (foreign key references column `humans`.`id`) - `partner_2_id` (foreign key references column `humans`.`id`) - `marriage_date` - `divorce_date` (NULL if still married)
In a transitive marriage, everybody is married to everybody else.
A transitive marriage is begun by creating a binary link: Jeff to Elizabeth, say.
Bob can join this transitive marriage simply by simultaneously marrying them both. Bob marries Jeff (new `binary_marriages` row) and marries Elizabeth (new `binary_marriages` row). Bob could then leave the transitive marriage (marking both rows "divorced") and then join it again (creating two new rows).
If Daphne and Charles - themselves married - joined the transitive marriage, Daphne would have to file paperwork marrying Bob, Jeff and Elizabeth, and Charles would also have to file paperwork marrying each of Bob, Jeff and Elizabeth.(This would be troublesome, but these larger marriages are exponentially less likely to occur...)
But that doesn't have to happen. Daphne could just marry Elizabeth. And that would be that.
What we end up with is a thing called a graph. A graph is a mathematical object consisting of a set of points (people) and a set of lines (marriages), each connecting two points. Up until now we have assumed that all points are either red (male) or blue (female) and all lines (marriages) must join a red point (husband) to a blue point (wife). Since then, we have acknowledged the existence of other colours (intersexed people and the like) and gone on to acknowledge that the colour of the points (sex of the partners) is irrelevant and that lines (marriages) may connect any two points (people) regardless of colour (sex).
We then realised that allowing each point (person) to connect to only at most one other point (marry one other person) is limiting, and now allow literally any combination of lines (marriages) to connect points (people) in any shape imaginable. A traditional binary marriage is still the most common figure. A triangle (three people all married to one another) pops up occasionally. But, in theory (and the database allows this), any binary marriage or triangle or square or any shape at all can connect with any other shape. Line segments (marriages) can appear and disappear (begin and end) at any time.
The fact that we still have a partner_1_id and a partner_2_id is still edgy, but the reason for this no longer exists. We are no longer limited to binary marriages since, clearly any conceivable combination of married people can be created using a combination of binary marriages.
We would still need a little application logic, of course, to ensure that nobody marries the same person twice at the same time. Because, of course, you can't be double-married.
Without loss of generality, you could make it so that the partner_1_id is a lower number than the partner_2_id. This would make searches easier. Because the order of the partners doesn't matter. Marriage may be many things, but it is certainly commutative: if Jeff is married to Elizabeth, then Elizabeth is married to Jeff. Right?
Afterword
Well, that started as a stream of consciousness about equal parts nuptial rights and Structured Query Language and finished up moving into graph theory, something I, for one, did not see coming. Noncommutative - or, I guess, unequal - marriage, in which one partner is considered legally, somehow, lesser than the other (perhaps Jeff would receive Elizabeth's property if she died, but not vice versa?), sounds like a logical progression on the theme to me one minute, and a recipe for a renewed and hardy breed of institutionalised sex discrimination the next. In fact, thinking about it, even intransitive marriages would most likely be susceptible to this.
Perhaps the simplest solution would be to ban marriage outright. Or, better yet, to declare everybody as married to everybody else. But then what would the database engineers do all day?
Final thought
The real crime is that I'm not allowed to marry my database.
Discussion (162)
2008-11-21 01:54:02 by Artanis:
That was pretty cool. Things like this is what keeps me coming back. This and your fiction.
As to the "ban all marriage," I've thought similarly recently. It occurred to me that the whole argument is over one damn word, which could probably be removed from law with few easily reconciled consequences. I don't think polygamy is a particularly good idea (at least at this time,) but I say let anyone 'marry' whoever they want, and they can call it a 'marriage' or a 'civil union' or whatever, because far as the law is concerned they just have a set of rights and file joint taxes.
2008-11-21 01:59:23 by Jake:
One of my TAs insisted that marriage should be noncommutative, and that people should be able to marry inanimate objects. I'm still not entirely sure he was joking. :\
2008-11-21 10:38:07 by DavidMacIver:
The polygamous version of the schema might be made easier if you consider everyone to be married to themselves. In a world where polygamous marriage has legal recognition it seems obvious that you have to update the tax break laws to consider the number of people in the marriage, so why not just make it so a marriage with 1 normalises to no tax breaks?
Why do we want to do this? Well, simple: This has the very nice property that every person is not only a member of *at most* one marriage, but also of *at least* one marriage: An unmarried person simply belongs to a marriage group with only one member. Thus marriage_partners goes away and marriage_id becomes a non-nullable foreign key on humans, along with date_entered_marriage or something like that. Marriages acquires a "divorced" flag indicating that the marriage is no longer valid. Marriage and divorce are now simple matters of merging two marriage groups to form a single new marriage group and splitting a marriage group into two new marriage groups and marking the old marriage as divorced (and some boring emotional stuff).
2008-11-21 11:37:53 by Eddy:
Why are you focused on tax? Tax has got only little to do with marriage...
Marriage is most often done in regard of having children; to ensure financial support (in case you wonna run off).
In your Variables you don't take children into account..
Marriage is more or less used as an insurance policy..
In a country such as Gambia.. you can merry for one day.. this turns a supposingly prostitute into a temporary wife; marriage for justification.
Good luck !
2008-11-21 11:45:59 by Steve:
Sam, Will you marry me?
2008-11-21 12:07:46 by Eddy:
Best what you do.. for now..
Get all the variables that you ask about a person.. and calculate those in a more intelligent way.. living apart together, living together apart, adopted children, married with children from other marriages etc. .. why narrow down your variables ?
Good luck !
2008-11-21 12:49:03 by Sam:
Steve: we'd only end up hurting each other
2008-11-21 12:53:51 by lol:
You could have left 1 to 6 out. Seriously...
2008-11-21 12:53:57 by pozorvlak:
In fact, poly relationships don't have to be transitive. For instance, I know one, er, set, let's call them D, E and L. D and E got together, then L got together with both D and E, creating a transitive triple; then D broke up with L, leaving E and L in a relationship and D and E in a relationship, but L and D not in a relationship.
I have no idea how they manage this.
2008-11-21 13:18:08 by Sam:
lol: There's a good reason I kept 1 to 6 but it needed clarification, which I've added. Thanks.
2008-11-21 14:48:07 by Slacko:
"Repulsive database integrity problems in their heads". Sam, I would totally gay marriage you.
2008-11-21 15:05:46 by Hank:
At this point marriage isn't what you are keeping track of it is sex partners. You shouldn't be keeping track of marriage/divorce dates, but when the two slept together. That way if a STD is discovered (which is pretty much a given in any large graph) you can trace who else might have it, to get them treatment. (Though you should also put neddle sharings)
Getting people to enter this data is left as an exercise for the reader.
2008-11-21 15:14:41 by Sam:
Quite frankly, Hank, building an exhaustive database of sex acts and partners is a much more mind-boggling proposition. There are so many preposterous combinations, collaborations, manoeuvres and interactions possible in the bedroom - and elsewhere - that one would no choice but to use an entity-attribute-value model.
2008-11-21 15:43:21 by Russ:
That was really interesting, and led me to reminiscing about Heinlein books I have loved.
2008-11-21 16:07:08 by Peter:
You never store Business Logic in your Database!
2008-11-21 16:43:00 by Oliver:
Randall Munroe put it very well in a different context. I'm paraphrasing, but the idea is this:
If you ever stumble upon a frustrating construct, simply remember that it probably drove some engineer(s) completely bonkers.
I guess that sentiment can now be extended to include marriage. Well done.
2008-11-21 17:02:18 by Will:
Before I get into this, let me state that I am pro gay marriage. For 6, 7 and 8 you are missing the only actual, everyone clearly agrees on the limit of who can marry each whom, constraint. Men are naturally restricted in their choice of marriage partners, and women are also. You can not merely choose "someone of opposite gender". A male can't marry any "unmarried female".
Specifically, you can't marry people who are genetically related to you, because, even without our understanding of recessive genes work, that shit is just gross. Sisters, brothers, mothers, fathers, grandfathers, etc. I'm not sure where the law falls in with cousins, but you see what I'm saying. So this argument that "rights that you don't have" etc isn't exactly as clear cut as you make it out to be, because straight people have marriage restrictions also. Those restrictions are not merely a reflection of a deep seated taboo -- like homophobia -- but unlike homophobia, it actually has a biological basis.
I'm just saying... the datamodel isn't going to match, there's always going to be app logic. Unless you want to model a biological family tree, which takes into account the fact that people sleep around...
2008-11-21 17:05:42 by mmas:
You also need to ensure via application logic that nobody marries someone who is too closely related to them, only for the sake of preventing birth defects.
2008-11-21 17:07:38 by Adam:
Great read!!
2008-11-21 17:20:20 by Sam:
The close relations thing is a problem of sex, not a problem of marriage. Preventing the latter doesn't prevent the former.
2008-11-21 18:23:28 by Fabien:
I don't know why everyone is focused on the word "marriage". And I don't know why your database shemas are so specific.
Your table `humans` (from, say, #14) is correct.
Now instead of a `binary_marriages` table, let's make a `binary_partnerships` table:
- `id`
- `type_of_partnership`
- `partner_1_id`
- `partner_2_id`
- `start_date`
- `end_date`
and a table `types_of_partnership`:
- `id`
- `name` ("marriage", "civil partnership", etc.)
- `tax_benefits`
- `legal_benefits`
2008-11-21 18:25:27 by Sam:
What's the difference between a marriage and a civil union?
2008-11-21 18:45:15 by Ru:
"Those restrictions are not merely a reflection of a deep seated taboo -- like homophobia -- but unlike homophobia, it actually has a biological basis."
I always roll my eyes when I read that. Yes, it is true, inbreeding can be a problem. But the idea that it is a problem in the first generation is left over from eugenics. Besides, we are assuming that marriage is linked to procreation, which, if you accept gay marriage is not true. You are also assume that it is tied to sexual relationships, which also need not be so (see asexuality). Finally, your root belief is that incestuous sexual relationships are wrong, which given birth control and that it takes quite a bit of inbreeding for health issues to arise, I see no bases besides taboo for.
2008-11-21 18:48:41 by Jerry:
Holy crap. That was the best database engineering primer I've ever read.
2008-11-21 19:02:04 by StoneCypher:
Wow, you've discovered first through third form normalization in a series of fourteen steps. Welcome to 1973.
2008-11-21 19:13:16 by Jake:
For the record, example 14 isn't quite as over-engineered as you might think. My wife has a live-in boyfriend, and if they were to marry, I don't think I'd want to be considered married to him.
Many thanks for calling me 31st century though
2008-11-21 19:17:00 by steve:
StoneCipher, I think Mr QNTM was actually demonstrating how others may approach database engineering problems, not his own recent learning experiences.
2008-11-21 19:17:47 by steve:
Jake, she would be legally required to divorce you first though, eliminating that problem.
2008-11-21 19:32:28 by Anii:
That, sir, was amazing.
you put soooo much thought into it. WOW.
good points, dude~~~~
2008-11-21 19:33:52 by Cat:
Steve,
Although your point is valid under the law as it stands, this article is discussing the technical ramifications of marriage structures that, while not currently applicable, may be relevant in the future. Jake was merely pointing out that the possibility for intransitivity in a polymarriage is not such a far-fetched idea.
2008-11-21 19:36:42 by RichardBronosky:
Sam said: What's the difference between a marriage and a civil union?
A marriage is a religious sacrament with a clear definition in virtually all "well established" religions. (Even though it may have slight variance its pretty common.) A civil union should be a legally binding contract. The flaw in the US is that we have given legal consideration to our citizens based on their participation in religious activities. This is what should be banned. When you suggested banning marriage you were almost there, but you were just being cheeky instead of logical. Being united legally is very valid, but doing it via "marriage" is a proven disaster. I blogged about that in 2005: http://www.bronosky.com/?s=marriage
My wife and I would have preferred to have chosen the terms of our union, rather than to take whatever the state decides to consider marriage at whatever point it impacts us. What is now placed in a prenuptial agreement should be the civil union contract of the future.
My wife and I would prefer to have more critical terms in our contract:
1. The person who chooses to leave the marriage leaves with nothing. No custody of the children, or assets.
2. The person who leaves the marriage is never free to remarry. Marriage is forever.
3. Two become one. Either person is empowered to make decisions for the other in absentia. If I'm in a comma, she gets to choose to keep me alive as long as she wishes and can change her mind whenever. (Limited only by logistics.) My parents don't get to raise a stink.
I don't know what I would call the wishy-washy garbage that is happening today, but it's not what we want to be a part of.
2008-11-21 19:40:41 by RichardBronosky:
Interesting hypocrisy, Sam, you closed-minded engineer ;-) http://skitch.com/richardbronosky/hkgh/qntm-new-comment
2008-11-21 19:47:59 by Doomsought:
Sollution: Use arrays, (Using java, which I am familiar with)
(class)humans
double id
string[] givenNames
string[] familyNames
double birthDate
(class)marriages
double id
double[] members
double marriageDate
double divorceDate (-1 if marriage not ended)
male or female is simply solved by giving the id number a negative or positive value.
also one will have problems adding ti the arrays, but this may be useful if one places a arbitrary limit on legal marages (which may be a godd idea anyway.)
2008-11-21 20:00:56 by Sam:
RichardBronosky: You have some pretty draconian notions about civil unions. You're welcome to hold them, but while they'd make the database schema a lot simpler, you can't force them on other people. The only marriages you have control over are your own.
I meant to direct the question of "what's the difference between a civil union and a marriage" to Fabien, in the post above, who was objecting to me using the two terms interchangeably. In fact, as I note at the top of the essay, all the database engineering fun relating to civil unions can equally well be applied to religious unions, which are free to be as complex or simple or draconian or free as they wish. All kinds of unions are susceptible to unhappy or even violent *failure*, but that's not my problem. I'm just a database engineer. I just engineer the database to support it all.
2008-11-21 20:53:30 by Paul:
Fun! Reminds me of the days I spend at a dutch Townhall. I can't auite recall the relational schema, (The dutch support gay marriage since quite a while) but one interresting thing that was implemented, and that you should add (just for fun ;-) is the 'usage-name' for married beings: (In the Netherlands) you can nowadays choose what usage-name you want: partner1's;partner2's;partner1s-dash-partner2's of partner2's-partner3s.
Remember that you can choose AGAIN if the other-half of you partnership passses away...
And hey; add birthplace, town+country, and remember that countries chance over time...
Get complicated soon!!
Cheers, Paul
2008-11-21 20:58:58 by ak:
Kudos! That is by far the most interesting and entertaining article about modelling human relationships in relational DBs.
2008-11-21 21:05:32 by Allan:
Let's play middle of the road and accommodate every option:
Persons:
ID (SSN)
FN
MN
LN
BirthDateTime
DeathDateTime (Null unless dead)
LKAddress (Last Known Address from License, NULL otherwise)
Chromosome (XX, XY, YY, XXY, etc. Tested at birth)
Blood Type
Unions:
ID (PK)
Person1ID (not unique)
Person2ID (unique, person 2 can only give one other person a tax incentive)
Union_DateTime
Union_Type
UnionTypes:
ID
TypeName (Civil Union, Marriage, Child, Elder, Foster)
TaxCodeID
TaxCodes:
ID
TaxCodeName
TaxBreak ($-...XXX.XX to $...XXX.XX)
Programming should cover business requirements for any other option (checking for previous unions given state laws, etc.) This should be covered by an API that states would program toward. If this were a national database then the UnionTypes and TaxCodes tables could also be implemented by the state to offer additional benefits.
2008-11-21 21:11:38 by RowingBear:
While I don't know exactly how this would be handled, what you are looking at is not very much different from a business partnership. Each time a new partner joins or leaves the group, there might be an instantaneous dissolution and creation of the legal entity (the king is dead, long live the king!), but there would be no effective discontinuity. Look at any sizable law or accounting firm to see how they deal with the issue and don't try to reinvent the wheel.
2008-11-21 21:20:19 by cliedwar:
Sam,
Very interesting read. Thank you.
2008-11-21 21:23:29 by Ryland:
Wouldn't it be simpler to take your Schema 13 and fold the fields in `marriage_partners` into `humans`?
`humans`
- `id`
- `forename`
- `surname`
- `birthdate`
- `marriage_id` (foreign key references column `marriages`.`id`)
- `marriage_date`
- `divorce_date` (NULL if still in marriage)
`marriages`
- `id`
- `marriage_initial_start_date`
- `marriage_final_end_date`
This schema ensures that each person is only in one marriage blob, and searches for marriage groups don't even really require a JOIN unless you need the date the marriage blob started. You'd still need to police it for single people being in marriage blobs alone, though.
2008-11-21 21:26:30 by kentbrew:
Seems like you could do it without extra tables.
In a monogamous system, two members of the People table point field IsMarriedTo at each other and be done with it done. Polygamy could be handled with a circular reference; person A points IsMarriedTo at person B, who points to person C, who points back to person A.
2008-11-21 21:53:43 by Frick:
You write "Sexist!" as if that were an argument. Yet you yourself are only one gender, and that's as sexist as anything else.
(N.B. There are 2 square roots of minus one)
2008-11-21 22:04:19 by bikko:
I am very impressed at how thoughtful and intelligent this article is! Great job. I came here from reddit but I'll be checking out your other work, too.
One thought: When you used the word trigamy, did you mean bigamy? Trigamy means having three spouses...
2008-11-21 22:32:48 by Ryan:
Fun read, I do have one issue though.
If you are keeping track of divorce dates then it is incorrect to state that one human may be in no more than one marriage. Let's say that Alice and Bob are the partners in marriage 1. Then Alice and Bob get divorced, they are still partners in marriage 1 even though it has ended. Then Alice decides to marry Carol, Alice is in two marriages and this should be acceptable because one marriage is no longer active.
Also, if you're gonna be 31st century about things, you should probably consider the possibility of non-human partners.
2008-11-21 23:00:27 by Michael:
I'd have to argue that marriage is not transitive. But aside from that, nicely done ;)
2008-11-21 23:08:34 by CJ:
I considered quickly on some paper the simplest non-transitive case which is obviously not symmetric - a triangle of spouses (A, B, C) of whom one (B) is married to a fourth person (D).
If you tot up the amount each person expects to inherit from the others, it comes out surprisingly fair. In a binary marriage, each person expects to inherit one half of one estate (the estate of the other person, times 0.5 which is the probability that the other one dies first). I've assumed that each estate is split equally amongst the remaining living spouses of the dead person.
So, there are 24 different orders in which these four can die. We find that D, the loner, expects to inherit 0.5 estates, which is slightly surprising - I'd expected em to profit from being connected to the network. A and C both expect to inherit 0.71875 estates, and B expects to inherit 1.333 estates. (These numbers aren't incorrect, but they oughtn't sum to one since sometime the chain of death terminates)
So the only result in this situation is that A and C expect to gain around 5% more of an estate than they otherwise would have, and B and D are as they always were. Financial win!
(Frick, the comments system accepts both roots, and I don't follow the rest of your comment. Indeed, I've seen little evidence to suggest that Sam is only one gender.)
2008-11-21 23:24:20 by GaryJordan:
First, I have a dog in this hunt. I've written a story (bublished on the web and still in progress) named "Going Down" which is Alternate History Science Fiction. The departure from our timeline takes place in 1862 when the US Congress voted in anti-polygamy laws. In the story, the Confederacy promptly votes pro-polygamy laws to successfully entice Utah and any other Mormon-majority territory to join the Confederacy. Thereafter the law of unintended consequences prevails and by the twenty-first century the Confederacy is a polygamous culture throughout its thirty or so states.
Your database needs to be able to accommodate multiple marriages to account for 1) divorce and remarriage or contimued marriage with fewer partners, 2) marriage of additional partners, 3 widows and widowers and remarriage or continuance of a marriage with fewer partners, 4) variable numbers of marriage partners.
The database is no good to me unless it can deal with a man marrying the two female partners of a marriage *modified* by divorce (they discarded a husband).
2008-11-21 23:25:09 by gopi:
I know one guy whose wife kept her maiden name. They had trouble with one bank, who were absolutely insistent that a married couple had to have the same last name.
He was wondering if the bank had actually set up a database schema with single last names for joint married accounts.
He did eventually convince the bank to open up an account for him, so it was apparently just the bank staff person not being able to comprehend such a strange scenario.
2008-11-21 23:26:06 by Art:
I truly can't decide whether this is meant to be satire or not. I will comment as if it were meant to be serious.
I have NEVER come across a database system with separate tables for Male and Female. It has ALWAYS been a table (Patient, Employee, Subscriber, whatever) for humans of any gender. In almost all cases it has had a "gender" field with values 'M', 'F', or Null, although I have read of systems with more options. I've never worked with them, and don't know how they deal with changes in gender.
In any event, marriages are usually represented by a structure like
marriage_id
husband_id
wife_id
start_date -- date of marriage
end_date -- if divorced
This is your schema 7, except that I do not enforce that a husband be male. As you note, this would be a check constraint. Constraints could enforce other rules as well: no polygamy, no self-marriage, no marriage to dead people. Personally I prefer triggers to complex constraints, but YMMV.
Dealing with a sex change of either participant has just not been a problem. I'm curious to know the context in which you need to deal with this.
In any case, generalizing from husband and wife to Spouse1 and Spouse2 is trivial. Yes, there's a lot of search-and-replace, but still trivial.
Generalizing to polygamy is EXACTLY like allowing more than two e-mail addresses (or phone numbers, or addresses). You create a new table called Marriage_Members with fields (marriage_id, spouse_id), EXACTLY like a table of Email_Addresses (person_id, email_address).
Hey, while we're at it, why does a person have a single birth date? Why not:
* the date on which it was born
* the date on which it prefers to celebrate the birthday (useful if the first date is Feb. 29 or Dec. 25)
* the person's Saint's date (null if not Catholic)
* the date one which the person was Born Again (null if ... you get the idea)
You want to be silly? Similarly, a person can have several First Names (Jean-Marie, Jean, JM, Skippy), several Last Names (birth name, married name, legally changed name, stage name, nom de plume, other aliases ...) and more than one Social Security Number (several illegal aliens of my acquaintance aren't Undocumented, they are Multiply Documented).
Essentially you are asking, "How do I structure the data if it is unstructured?" And the answer is, "you can't."
By the way the Turing question about the square root of -1 has three sets of answers that I'm aware of:
i, -i if you're a mathematician
j, -j if you're an electrical engineer
i, j, k -i, -j, -k if you like quaternions.
2008-11-21 23:28:50 by homo:
2 + 2 = 5
2008-11-21 23:43:26 by Artanis:
>>Yet you yourself are only one gender, and that's as sexist as anything else.
Hardly. I assume Sam is male by the chosen nick, (though this could be the personal website of a one Samantha Hughes and she never clued us in,) and therefore possesses, as most males do, both the X and Y chromosomes.
On the other hand, most females possess only X chromosomes, making them far more discriminatory then males.
(I know, I know, chromosomes != gender)
2008-11-22 00:01:24 by ardil:
Consider true polygamy -- one straight man married to two straight women: I would differentiate this from the case where the two women happen to be bisexual and are married to each other as well! Also, from the case where Sheila, a bisexual woman, is married to Alice, a lesbian female partner and Brian, a straight male partner... I believe that a combination of n-ary unions with no uniqueness constraint on the no. of marriages that one can be part of would be needed for this purpose.
2008-11-22 00:35:19 by Abraham:
Marriage [should have] never met gubbment or therefor civil unions.
It is defined as a woman and a man under God (monotheistically).
Hard to procreate with hotdogs and no buns...
2008-11-22 01:22:27 by IanOsmond:
Obviously, you're still dealing with assumptions of what "marriage" is, even at the last stage. This is a fundamental problem in ALL database design -- really, all design of ANY complex system -- trying to anticipate in what ways the system may need to grow in the future.
What you've come up with, at the end, is a design that can accomidate a fair chunk of the ways that you, personally, can perceive of an institution called "marriage" being implemented. Which, when you get down to it, is probably the best you can do.
But, while, for instance, you are accepting the possibility of marriage being non-transitive, what if it were non-symmetric? I have absolutely no idea how that would work, but could it be that A is married to B, but B is not married to A?
On the one hand, that appears entirely ridiculous. On the other, the converse also could be problematic. If A and B are married, is the relationship of "marriage" that A has to B the same relationship as B has to A?
One of the things that you refer to above is that, in many people's conception of "marriage", they are NOT. The role that a "wife" has toward a "husband" is NOT the same as the role that a "husband" has to a "wife".
So, really, a "marriage" between two people is a set of TWO directional relationships, one defining the relationship from A to B, the other from B to A.
My design instinct would be to create a general class of "relationship" of which "marriage" would be simply one example. "Relationships" would not be necessarily symmetric -- if I have a "Teacher-Of" relationship with someone, that person has a "Student-Of" relationship with me. They may ALSO have a SEPARATE "Teacher-Of" relationship to me, if, say, I teach them cooking and they teach me judo.
Now, this would allow me to have a "Married-To" relationship with my wife, who could have a "Married-To" relationship to me. Or I could have a "Husband-Of" relationship with my wife, who could have a "Wife-Of" relationship to me. Or, since I take care of the house and do the cooking, and she makes most of the money, I could have a "Wife-Of" relationship to her and she could have a "Husband-Of" relationship to me.
I think that that structure would be more flexible.
2008-11-22 03:12:08 by Ganymede:
Having worked for behavioral health software firms and the San Diego and San Francisco County health departments, I can say from actual experience that these are not completely theoretical or moot issues, particularly concerning non-binary gender and gender-identification variables. Familial relationship variables however ran much more to head-of-household, household-income, number of children, significant-other-id, and living-situation-codes. The privacy of these data are subject to the interest of involved third-parties (providers, payors, responsible parties, etc). HIPPA stipulates jail-time for allowing instances (paper or electronic) of such protected data to be exposed to non-involved parties (thank you, Bill Clinton). While tracking of individual sexual acts &/or liaisons has been done for political and financial purposes (blackmail), legitimization of such has not been established, even where it could conceivably serve public health (e.g. epidemiological analysis).
Personally, the complexity of relationships & sex acts of myself and my myriad partners greatly exceeds that which has been described here.
Also, the roots of my ones were not square when they were plus, nor now that they are minus.
2008-11-22 03:50:26 by LachlanMcDonald:
In the interests of data integrity and reducing redundancy, couldn't the following:
- `id`
- `partner_1_id` (foreign key references column `humans`.`id`)
- `partner_2_id` (foreign key references column `humans`.`id`)
Simply be reduced to:
- `partner_1_id` (foreign key references column `humans`.`id`)
- `partner_2_id` (foreign key references column `humans`.`id`)
If both partner_1_id and partner_2_id are constrained as keys together, then it will remove the redundancy of ID. To prevent issues occurring with divorces, you could use the marriage date as well as part of the constraint, as partner_1_id, partner_2_id and the marriage date is enough to uniquely identify each marriage.
2008-11-22 04:02:30 by Deekitten:
Will you email me your picture? I'd like to masturbate to it later. That's how happy this post has made me.
No, seriously.
checkerknucklez@yahoo.com
2008-11-22 04:05:48 by Sapiophile:
Why have a "marriage" relationship at all?
Why not have a "relationship" entity that joins any number of people with any other number of people? This is handy for variations of marriage, and also for representing familial relationships. The relationship entry would need to refer to a table of relationship types, and as noted above, those types need not be symmetric. "Father" is clearly not a symmetric relationship. Business logic could be added to ensure symmetry if required (for instance, if we insist that A married to B implies B married to A) but it's more flexible not to encode that at the DB level, eh?
2008-11-22 08:57:22 by james:
how is marriage blob formed
2008-11-22 09:01:57 by jholman:
As many people have pointed out, most polyamorous relationships are not transitive, and so too it would be nice if the schema supported that.
But this article made me think of a Relational Model question I've been harbouring. Is there a Right Relational Way to store symmetric relationships of fixed arity, like a 2-person symmetric non-sex-specific marriage? If we use the relation with two columns, partner1 and partner2, we get some ugliness, like querying if Bob is married requiring an OR, and things like that. If we use the 'blob' approach, it's tough to enforce the fixed arity... it's too messy preventing a third entity from joining the relation.
It seems like the first approach is closer to the mathematical relation, perhaps by adding a symmetry-enforcing trigger which makes sure that R(X,Y) iff R(Y,X), by adding/updating/deleting records as necessary. Too bad about the whole using-up-twice-as-much-space business.
Any thoughts on how to do this accurately and elegantly?
2008-11-22 14:35:34 by SteveB:
I'm not a database expert, but I frequently teach programming languages, and am currently teaching a bunch of college sophomores Prolog, in which many of the classic introductory exercises involve family relationships. So in my example rulebase I had "primitive" predicates (arbitrary facts) like
1-place predicate "male"
1-place predicate "female" (no enforcement that one and only one of them applies to any given individual)
2-place predicate "parent"
2-place predicate "married"
etc.
One can then write rules like
mother(Mom,Child) :- parent(Mom,Child), female(Mom).
father(Dad,Child) :- parent(Dad,Child), male(Dad).
sibling(X,Y) :- mother(Mom,X), mother(Mom,Y), father(Dad,X), father(Dad,Y), X \= Y.
% which obviously falls apart once you've got same-sex couples having children
% The X\=Y at the end is to make it non-reflexive.
% You can fix the same-sex problem by saying instead
sibling(X,Y) :- parent(P1,X), parent(P2,X), P1 \= P2, parent(P1,Y), parent(P2,Y), X \= Y.
% thus defining "sibling" as "sharing at least two distinct parents"
How do you write "aunt"? Well, there are two ways to be my aunt: you can be a sister of my parent, or you can be married to a sibling of my parent.
aunt(Aunt,NN) :- sister(Aunt,P), parent(P,NN).
aunt(Aunt,NN) :- married(Aunt,Uncle), sibling(Uncle,P), parent(P,NN).
But what if I've got the primitive statement married(bob,jane) in the rulebase? This will fail, because "married" is asymmetric. Simply adding a rule to make it symmetric
married(X,Y) :- married(Y,X).
has the unfortunate practical consequence of infinite recursion. So in the example I gave my students last week, I insisted that the first argument to "married" must be male, and the second must be female. Obviously, this imposes restrictions that might not be applicable in all cases.
Now I'll have to go try to translate each of your database schemas into Prolog for my class... maybe that would be a good homework assignment....
2008-11-22 15:16:53 by LarryAnderson:
On the database I am working on I have the adults and children connected by a relationship table to the family table.
Family tables:
adult family-adult relationship family family-child relationship child
The adult relationship also defines whether the adult is a head of household, spouse, other family member or other (not counted in family size) This allows for either multiple spouses or HH and spouse regardless of gender, as well as adults and children able to be be linked to two families simultaneously (in cases of shared custody for children.) With the child, relationship can be guardianship, natural-born, adopted, foster, etc. you can even relate together foster kids with it.
I'm just now setting up a project page and will be discussing it once everything is set up, www.wandahome.org
2008-11-22 16:13:19 by Mick:
Sam, I keep thinking I can't love this site any more, and then you do stuff like this. Thank you for your time and effort, and good work.
Oh, and just wandering: what are the gay marraige-civil union laws over in the UK?
2008-11-22 16:43:22 by Jymbob:
Two things: I disagree with your argument that women being the only ones allowed to marry men and vice versa is sexist, unless you start to impose gender models. Both groups are allowed to marry a member of the opposite sex. Suggesting some sort of two-way difference is dangerous at best.
Secondly, you've also restricted all members of your database to one forename and one surname. Surely some kind of one-to-many relational with a couple of keys for 'formal' and 'informal' (or some other indication of name_called and name_written_on_letters) would be better?
2008-11-22 16:46:56 by Fabien:
On the question:
"what's the difference between a civil union and a marriage"
In several countries, using the word "marriage" for homosexual couples doesn't seem acceptable. That's why different names are created for gay unions. Some details can also change (like the procedure to end the union), depending on countries.
So my point was, what's called "marriage" is only one possibility for a legal union between two people.
I took the term "civil union" from the British law, as an example of such a union which is pretty much a marriage but without the word "marriage".
2008-11-22 17:39:58 by Tom:
Plus, the name bit should probably not be forename, surname rather something like:
...
given_name text,
family_name text,
family_name_comes_last boolean default true,
...
Though that might also be baggageful.
Perhaps just
...
name text
...
2008-11-22 19:21:15 by Sam:
Storing human names in databases is an entirely different (and, I would say, not nearly so trivial) problem. However, I would say that my `forename` and `surname` columns would, of course, permit spaces to be entered as characters, for those with multiple surnames.
2008-11-22 20:43:55 by adamo:
"why I haven't just skipped schemas (schemae?)"
schemata
2008-11-22 21:00:49 by Andrew:
This post is everything that I believe in.
2008-11-22 23:16:00 by quantumkitty:
adamo: Thank you. I was going to say that. (How can anyone not know that "schema" is Greek third declension neuter?!?) :)
2008-11-23 02:21:09 by DavidGaramond:
interesting, albeit a bit long-winded. A few database schema examples could be omitted, for example: no real-world databases that I know of store males and females in two separate relations.
2008-11-23 04:54:01 by bonniea:
A fun and excellent read, logically following some obvious paths given the current discourse on marriage. And hey, databases =)
2008-11-23 20:42:12 by PietroSperoni:
Thanks for the article, thanks for the fun.
It should be pointed out that the male-female does not depend on the shape of the genitalia, and not even by who gets pregnant, but by who gets to put the ribosome dna. Which, as you know, only come from the female. So there are some animals where is the male that gets pregnant.
I suppose things will get easier when we simply have a long field for the complete DNA to represent the sex: here check it out. Unfortunately by that time we will probably be able to change our DNA, forcing you to have a list of DNA's. Of course assuming that only one DNA will be in use at any given point.
I am SO looking forward to read the follow up on how to build a database of sexual partners.
2008-11-23 20:57:48 by PietroSperoni:
Correction:
the Cytoplasm DNA comes from the female. Not the Ribosome.
From the Red Queen Effect, pg 104,"Gender, then, was invented as a means of resolving the conflict between the cytoplasmic genes of the two parents. Rather than let such conflict destroy the offspring, a sensible agreement was reached: All the cytoplasmic genes would come from the mother, none from the father. Since this made the father' s gametes smaller, they could specialize in being more numerous and mobile the better to find eggs. Gender is a bureaucratic solution to an antisocial habit."
2008-11-23 20:58:02 by charles:
hah y2gay, excellent.
2008-11-23 22:40:13 by Pancake:
An prior comment wrote: "Before I get into this, let me state that I am pro gay marriage" Then... "Specifically, you can't marry people who are genetically related to you, because, even without our understanding of recessive genes work, that shit is just gross."
So after declaring support for gay marriage, the person then forbids intra-family marriage on the basis of either genetic destructiveness or plain "grossness". Please explain how reproductive genetics factors into a homosexual marriage, or why "grossness" is a reasonable limiter in intra-family marriage, but bigoted when applied to homosexual relationships?
If you don't understand my point, let me pose this scenario: I'm a male and I love my brother. We decide we want to marry. Please explain your reasoning why this shouldn't be allowed.
The fundamental miss of the OP and most people opposed to the (non familial) man-woman definition of marriage is that they believe it is a bigot or hate argument. This might be true for a subset the man-woman supporters, but it is not the sole reason and a red herring issue, just as the OP makes light of it as a bureaucratic issue. It is an issue of definition, and the persistent entropy of vocabulary and populations (which you might think is a good thing--but if everything is the same, then who is unique?) Let me put this terms the readers here will understand:
A = Man
B = Woman
A B
(While the law is blind to characteristics like race & religion, men & women are not equivalent in the law and protections afforded them differ. Don't believe me? Ask a lawyer as I have. Or, for a more practical example go to your local mall and hang out in the opposite gender's restroom and ponder why segregated water fountains are gone but segregated bathrooms still exist.)
Then:
A + B = C = marriage
Therefore:
A + A C
B + B C
A + B + B C (unless B = 0)
B + B + B C (unless B = 0.5A)
A + X C where X B
Let me also make it clear I am not opposed to civil partnerships that afford homosexual couples the rights of inheritance and other issues that plague these couples in difficult times (such as being unable to enter family-only hospital areas). Many friends of mine are directly affected by these limitations and it is wrong. But I am adamant about preserving the distinction of marriage and oppose the continued dilution of our culture and language. As a species and culture, the man-woman union is fundamental to our propagation and evolution (no matter how they try, a homosexual couple cannot achieve natural procreation) and a special designation should be reserved for this union. (Under my own arguments, a polygamous relationshop would be better suited to gain merit for definition as a marriage vs. a homosexual couple, but I am firm in the man-woman = marriage definition.)
2008-11-23 22:54:29 by Maggie:
Magnificent - not only supremely logical, but funny and thoughtful as well. THIS is why computer engineers and others of their ilk are absolutely amazing people.
http://stealth-homestead.blogspot.com/
2008-11-24 01:46:21 by Kay:
@ Pancake:
We live in a world where the majority of people are suffering as a direct consequence of overpopulation (reckless heterosexual union, if you will). Now as we as a species rapidly approach 7 billion worldwide, I have to ask you... why are we specially privileging a certain type of union simply because it may, in some cases, lead to children which are accidental and/or unwanted?
Any stable, loving coupling of adults (especially when backed by legal protections) is a potentially fertile environment into which children might be introduced. Whether through intentional biological processes or, importantly, by adoption. There are too many unwanted kids in the world. Why are we actively encouraging people to churn out more and discriminating against a group that routinely does its part to help alleviate the problem by taking in the products of irresponsible heterosexuality?
2008-11-24 01:47:42 by Kay:
@ Pancake:
We live in a world where the majority of people are suffering as a direct consequence of overpopulation (reckless heterosexual union, if you will). Now as we as a species rapidly approach 7 billion worldwide, I have to ask you... why are we specially privileging a certain type of union simply because it may, in some cases, lead to more children. Some of whom will be accidental and/or unwanted?
Any stable, loving coupling of adults (especially when backed by legal protections) is a potentially fertile environment into which children might be introduced. Whether through intentional biological processes or, importantly, by adoption. There are too many unwanted kids in the world. Why are we actively encouraging people to churn out more and discriminating against a group that routinely does its part to help alleviate the problem by taking in the products of irresponsible heterosexuality?
2008-11-24 02:28:12 by ArmyOfAardvarks:
I am planning on marrying the marriage database.
It turns me on and I'm quite convinced that we would be happy together.
My only concern is that it might spend too much time "referencing itself".
2008-11-24 04:48:18 by NovelDevice:
I less than three you.
2008-11-24 14:07:19 by Noel:
This is really fantastic. Thanks. I'm passin' it along.
2008-11-24 14:46:11 by Kitwench:
Am I the only one who'd really prefer that the information not be tracked at ALL ?
2008-11-24 16:14:19 by Jeff:
First off, your table 'humans' should be 'persons', and should have the capacity to handle any non-artificial, natural, legal person. (i.e., IBM can't marry Microsoft) It is conceivable that someday we encounter an intelligent race which we can consider to be persons, whether extraterrestrial, AI, or, hell, mutant dog-beast. You get my point.
As a corollary, this persons table need not have a gender column. Add an additional table for gender and/or one for sex.
Finally, the big one, follows "Twelve: Polygamy"
If I marry Jane and then Mary, but Mary had already married John, I haven't consented to John's having any say in my health care or visitation rights. In fact, Jane and Mary haven't married each other, they've each married me. So, we have three marriage entries. This is what happens, right now, with bigamy (only its illegal). At the same time, all three of me, Jane, and Mary could choose to enter into a single marriage, too. I don't see a need for a unique key.
The other problem with polygamy is who gets priority over who. Because, as much as I might hope otherwise, when I'm sitting there nearly dead on a respirator and food tube, Jane might say I wouldn't want to live like that and Mary might say oh, but I would want everything done to bring me back. We have an impasse. Both people have rights to say what happens to me. In this case, and in the absence of specific instruction (living will) as to who decides and/or what happens, who takes priority? In the former case, above, the marriage with the oldest date might be presumed to have seniority, and therefore the edge given to Jane. In the latter case, however, there's no help for it but to either have some sort of priority built into the schema, or let the law decide.
(Incidentally, I'm poly, in a rather complex relationship network, and prefer the view that my relationship with each individual is essentially independent, though I know some poly people who treat things as a blob.)
2008-11-24 16:38:36 by Charlene:
So in your world nobody is ever widowed?
2008-11-24 23:39:40 by Daan:
Absolutely amazing Sam. That was a great read, thanks for that!
2008-11-25 09:29:43 by NicholasWhyte:
The Belgians have an answer:
http://nhw.livejournal.com/430075.html
2008-11-25 17:32:48 by maryhs:
Just a thought...the multiple-marriage database isn't theoretical in countries whose marriage laws are based on the Qu'ran instead of the Bible. (Or those churches here in North America that belong to the Fundamentalist sects of the Church of Latter Day Saints here in North America.)
The Qu'ran allows a man 4 wives --as long as all 4 can be treated equally--not sure about the Fundamentalist sects.
So a database that can handle this situation probably exists somewhere.
2008-11-25 20:28:52 by YarKramer:
You know what I find awesome? The sheer *volume* of material you can get simply by combining multiple issues which are only tangentially related: in this case "gay marriage" and "how to store marriages in a database." (And this by the creator of "How to Destroy the Earth.")
2008-11-25 22:51:04 by Richard:
As Charlene wrote, you don't want 'divorce date'; you want 'date marriage ended', with a code for how it ended.
You also want the date the marriage began. Current registration forms ask, as well, for: city, town, etc., where performed; religious or civil ceremony; name of person performing the marriage (officiant); probably some others. It's all up for review, but I think date is crucial, place highly desirable, and name of somebody other than the parties important -- the last is, in effect, a witness to its having been done, and a person taking responsibility for registering it.
You put your finger on the critical complication of more-than-two-people marriages: If there are only two people, then either both is in the marriage or neither is; the date of each leaving the marriage is the same, and the same as the date the marriage ceases to exist. It sounds like a marriage will have to become like an association or a committee: it always has members, but it is also an entity in its own right. Then, forming or dissolving a marriage is a separate act from people joining or leaving the marriage, though you likely want a rule that a marriage has to have members ('partners') at all times. Probably similar data structures exist now, for associations.
Of course, besides 'partner' ('spouse', 'husband', 'wife') there's now another relationship between humans and marriages: 'minor child'. You'd need to retain that, or decide why not to. It'd change the 'marriage_partners' table to something like 'marriage_members', with a field indicating the human's role in the marriage.
What demographics you want about 'humans' - sex, gender, whatever - is a separate question. However, for use with 'marriages', fields for 'humans' should include birth dates (supposing, as is likely, that there's a minimum age to be a 'partner' and a maximum age to be a 'minor child'), and must include death dates (to identify marriages ended or diminished by widowhood).
2008-11-25 22:53:53 by Richard:
Postscript, on names for 'humans': Many names do not conform to the 'forename', 'surname' template. It's especially bad if you include 'middle initial', say I (W. Richard Ristow).
But solving that is orthogonal to the question how to represent marriages in the database.
2008-11-26 08:47:48 by mhlekazi:
One could actually quite easily modify the old Corel InfoCentral PIM to handle these situations - I used it in anthropological fieldwork in a setting with polygynous marriage where I had to keep track of the ranking of wives, amount of bridewealth paid at marriage, etc. Every connection, including marriage, was fully customizable so one could add rank, marriage/divorce dates etc. So yes, such a database does exist somewhere, on a Win 98 install running in Q on my mac, but perhaps nowhere else.
2008-11-28 05:32:40 by Amy:
Wow, I really enjoyed following your logic from one schema to another. I think it was an entirely logical progression, and I rather enjoying reading your schema as well.
Thank you.
2008-11-28 14:38:46 by Steven:
Consequences of gay marriage: http://eatliver.com/i.php?n=3722
2008-11-28 15:44:57 by mildlydiverting:
A little realworld asside. A friend of mine works in IT for the NHS. He came across an interesting problem recently whilst trying to create a system that merged information from both a police database and an NHS database.
The police db had three genders: Male, Female, Other
The NHS db had five.
It caused the team quite a lot of development pain.
2008-11-28 17:33:34 by Andrew:
This brought back bad memories. My first full-time job was maintaining a database app for an estate agent. The schema was horribly broken in a number of ways: from memory, here's a representative fragment:
customer: # a human
title
firstname
lastname
partner_id # another customer
is_customer1 # is this the primary or secondary customer?
application: # someone buying/selling a property
customer_id # must reference the primary customer of a couple
property_id # which house are they buying/selling
is_selling
There are any number of ways this could have been better - but very few schemas would have been able to handle the situation that arose when one couple was getting divorced and they wanted to sell their house and each move into separate houses with new partners.
2008-11-28 17:39:55 by PaulSkinner:
Optimise for common case.
This is certainly not optimised for the common case.
2008-11-28 21:33:27 by Logbuffer:
"The article is Gay marriage: the database engineering perspective. It's perceptive, funny, and yes--there's lots of comment."
Log Buffer #125: http://www.pythian.com/blogs/1408/log-buffer-125-a-carnival-of-the-vanities-for-dbas
2008-11-28 22:02:24 by Sam:
I've not noticed any hits coming in from pythian.com. Maybe they've been drowned out by the deluge from b3ta.com? ;)
2008-11-29 01:23:59 by Steve:
I haven't read this entire thread...I don't have a few hours to spare... :o) So maybe someone brought this up before... but it's been my experience that with any gay couple, one usually assumes the role of the male and the other assumes the role of the female. It doesn't matter whether it's two gay males or two lesbians, they each assume an opposite gender role. I'm sure there are exceptions but that has been the case with all of the gays that I've known in the past, which admittedly, is not that many.
So, you could still have the categories of husband and wife, you would just need to clarify it with a male or female sub-category. Or vice versa. Or not. Maybe you don't even need to record the actual gender, just the assumed role gender. I would think that would make it a lot easier.
2008-11-29 09:22:42 by CJ:
Steve, that's actually a pretty... well, I'll go with 'inaccurate' statement. For one thing, the role you play in a relationship is independent of the gender role you present to society - 'gender' has a pretty specific meaning and it's not the one you just used it as.
Second, it would actually complicate matters. If I'm the husband to Bob but the wife to Darcy (which is possible in some of the above examples), that needs another field to be added to the table linking us together; plus more application checks that one husband has one wife (or the exercise is pointless). May a woman be a husband to a man? Why not? Wouldn't it usually be assumed that the man was the husband? For most situations, the actual dynamic inside the relationship is none of whoever's storing the data's business (so, the government has no right to know if I'm hugging Jamie or ey's hugging me), and I would object to them trying to store it.
Finally, I don't believe that that's generally true - certainly not in the way you stated it. You get a much more accurate picture if you think of people as (emotionally) "Strong" or "Weak" (before anyone jumps down my throat, "strong" people need "weak" people just as much as the other way around, and I'm definitely not "strong"). I had two female friends in a relationship, for example - the one who conformed much more to the female gender stereotype of society was the "strong" one.
2008-11-29 09:57:58 by Mark:
As a database designer who is looking into how family trees are modelled, this was fascinating.
Thanks.
2008-11-29 12:58:32 by Sam:
CJ: as far as I am concerned, human emotional relationships are so astoundingly complex as to be impossible to model in a database. Traditional "husband" and "wife" roles do exist but they are categorically just the starting points for some profoundly unconventional combinations. Not only is this too complex for us to store it the database, it is irrelevant. How you manage your relationships and marriages is your business. This is not something that the law has any interest in recording, let alone interfering with or defining legally.
2008-11-29 15:23:32 by CJ:
Indeed - that was my point. My post was in response to 'Steve' above.
2008-11-29 20:38:39 by Jerry:
Could well be adopted as a (junior level) database developer interview question.
2008-11-30 02:05:26 by JoAnne:
Here's a possibility. You would have three tables: persons, marriages, and links between the two, which I call memberships:
person_id
person_name
person_birthdate
person_deathdate
etc.
marriage_id
marriage_location (for legal reasons)
membership_id
marriage_id
person_id
marriage_date
divorce_date (null if never divorced)
Person records for A and B are created when the person is born.
When A and B marry, a marriage record is created, and two members are created.
If C joins their union, another member record is created, with the same marriage_id as the first two, but with a different marriage_date.
If any of A, B or C leave, their member record is altered to add the divorce_date. When the last two people split, they both get the same divorce date.
To find out who's ever been married to a particular person, you would first select all member records with that person's person_id in them, then select all member records linked to those marriages, and look up all the persons whose person_id records appear in that list. In SQL terms:
WHERE person_id = 'person1'
Consequences which you could choose to allow or disallow through legislation and business rules:
People could have the same marriage going for as long as you want. A could marry B, then A dies, B marries C in the same marriage, B dies, C marries D in the same marriage and so on.
People could be in more than one marriage. A could marry B, and also marry C, but they wouldn't mean that B is married to C unless they all wanted C to join the existing marriage of A and B.
You'd want to constrain for multiple people having multiple marriages to each other. If there is an existing A/B marriage, another won't be created unless the divorce date is non-null on the memberships for A and B for the existing marriage. That way, A and B and C and D could marry, then B and D leave. You'd put the divorce date on the membership records for B and D that have the marriage_id for that marriage.
A and C still have the original marriage. B and D could marry each other again, in a separate marriage. They'd get a new marriage_id and there'd be new membership records for that marriage, one for each of them.
Or B or D could rejoin the original marriage. There'd be a new membership record with the same person_id and same marriage_id but a different marriage date.
You could even set a special constraint that, if the same set of people wants to recreate the same marriage as before, they would be allowed to have the same marriage. Or you could rule that out. Either way, by walking through all the membership records related to the marriage, sorted by date, you could see who was married to whom, and when. You could use a duplicate table to show the whole story of the marriage including weddings and divorces and deaths and births.
Hey, we didn't even put in the birth information for each child. Same basic thing, though; you could have any number of parents, and those relationships could be broken at will or under legislation. So when you're born, along with your person record, there would be a set of parent records created:
parent_id
person_id
parent_date
release_date
Release would be equivalent to giving the child up for adoption or relinquishing one's parental rights, or having them severed through a court of law.
2008-11-30 02:09:17 by JoAnne:
Oops 1: forgot to build the SQL statement, and now I have to run and check a job, having spent enough time on this already, so I probably won't get around to it. We all remember "left as an exercise to the reader," I hope?
Oops 2: not a duplicate table, a duplicate table reference.
2008-11-30 22:22:47 by Gav:
That really is an absolutely incredible read, a fascinating (and persuasive) perspective, and exquisitely written.
While I'd have been in favour of gay marriage/partnership/whatever previously, the case makes a compelling point for legitimate polyamory.
2008-12-01 09:36:35 by viiviiviivii:
Now I have a headache
2008-12-01 15:07:28 by fsilber:
You're all a bunch of uptight, closed-minded, sexually repressed speciesists. Meanwhile, workers who express conjugal love with their canines are denied veterinary benefits by bestiaphobic employers.
2008-12-01 18:12:08 by RioRico:
Lots of culture-specific stuff here. The human sphere extends beyond the Euro-Gringo world. Rather than just try to invent a schema, why not see how DBs are built in various disciplines (anthropology, law, census, biology-taxonomy, genealogy) around the planet, to handle the complexity of names and relationships? Plagiarize, plagiarize, let no-one else's work evade your eyes... But I digress.
Some previous comments raise good points, worth exploring, like:
* Why track this info at all? (For legal benefits, supposedly, but how closely do we want to scrutinized by bureaucracies? Can we opt out? How?)
* How can data integrity and security be safeguarded? (USA voters are disenfranchised due to clerical errors; what happens when relationship data is miskeyed, falsified, leaked?)
* Assuming the above concerns are satisfied, how can (or should?) ever-changing relationships be tracked? (Do I really want to be associated with my temporary step-sibling's current crackhead SO?)
In my long-ago experience as a DBE/DBA for a major insurer, schemas are ALWAYS expanded to encompass more data. So we can build a DB of individuals, their relationships to other individuals (and duration, like tracking when commercial insurance policies and riders are in-force), and legal aspects of those relationships - marriage, parentship, whatever. But unless perfectly controlled, it WILL expand to include pointers to DBs of criminal, legal, medical, school, credit, commo, purchasing, political etc histories. It's the corollary to Parkinson's Law: Data, like work, expands to fill all time and space available.
2008-12-01 21:08:43 by thetreeorthebear:
My proposed system would be
`people`
- `name`
- `id`
The end.
I don't think there'd be any problems with that.
2008-12-02 05:48:51 by Ben:
You did not just add an Italian translation of this.
You did.
That's awesome.
2008-12-02 06:53:02 by Oblomov:
I'd say the problem is that people think of data in two dimentions (rows and columns) when there really is a lot more of them (polymorphism, inheritance, composition, type aggregation, et c). I.e. the problem is that people for some reason stick to technology from the 60s (relational databases).
Females and males are both subclasses of human (unless you want gender as an attribute in human), the semantics of a marrige is the relation of two humans.
2008-12-02 18:15:32 by LeonardJ:
As a gay man, and given the subject matter, I am shocked and awed by the sheer STRAIGHTNESS of attitudes expressed throughout this page (not that I can swear to have read EVERY line of your thesis, Sam). But some of the comments have stepped out of the Ark. Steve (2008-11-29), you're talking TOSH when you say "it's been my experience that with any gay couple, one usually assumes the role of the male and the other assumes the role of the female". Yes SOME gay men choose to identify themselves as "Top" or "Bottom" (ghastly functional terms!), but the vast majority choose the third option in this nomenclature: "Versatile". This is the point at which straights always grow envious of what gay men "get up to" in bed.
Anyways, versatility by the majority is certainly what was confirmed in the UK's definitive surveys into sexual behaviour, Natsals 1 & 2, conducted in the 1990s with representative samples totalling 30,000 UK adults (with Wellcome money provided after Mrs Thatcher withdrew funding for such an "inappropriate" survey even as the Aids crisis mounted!)
2008-12-02 19:27:53 by Dave:
This ought to be published in school computer science texts.
2008-12-02 20:39:58 by Moose:
I must admit, I fell foul of politically correct bureaucracy in another direction.. when I first moved into this house, my brother was my landlord and was also living here. I applied for the usual benefits to tide me over, but was refused. The letter that informed me of this stated that I was refused benefits as I was living with a man as his partner - they did not seem to realise that two men who share the last name could be related instead of partners. I laughed, but it made me bloody angry at the time.
2008-12-03 15:57:21 by Raul:
Believe what you want about morality, but natural law can't be denied:Men and women are not interchangeable. Try replacing the Giants Linebacker corps with women. Choose any women you want, see if there is a functional change.
2008-12-03 16:03:13 by Sam:
Men and women are equal in rights and equal in the eyes of the law. Giants linebackers are of course selected according to their suitability for the job, as is the case for any job. Men are generally stronger than women, which generally makes them better for the job, but if a sufficiently talented/strong woman came along then there's no reason why she wouldn't get picked.
And I don't know about this "natural law" thing. Just because something is natural, and it would extremely difficult to change, doesn't make it a "law" and doesn't mean it shouldn't be changed.
2008-12-03 18:08:49 by Tarot:
Sam:
"Just because something is natural, and it would extremely difficult to change, doesn't make it a "law" and doesn't mean it shouldn't be changed."
Of course, you don't want to use this argument too much without some clarification because you're not going to like it when you run into someone who says "I'll admit homosexuality is a natural thing, it occurs naturally. Just because something is natural, and it would be extremely difficult to change, doesn't mean it shouldn't be changed."
2008-12-03 18:31:42 by Sam:
Nobody on the planet thinks like that.
2008-12-03 21:38:35 by Mick:
Well, he apparently thinks like that, but, yeah. That 'logic' doesn't really make since.
2008-12-04 14:08:21 by jaymie:
@RichardBronosky
I'd hate to be in a comma - full-stop.
2008-12-04 18:20:44 by JonHanna:
Good use of topical subject matter to discuss database normalisation and schema design. Really, the only bit that was strange to my mind was the idea that poly relationships would have everyone involved married to everyone else; that's certainly not how I've seen them, they've tended to be graphs as in the 14th schema but the 12th and 13th schemata are much rarer (I don't even know of anywhere that legally recognises or recognised them, though I'm sure anthropologists and historians can find examples, though I'm just as sure they'll largely be ignored - there are already plenty of people who think gay marriage is some sort of new thing, and not largely the artefact of localised decisions and the historical accident of Rome's legacy to Christendom meaning that the ban on gay marriage in the 5th Century CE ended up having wide-ranging effects). It made sense to have have them where they were in terms of discussing databases, but they stick out a bit as unusual in terms of real-life.
One thing that the 12th and 13th schemata do raise though. There are often well defined relationships between people who share a spouse, sometimes codified in such concepts as "sister-wives" (women in heterosexual marriages to the same man, and forming part of the same household) and sometimes not (pretty much everyone in a poly relationship that doesn't live in a cultural, religious and/or legal framework that recognises their family structure). There are also though plenty of cases where two people would share a partner but have little or no relationship to each other.
While I was musing on the possibility of recording it in your hypothetical database it mixed on musings on your last point about noncommutative relationships. Certainly plenty of those exist, after-all fights against this being the de facto state of marriage covers an awful lot of the progress made in sexual equality over the last thousand years either directly or indirectly.
Still, they do still exist and sometimes both partners want it that way. They may be of religious views that man is the head of the household and this is a natural order set by their god or gods, or they could believe the woman is the natural head of the family unit (or either of those with the genders reversed). They may be in a D/s relationship where one person prefers to submit and the other to dominate.
Now, should we need to record either the relationships that may or may not exist between those who share a partner, or the power-dynamic within a relationship, then we want to record not just the edges of the graph, but also label those edges. This naturally extends to cover the cases of multiple forms of marriage; religious only, state only, or both, or the fact that there are both multiple forms of marriage in some jurisdictions both historically (e.g. Brehon Law in Ireland with several forms) and now (e.g. states in the US all have marriage but some have also civil partnership registrations of various forms and three have also covenant marriages which make it harder to get a divorce).
A rules engine can now cope just as well with a legal marriage in a state that only allows heterosexual monogamous marriages, a religious marriage between three or more people of the same sex, a D/s collaring ceremony, or any other combination one might think of.
Of course the rules of each of these different types of relationship vary considerably. So much so that we can no longer have any rules on who can marry whom hard-coded into our schema at all, but rather we must record the rules about the forms of relationship in the schema also and our validation will require a rules-engine to work from it.
Ironically, this now allows for the encoding of the fact that some forms of marriage (many religious marriages, and backwards legal jurisdictions) are indeed male-female only (with varying degrees of concepts as to what defines "male" and "female", with for example Iran banning gay marriage - or any homosexual activity - but allowing post-op MTF transsexuals to marry men).
With a rules-engine we can have a system that allows people who don't agree with gay-marriage to enter into a form of marriage that can only be male-female. At which point they can STFU and let the rest of us marry whoever we want.
2008-12-05 10:46:51 by leonardj:
@ Mark 2008-11-29 - " As a database designer who is looking into how family trees are modelled, this was fascinating."
Not half as interesting as Jocasta's place in the House of Laius tree published in the National Theatre programme for the current production of Oedipus.
2008-12-06 02:57:28 by townmouse:
PietroSperoni was half-wrong twice.
In human beings, and in multicellular animals in general, the only (known) cytoplasmic genes are in ribosomal RNA. Not DNA.
2008-12-09 19:08:35 by Rory:
I helped foster parent-ish organization with their database design... We spun out several tables to handle all sorts of situations. Something like this... It let them track kids and parents going from one family to another over time.
`people`
- `id`
- `forename`
- `surname`
- `birthdate`
- 'birth_father_id'
- 'birth_mother_id'
- 'gender'
`family`
- `id`
- `type`
- `start_date`
- `end_date`
'family_member'
- `family_id` (foreign key references column `family`.`id`)
- 'person_id' (foreign key references column `person`.`id`)
- `role_id` (foreign key references column `role`.`id`)
- `join_date`
- `leave_date` (NULL if still in family)
2008-12-12 23:02:10 by Letters:
Sorry, I have to argue with your remarks on several points, but I'll just point out the biggest ones. More than likely, those systems are not set up in a normalized fashion so that all the males are in one table and a females in another, nor do they have "humans" and "marriages". Since it is a given that a marriage is made up of two people and that will never change (we'll get to gender in a moment), then the database probably has one entity: MARRIAGE_LICENSE. Everything in that entity is just an attribute of that entity. "Husband" is an attribute, and so is "wife". So as the clerk enters the information from a paper form into a computer, all the details go into a single table: date submitted, husband info, wife info, license number, clerk's name, witnesses name, etc, etc. The paper form is labelled "Groom" and "Bride". The paper is actually irrelevant. They might as well label it "partner 1" and "partner 2". The applicants can simply pick a person to go in each one (even if they put a male in "bride"). Over time, they can slowly phase out the old forms and bring in the new ones. As for the system, they can either leave the table as-is and simply rename that text block on the front-end interface, or they can rename the column in the table as well. Problem solved in a simple .1 release.
2008-12-19 03:34:12 by eriqalan:
too bad you never programmed such databases.
What you really want is a Head of Household name / address (HHADR) file and then a related file of people related to that head of household (includes children, etc. in the household. Libraries, Parks and Rec., Police Departments, Vital Records (Birth certificates, etc) et. al. live by HHADR files; if the family moves all you change is the HHADR entry, etc.
Pyramid type businesses ("Multi-level Marketing") have their own spawning structure (usually Binary - a recruits a1 and b1 who each recruit their own a2 and b2 which are a1a2, a1b2, b1a2, b1b2; etc. this is how amway, new vision, et. al. work.
But then it would not have been as funny
2009-01-10 18:44:33 by Dominique:
Great read!
But for the purposes of polygamous marriages, wouldn't it be simpler to have a table like this:
`humans`
- `id`
- `forename`
- `surname`
- `birthdate`
- `marriage_id` (NULL if not married)
`marriages`
- `id`
- `marriage_date`
2009-01-21 18:37:39 by RichMest:
LachlanMcDonald,
You need an ID for each marriage. My brother and his wife were married twice, once civil and once religeous. The database should handle both.
2010-03-11 16:39:31 by R:
The post at 2008-11-21 21:05:32 by Allan does it all. Considering that 80% of the government servers are from the '80 I'd assume off the bat the database was created in a DBMS without support for constraints or enforced primary keys. Try working with Sybase if you can't imagine that. Also programming at that time was most likely created with Cobol so making the changes would be god awful. Interesting point about the thought given to supporting the marriage change from a technical aspect.
2010-03-11 18:26:23 by MikeRoberts:
I love how data storage techniques can be sexist. If you moved all of the marriage data to the female side it would still be called sexist because that would mean that a man isn't really married, but a woman is. Besides with that setup and man can have multiple wives, but not the other way around.
When you're insecure everyone is out to get you.
2010-03-11 21:35:07 by Pasha:
I like the above comment about the relationships. Why bother with marriage at all? Why not have:
relationships (purposefully unidirectional)
human_id_1
human_id_2
rel_type_id
rel_type is something along the lines of
(is committed to)
(has a crush on)
etc..
facebook is probably facing the same problem right now, as they are unable to really codify the full
extent of people's relationships.
2010-03-11 21:45:50 by MikeWilliamson:
People tend to think that technology is neutral. This is a great example of how we embed our values into our technology. Absolutely awesome.
2010-03-11 21:54:34 by Alex:
Easily the funniest among all the clever things I've read this year. Oh, and cleverest of all the funny ones, too.
2010-03-11 21:57:15 by GregWilson:
At least you're not trying to alphabetize things: http://pyre.third-bit.com/blog/archives/1463.html.
2010-03-13 06:52:32 by Liz:
Now please fix the databases to deal with my hyphenated name.
K'Thnx - Mrs. Ray-Trumitch
(Great Post!)
[lmao, your "your name" field won't take my name either! "names can consist only of letters" Who makes these rules!]
2010-03-17 15:50:51 by Marc:
Believe it or not, I've read this until the end.
Very entertaining !!
2010-03-22 12:48:43 by LP:
If God had a database I bet it would be uber-awesome!!!
2010-03-31 16:29:00 by potto:
So, ultimate solution is move to a schema-less db like CouchDB, then we can all get along! :)
2010-04-15 04:08:23 by namesdonotonlyconsistofletters:
First, very interesting post on the subject!
Next, "names can consist only of letters"? Wrong! I would like to add to the pleas that systems handle perfectly legal real-world names better. A British database (I think it was a bank) I had the misfortune to interact with was incapable of handling a space *inside* a person's first name. Italian systems do handle this. This resulted in the two countries systems having inconsistent records. Luckily the Italians accepted the broken British data. The space does not always mean multiple forenames: there is a difference.
2010-04-15 08:17:48 by Sam:
There's a pretty big difference between an official government census database and a commenting system on someone's website. Pick a handle or something.
2010-04-20 01:17:14 by Tantek:
I think the key is to simply distinguish attributes about one person in particular (e.g. their name) and relationships *between* people. Any attempt to collapse those two is fraught with problems (as this article works through in so many steps).
On the Web we've done this with hCard[1] for people (since 2004), and XFN[2] for relationships (since 2003, specifically with rel="spouse" for the particular use case mentioned in this post). It's quite simple, and it maps to people's existing real-world web-publishing behaviors.
It's databases that are the problem - they don't reflect the way people think of content/information. Web pages are closer.
Tantek
[1] http://microformats.org/wiki/hcard
[2] http://gmpg.org/xfn/
2010-05-31 22:52:51 by Anonybot:
There are other reasons for termination of a marriage: death and annullment spring to mind. Change divorce_date to termination_date and include a termination_reason_id from a termination_reason lookup table.
Also, since anti-gay marriage people claim gay marriage is yet a stepping stone to other marriage abominations (their word), why assume each partner in a marriage is human? Sheep need love and protection under the law, too!
2010-06-03 20:47:16 by betabug:
Get an object database.
Then a marriage becomes a container like object, containing any number and kind of person objects.
2010-06-17 09:50:21 by Webdrifter:
You could have saved a lot of time by some decent modeling of the problem.
You have:
- Groups (defines properties of groups) (a group without child-groups is an individual-entity)
- Relationships (defines relations between groups) (the type of relationship should at least define whether it is a parentgroup-childgroup relationship, which is a vertical relationship, or group-group, which is a horizontal relationship)
The terms "parent" and "child" used here come from systems-theory (you can also find this termonology in OOP).
2010-06-17 10:04:03 by Webdrifter:
Your next questions should be;
- Do I want every kind of group in my database or just specific kinds.
- And which info do in wish to register on them.
- Do I want every kind of relationship in my database or just specific kinds.
- And which info do in wish to register on them.
Most applications just do not need every option open and every information registered.
It all boils down to what you are going to use your database for, and in which (legal, social, business) environment it has to operate.
By the way, what is wrong with Sexism?
Sexism to my opinion is part of the natural order of the universe, since the universe is nature is clearly Sexist.
However, I would agree with you that forms of Sexism based on non-natural grounds should not be tolerated.
2010-06-17 20:08:10 by RichardGaywood:
Not only is this awesome on its own terms, it'd also be a fantastic worked example for anyone studying database design.
2010-07-09 20:13:20 by tom:
There's renewed interest in this article thanks to XKCD, so I don't mind posting 2 years after the fact. A couple of points should be made with regard to poly marriage. Though you eventually account for this, your acceptance of polygamy does not reflect traditional polygamy: old-school Mormons and Muslims accept a husband having multiple wives. The wives are not intermarried, and female-female love is strictly forbidden by the church -- they share the "husband's other wife" relation, but none of the man's wives have wives.
As Pasha noted, relationships are much more complicated than marriage. They can be assymetric, weighted, temporary, permanent, periodic, or fluctuate aperiodically.
2010-07-31 10:32:46 by Abigail:
2008-11-21 19:36:42 by RichardBronosky:
"My wife and I would have preferred to have chosen the terms of our union, rather than to take whatever the state decides to consider marriage at whatever point it impacts us....
My wife and I would prefer to have more critical terms in our contract:
1. The person who chooses to leave the marriage leaves with nothing. No custody of the children, or assets.
2. The person who leaves the marriage is never free to remarry. Marriage is forever.
3. Two become one. Either person is empowered to make decisions for the other in absentia. If I'm in a comma, she gets to choose to keep me alive as long as she wishes and can change her mind whenever. (Limited only by logistics.) My parents don't get to raise a stink.
I don't know what I would call the wishy-washy garbage that is happening today, but it's not what we want to be a part of."
Although I can appreciate wanting your civil contract personalized to fit your needs, customization strikes me as a terrible idea (or at least having the above options available). In the scenario of an abusive relationship, clauses such as the first one would be an incredible barrier preventing a victim from leaving their abuser. If you leave the marriage, you will have no money and no custody over your kids (and what if they are also victims of abuse?), nor will you ever be allowed to remarry once you find a healthy relationship with a non-abusive individual (or individuals!). It's hard enough to leave an abusive relationship, and this could make it impossible (no money/assets) or morally problematic (to say the least... leaving your kids with your abuser?!?) for many.
I would hope Mr. and Mrs. Bronosky are indeed in a healthy and mutually respectful marriage, and while it is their prerogative to have such rules for themselves to solidify their commitment, the option of making them legally binding could force someone to stay in a dangerous and possibly lethal situation.
2010-08-03 03:59:05 by james:
You forgot marriages with pillows and anime characters! They are possible in Japan:
http://www.metro.co.uk/weird/816601-man-marries-pillow
"The pillow marriage is not the first similarly-themed unusual marriage in recent times - it comes after a Japanese otaku married his virtual girlfriend Nene Anegasaki, a character who only exists in the Nintendo DS game Love Plus, last November."
2010-08-05 03:53:58 by Tiferet:
@Art
I've come across the problem of a sex change in a database. I run a large fantasy role-playing game; we wanted to build a family tree or failing that, at least a database, for the characters.
We have a character who was born biologically male, transitioned to functional female and then through various magical means became biologically female. She has had a wife and two husbands; she is the mother of five children, biological father of two and mother of three.
It's a bit challenging.
And you know, the technology for this to really happen will eventually exist.
2010-08-06 04:04:16 by JustSomeone:
I think you are being species-ist there. I mean, what if I wanted to marry that hot Orion Slave girl? Saying "Humans" is too narrow of a mind.
2010-08-10 14:50:25 by pinkgothic:
You 'lost' me at two places.
One, that a marriage with multiple people would ever be designed as a single marriage with multiple people attached to it, rather than a list of binary marriages. I'm not sure why someone would want to map it that way.
Two, that a second-degree marriage (I'm married to X who is married to Y) has any bearing on the person in question.
For #2, I'll accept it's hypothetical and it might, of course. Hell, I'll accept that for #1, too. I loved the article! I suppose, myself being a polyamourous person, I just view the whole thing with a natural simplicity that takes other people a few more steps to stumble across.
I am, however, also anti-marriage; so I suppose my approach would be much as your stray thought in your afterword. :)
Thanks for this, had a great time reading it, hilarious! I love database.
2010-09-25 23:07:02 by rs:
And just to think... abolishing marriage altogether would just make this problem vanish completely! It's only recognized on a legal level for historical reasons, and is clearly declining in developed countries, as younger generations see fewer and fewer reason to actually marry. Makes perfect sense if you ask me.
2011-01-11 17:36:54 by JPad:
If the point of the discussion was gay marriage, all the discussion of polygamy was irrelevant. You could still create a binary marriage structure between two humans, leaving sex and gender inconsequential. Simply:
'humans'
- 'id'
- 'partner_id' (references 'humans' and may be null if not married)
- 'marriage_date' (null if not married or divorced)
2011-05-12 18:50:44 by TomAnderson:
Leaving the metadata aside for a moment, if the central problem is tracking who is married to who, an easy and efficient way to do this would be to have a single PERSON table, with a column SPOUSE REFERENCES PERSON NULL, but rather than trying to have every person reference their spouse (which limits you to two-person marriages, and admits all sorts of errors), arrange things so that there is a directed acyclic graph in each marriage: let one person have a null spouse reference, and have all their spouses reference them, or another spouse who references them (you can enforce directed acyclicity by defining a total order, and only allowing references from a more junior to a more senior person - you could generate the order by sorting by age, or by name - or, like a good egalitarian and bad DBA, by surrogate key, although an advantage of the surrogate key is you can write a CHECK constraint that compares the spouse reference to the person's own key).
You can marry two single people by setting the junior's spouse reference to point the senior. You can marry a single person to an existing marriage in which the senior partner is senior to them in the same way. You can add a new senior partner to a marriage by having the existing senior point to them. You can merge two entire marriages - of any size - by having the more junior senior partner point to the more senior one. You can divorce someone by nulling out their spouse reference; if they have more junior spouses pointing to them, set their references to point directly to the senior spouse who is being divorced. You can tell if two people are married by chasing spouse reference until you hit a null, to find the most senior person in the marriage; if it's the same person, they're married. As a maintenance task, you can periodically update everyone's spouse reference to point to the most senior person in their marriage, to speed up is-married queries.
This structure sounds odd, but it is flexible, fairly simple to implement, and above all, fast - the marry and is-married operations are approximately O(1), although the function actually grows as some weird term involving the inverse of the Ackermann function. I know this because i did not in fact invent it, although i think i have here proposed a novel application of it.
I propose to call this structure either the "conjugal disjoint-set data structure", or, if used for homosexual purposes, a "civil union-find data structure", and i wrote this entire comment merely so i could make those two extremely weak puns.
2011-06-02 00:52:26 by TravisW:
I've given this some thought too, and I think it's fair to remove further assumptions about what it takes to form relationships and instead just recognize arbitrary relationship definitions to be created and endorsed both officially and unofficially.
For example, removing your endorsement for a relationship of the type "marriage" you are a member of would be essentially a divorce.
http://pastebin.com/8VthZKft
2013-02-06 23:33:50 by Emilsson:
I think you did not go far enough.
You missed a hypergraph marriage.
That is, marriages are blobs of multiple people, and anyone can be part of as many of these blobs as wanted.
2013-03-18 18:15:47 by buni:
The only thing in this article with which I take issue is the constant assumptions being made about humans' names.
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
2008-11-21 01:08:23 by Isaac: