I have never seen a paper talking about gay marriage that was entertaining, doesn't matter if you're for or against it, still an entertaining read, now THAT's how articles about current issues should be written!

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.

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. :\

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).

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 !

Sam, Will you marry me?

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 !

Steve: we'd only end up hurting each other

You could have left 1 to 6 out. Seriously...

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.

lol: There's a good reason I kept 1 to 6 but it needed clarification, which I've added. Thanks.

"Repulsive database integrity problems in their heads". Sam, I would totally gay marriage you.

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.

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.

That was really interesting, and led me to reminiscing about Heinlein books I have loved.

You never store Business Logic in your Database!

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.

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...

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.

Great read!!

The close relations thing is a problem of sex, not a problem of marriage. Preventing the latter doesn't prevent the former.

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`

What's the difference between a marriage and a civil union?

"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.

Holy crap. That was the best database engineering primer I've ever read.

Wow, you've discovered first through third form normalization in a series of fourteen steps. Welcome to 1973.

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

StoneCipher, I think Mr QNTM was actually demonstrating how others may approach database engineering problems, not his own recent learning experiences.

Jake, she would be legally required to divorce you first though, eliminating that problem.

That, sir, was amazing.
you put soooo much thought into it. WOW.
good points, dude~~~~

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.

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.

Interesting hypocrisy, Sam, you closed-minded engineer ;-) http://skitch.com/richardbronosky/hkgh/qntm-new-comment

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.)

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.

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

Kudos! That is by far the most interesting and entertaining article about modelling human relationships in relational DBs.

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.

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.

Sam,

Very interesting read. Thank you.

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.

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.

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)

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...

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.

I'd have to argue that marriage is not transitive. But aside from that, nicely done ;)

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.)

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).

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.

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.

2 + 2 = 5

>>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)

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.

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...

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.

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.

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.

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

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?

how is marriage blob formed

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?

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....

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

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?

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?

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".

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
...

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.

"why I haven't just skipped schemas (schemae?)"

schemata

This post is everything that I believe in.

adamo: Thank you. I was going to say that. (How can anyone not know that "schema" is Greek third declension neuter?!?) :)

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.

A fun and excellent read, logically following some obvious paths given the current discourse on marriage. And hey, databases =)

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.

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."

hah y2gay, excellent.

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.)

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/

@ 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?

@ 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?

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".

I less than three you.

This is really fantastic. Thanks. I'm passin' it along.

Am I the only one who'd really prefer that the information not be tracked at ALL ?

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.)

So in your world nobody is ever widowed?

Absolutely amazing Sam. That was a great read, thanks for that!

The Belgians have an answer:

http://nhw.livejournal.com/430075.html

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.

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.")

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).

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.

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.

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.

Consequences of gay marriage: http://eatliver.com/i.php?n=3722

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.

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.

Optimise for common case.

This is certainly not optimised for the common case.

"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

I've not noticed any hits coming in from pythian.com. Maybe they've been drowned out by the deluge from b3ta.com? ;)

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.

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.

As a database designer who is looking into how family trees are modelled, this was fascinating.
Thanks.

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.

Indeed - that was my point. My post was in response to 'Steve' above.

Could well be adopted as a (junior level) database developer interview question.

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.

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.

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.

Now I have a headache

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.

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.

My proposed system would be

`people`
- `name`
- `id`

The end.

I don't think there'd be any problems with that.

You did not just add an Italian translation of this.


You did.

That's awesome.

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.

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!)

This ought to be published in school computer science texts.

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.

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.

Men and women are equal in rights and equal in the eyes of the law. That's what matters.

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.

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."

Nobody on the planet thinks like that.

Well, he apparently thinks like that, but, yeah. That 'logic' doesn't really make since.

@RichardBronosky

I'd hate to be in a comma - full-stop.

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.

@ 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.

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.

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)

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.

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

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`

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.

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.

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.

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.

People tend to think that technology is neutral. This is a great example of how we embed our values into our technology. Absolutely awesome.

