Tracking Families in a Database

Developers out there: What is the best way to track families in a database? Trivial question, right?

You'd better get this right! Your application may end up being used by your company's (or client's) employees with a vip at the end of a phone call, and it's important for your company's employee to quickly add/update/change records to reflect changes to their family (you wouldn't want to send out a happy anniversary card if the donor is divorced, or send one to his neighbor, but miss hers)!

Oh, it is important to get right; but it's a trivial problem... so... think about it for a moment before you read on.

Here's one common approach:

Tables (columns:
people (name, date of birth, etc)
relationships (RelationshipType, personid, personid)
relationship_types (Father, Mother, Cousin, Uncle, Twice Married Half sister's mother cousin, etc)

That sounds fun. Some individuals would end up with dozens of connections; often connections are duplicated because it's hard to check for duplicates in such a scenario, and your business rules better be perfect otherwise your data is going to get corrupt. Not an instant nightmare, but a nightmare that'll get you two years after your application is in production and you suddenly need detailed reports requiring that family relationship info.

So say we have a family Mother: Sue; Dad: Bob; and child: Tofu. Tofu has 2 relationship entries linking him to Bob; Bob has 2 relationship entries linking him as husband to Sue and father to Tofu, Sue has 2 relationship entries linking her to Tofu as Mother and Bob as Wife. 6 relationship entries for 3 people. Add in more children, grandparents, 2nd marriages, and suddenly you have a pretty precarious situation. Almost certainly to get a full picture of a person's relationships you will find out your database knows about relationships that your app doesn't, because those records weren't added.

Is there a better way? Sure. Track events in people's lives instead.

people (First Name, Last Name, ...)
life_events* (EventType, Date)
people_life_events (Assocation Table: Event, Person, Title)
person_life_event_title (Lookup table: name; example: Father, Mother, Child, Husband, Wife, etc)
life_event_type (Lookup table: name; examples: Marriage, Birth, Divorce, etc)

*side note: life_events seems the best term to me; although I prefer one word table names. There are some options out there, but they aren't great. milestone, event, occasion, happening, or epochal.

Example: Dad: Bob; Mom: Sue; Son: Tofu.
3 Person records (Bob, Sue, Tofu)
2 Life Events (connecting event_type(marriage) and event_type(birth) with their persons)
2 associations in life_event_people for the Marriage, 3 for the birth
3 person_life_event_title records (husband, wife, child)
2 life_event_type records (marriage, birth)

Here's a Google Doc to help you visualize the data:


[or you can view the actual Google Doc here]

Now, there is no duplicate entry; and your application will know even complicated relationships correctly every time.

You might get to the point one day where you decide person_life_event_title can be converted to an enum. You certainly don't have to use it for generating reports; just use the string 'Father' etc. This is true for the event_types also.

Hint: Spend time on your models. It's the foundation on which your entire application will stand. Don't worry about how you're going to work with the data, worry about making a solid foundation. Your database shouldn't have to change much, if at all, besides adding new tables and new associations if you build it correctly.

Now Discuss:

Django ListFilter e=1

I ran into some odd issue with Django the other day when attempting to inherit the ListFilter class in the Django Admin app.

I was creating a custom filter to allow a date range to be selected; and doing this required that I add the date parameters into the query string. Not a big deal, I thought. Well, turns out; it was a bigger deal than I thought. Apparently Django Admin had decided that query string parameters needed to be explicitly declared by the filters; otherwise the app would silently error on them; and replace them with e=1 (strange choice, I think). I wandered all over the internets to find an answer to why my query string class_start_time=2013-01-01 was being converted to e=1. Nothing. Crickets.

I ended up debugging the app itself to determine the root cause of the problem; Pycharm made this a fairly painless procedure.

All of that to say: If you ever find yourself wanting to inherit the ListFilter; and adding some of your own query string values; you need to remove those from the params dictionary that will get passed to your classes __init__ method. 

del params[parameter_name]

Most of the time, the Admin app makes life very easy, and sometimes you end up spending an afternoon debugging source files to figure out what's going on. At least there are well written source files to debug!

Here's a link to the StackOverflow question I posted on this: http://stackoverflow.com/questions/15792884/creating-custom-list-filter-in-django/15797139#15797139