{"id":11730,"date":"2015-07-16T11:23:40","date_gmt":"2015-07-16T15:23:40","guid":{"rendered":"http:\/\/mjtsai.com\/blog\/?p=11730"},"modified":"2015-07-16T11:23:40","modified_gmt":"2015-07-16T15:23:40","slug":"obergefell-v-hodges-the-database-engineering-perspective","status":"publish","type":"post","link":"https:\/\/mjtsai.com\/blog\/2015\/07\/16\/obergefell-v-hodges-the-database-engineering-perspective\/","title":{"rendered":"Obergefell v. Hodges: the Database Engineering Perspective"},"content":{"rendered":"

Sam Hughes<\/a> (epic 2008 post):<\/p>\n

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.<\/p>

[…]<\/p>

No matter how advanced and flexible your table structure, it will always be possible to create data which cannot fit into it. At that time, you will need to change your database. And the longer it’s been since you did, the less pleasant that’s going to be.<\/p>

The lesson is not<\/em> “prepare for every possible eventuality”. The lesson is to become comfortable and confident in modifying your schemata without losing data, and rolling back botched changes. Do this regularly, so that it becomes second nature. The lesson is to get used to change.<\/p>

And what is true of our databases is also true of our world views. The future is vast and humans are creative. Things are going to happen which nobody could predict.<\/p><\/blockquote>\n

Sam Hughes<\/a> (2015 update):<\/p>\n

To investigate the specific ramifications of today’s ruling, however, here’s the schema we’re probably starting with:<\/p>

[…]<\/p>

Already the constraints on a schema like this are quite complicated. husband_id<\/code> and wife_id<\/code> are both foreign keys for column people.id<\/code>. Check constraints ensure that the value of marriages.husband_id<\/code> always points to a people<\/code> row with gender<\/code> set to “male” and the value of marriages.wife_id<\/code> always points to a row with gender<\/code> set to “female”. (Exactly how the gender<\/code> column should be structured is outside the scope of this essay, but the values “male” and “female”, at least, should be available. Structuring the name<\/code> column is even further out of scope, because yikes<\/a>.) divorce_date<\/code> is nullable. Probably there ought to be another check constraint which ensures that divorce_date<\/code> doesn’t come before marriage_date<\/code>.<\/p>

It might be required to incorporate some sort of check for duplicate combinations of husband_id<\/code> and wife_id<\/code>… but then again, this could make it impossible for a couple to e.g. marry in 1994, separate in 2009 and then remarry in 2015.<\/p>

[…]<\/p>

But the more interesting thing is that you just incidentally let in a whole bunch of edge cases. Up until now, it wasn’t possible for an individual to marry themself. Now it is, and you need a new check constraint to ensure that partner_1_id<\/code> and partner_2_id<\/code> are different. Regardless of concerns about duplicate rows\/couples remarrying, you also now have to contend with swapped partners: Alice marries Eve, and also Eve marries Alice<\/em>, resulting in two rows recording the same marriage. This can typically be prevented by ensuring that partner_2_id<\/code> is greater than partner_1_id<\/code>, which would incidentally also prevent self-marriage as described above. Note that this could in turn invalidate previously-existing heterosexual marriages where the husband_id<\/code> was lower than the wife_id<\/code>. This constraint would have to be applied for future inserts only, or the disordered rows would need to be swapped.<\/p><\/blockquote>","protected":false},"excerpt":{"rendered":"

Sam Hughes (epic 2008 post): 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.[…]No matter how advanced and flexible your table structure, it will always be possible to create data which cannot […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"apple_news_api_created_at":"","apple_news_api_id":"","apple_news_api_modified_at":"","apple_news_api_revision":"","apple_news_api_share_url":"","apple_news_coverimage":0,"apple_news_coverimage_caption":"","apple_news_is_hidden":false,"apple_news_is_paid":false,"apple_news_is_preview":false,"apple_news_is_sponsored":false,"apple_news_maturity_rating":"","apple_news_metadata":"\"\"","apple_news_pullquote":"","apple_news_pullquote_position":"","apple_news_slug":"","apple_news_sections":"\"\"","apple_news_suppress_video_url":false,"apple_news_use_image_component":false,"footnotes":""},"categories":[2],"tags":[143,144,677],"class_list":["post-11730","post","type-post","status-publish","format-standard","hentry","category-technology","tag-database","tag-nosql","tag-sql"],"apple_news_notices":[],"_links":{"self":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/11730","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/comments?post=11730"}],"version-history":[{"count":1,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/11730\/revisions"}],"predecessor-version":[{"id":11731,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/11730\/revisions\/11731"}],"wp:attachment":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/media?parent=11730"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/categories?post=11730"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/tags?post=11730"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}