Easily the funniest among all the clever things I've read this year. Oh, and cleverest of all the funny ones, too.

At least you're not trying to alphabetize things: http://pyre.third-bit.com/blog/archives/1463.html.

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!]

Believe it or not, I've read this until the end.
Very entertaining !!

If God had a database I bet it would be uber-awesome!!!

So, ultimate solution is move to a schema-less db like CouchDB, then we can all get along! :)

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.

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.

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/

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!

Get an object database.

Then a marriage becomes a container like object, containing any number and kind of person objects.

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).

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.




 

Not only is this awesome on its own terms, it'd also be a fantastic worked example for anyone studying database design.

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.

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.

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."

@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.

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.

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.

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.

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)

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.

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

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.

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/

It may be a common programmer assumption, but I think it's safe to leave in this context because the database in question is in service at a bureaucracy. Bureaucracies have always been weirdly hard headed about things like name structures. "There is a 'Middle Name' line on the form and all lines MUST be filled out or the form will be rejected as invalid."

I knew a family whose children were only given middle initials, on the reasoning that most official forms they'd have to fill out would be satisfied. I also knew a guy who was part of a family tradition of rearranging the same 6 or 7 names each generation thus producing and odd mixture of xxx The 3rds or whatever.

Trying to lay out and name such very Human things as names in ways that bureaucracies and computers can cope with is really just a world of compromise.

I absolutely love this, but a quick pedantic mathematician note: "Commutative" isn't the word you're looking for here, you mean "symmetric".
Commutativity is a constraint on group operations rather than binary relations, and would mean that if Alice marries Bob then Bob marries Charlie, the outcome is equivalent to Bob marrying Charlie then Alice marrying Bob.

Nuts. Before we get to this point, let's change the record keeping schemes that insist on first_name, middle_initial, last_name. There are a lot of people who prefer to use their middle name. Yes, I am one. It used to be that when I got a letter addressed to first_name last_name I could safely throw it out as junk mail. Some systems allow me to use preferred_name last_name. But the federal government in its wisdom fits everyone into a scheme of first_name, middle_initial, last_name, regardless of what name they use daily, use elsewhere, or what they prefer.

Hank, you're a bigot. Thanks for the slut-shaming in an article about marriage and databases.

Excellent article and comments. As a gay database admin, I encountered these issues long ago and chose to handle them as Webdrifter recommended using application logic to subclass Group for relationship types. A role field helps define their role in the group - father, mother, husband, wife, spouse, son, daughter or child in a family. Group is not limited to families, it can also be used for organizations, interest circles and event attendees with relevant roles. Not interested in imposing biological inspections, I record gender only to determine proper pronouns when talking about him, her or them and accept the individual's identity perception. As a database admin, my job is not to make sure groups conform to the law, only to model them as data (my database is sure to go where no law has gone before). My only concern is accurately modeling these relationships in the context of my company's needs.

I wish we could define legal relationships in much the same manner. The government shouldn't be concerned with our bedrooms, only with the legalities of these relationships and a wide variety of relationships, both sexual and non-sexual, can benefit from the legal rights of marriage. Marriage will never be stricken from the Bible, so we should strike it from law and base civil unions strickly on legal needs. People have thumped their Bibles in Leviticus at me for years, but I have yet to have anyone thump the Ten Commandments because I have to lie about my relationship on my taxes every year.

As I look at my database that has survived marriage, civil unions, polygomy, divorce and gender reassignment for 25 years, I wish it were as easy to prepare the law for the future.

What Sam describes already exists. It is a fully recognized legal entity in common use. It is called a limited liability company (LLC http://en.wikipedia.org/wiki/Limited_liability_company). Since the point of marriage (from a legal standpoint) is to protect the assets, including children (ahem), this fits nicely into existing database schemata. People can join the LLC or leave it. It gives legal protection to the assets of the LLC even if someone dies, leaves, joins, and so on. It allows multiple members regardless of gender or sex, and even allows a person to part of more than one LLC.