Building a Family Database: Member Information and Relationships, Family Tree, and Birth/Death Calendar

I’ve had this idea of collecting biodata of my big family and visualise it using a family tree since years ago. I explored some possible solutions during my free time and integrate them all into my own website.

What I had in mind was to store raw information about family members, who their parents are, when they were born (and died, if deceased), and manipulate as such that I can transform them into beautiful and informative pages.

I know there are a lot of similar services available online that I can use. However, as with my expense manager (which I might write about in the future), I don’t like the idea of handing over my private information to other people. Even if they have a very good privacy policy, I do like the idea of owning the data and be free to process it in any way I want.

Data Design

To be able to visualise data, we need to figure out how to store that data first. I chose to build the system on top of my existing personal website, which is built on Ruby on Rails backed by MySQL, MongoDB, Redis, and Elasticsearch. Therefore, I’m utilising MySQL for the primary data store, Redis for the cache store, and Elasticsearch to ease querying the data (I’m comfortable with it, don’t judge).

First, we need to establish what kind of information you want stored. For a single family member, I figured this information suffices (with the asterisks marking the mandatory attributes):

  • Full Name*
  • Nickname
  • Email* (for authentication purpose)
  • Phone number
  • Address
  • Place of birth
  • Date of birth*
  • Sex*
  • Place of death
  • Date of death

If you feel like adding more fields, there’s no one stopping you so feel free.

What’s a family without defining relationship of each member? We have several relationships in a family, with only one hop of line (picture a family tree), there are already father, mother, husband, wife, son, daughter. Add one more line and you’ll get brother, sister, grandfather, grandmother, son in law, daughter in law, grandson, granddaughter. There’s practically hundreds of possible relationships between you and other family members (if you want to trace your family back to your great-great-great-great-great-grandparents). So we need to figure out a simple relationship model that can cover all kind of relationships.

The solution to that problem would be person-father and person-mother relationships. Using those two relationships alone you can already cover most of the relationships you have in a family. Want to find out who your uncle is? Find the males who have the same father or mother as your father’s or mother’s. Second cousin once removed? I’m not going to write the long path to get to it, but you get the point.

However, there’s a slight problem with the relationship model above. Say you want to find out who your wife is (get that hangover feeling?), the only possible way is to see who the mother of the people who have you as their father, meaning you need to find out who the mother of your children are. But what if you don’t have any children? We then are going to need an additional relationship in your data, person-husband relationship, that will come in handy to handle the couples who don’t have children.

Look how beautiful my diagram is
Look how beautiful my diagram is

Family Member Information

Let’s assume for now that you have access to open a family member’s information page without having to locate them in the family registry (be that a tree or a table). What kind of information do you want to display?

In my case, what I show is:

Biodata

Full Name: Lorem Ipsum Dolor
Nickname: Lorem, LorBro
Age: 27 years 3 months 22 days
  [or if the person is deceased]
  Age at death: 27 years 3 months 22 days
  Time since death: 3 years 2 days
  Total age: 30 years 3 months 24 days
  [/or if the person is deceased]
Place/Date of Birth: Neuvis, 19-05-1905
  [if the person is deceased]
  Place/Date of Death: Octocapolis, 10-09-1932
  [/if the person is deceased]
Sex: Male
Email: loremcool@mail.neuvis
Phone: +987654321
Address: [redacted]

Relationships

Grandfather: -
Grandmother: -
Father: Kovarex Dolor
Mother: Donna Paulsen
Wife: Lulu
Children (3): Andpad Dolor - Zyvan Rusto
              Bymine Dolor
              Charlie Dolor
Grandchildren (1): Loggy Dolor

Some of the data shown are stored as is in the database above, and some need to be derived from it.

Age

I built a helper function to calculate elapsed time from a point in time to another. The function basically looks like this:

def time_elapsed(since, up_to)
  ret = []

  year = count(since: since, up_to: up_to, measured_in: :year)
  if year > 0
    ret << "#{year} years" 
    since += year.years 
  end 

  month = count(since: since, up_to: up_to, measured_in: :month) 
  if month > 0
    ret << "#{month} months" 
    since += month.months 
  end 

  day = count(since: since, up_to: up_to, measured_in: :day) 
  if day > 0
    ret << "#{day} days" 
  end 

  ret.join 
end 

def count(since:, up_to:, measured_in:) 
  raise ArgumentError unless measured_in.in?([:year, :month, :day])
  count = 0 
  count += 1 until (since + count.send(measured_in)) > up_to
  count - 1
end

The idea behind the calculation is that we keep adding the biggest measurement from since until it is past the up_to limit. For example, if you want to calculate how much time has elapsed since 25th December 2017 until 22nd October 2019, the algorithm goes like this:

since = 25-12-2017
up_to = 22-10-2019

# calculate year
count = 0
25-12-2017 + 0.year = 25-12-2017
is not > 22-10-2019, increment count

count = 1
25-12-2017 + 1.year = 25-12-2018
is not > 22-10-2019, increment count

count = 2
25-12-2017 + 2.year = 25-12-2019
is > 22-10-2019, break

count - 1 = 1

result is 1 year

# update since
since += 1.year # since is now 25-12-2018

# count month
count = 0
25-12-2018 + 0.month = 25-12-2018
is not > 22-10-2019, increment count

...
[omitted]
...

count = 10
25-12-2018 + 10.months = 25-10-2019
is > 22-10-2019, break

count - 1 = 9

result is 9 months

this goes on until you get 1 year 9 months 27 days

The calculation in the function can further be detailed until you get to the microseconds (if that’s your thing).

Grandparents & Grandchildren

This is about what I said before. You only need to know who someone’s father and mother are to get to other family members.

In order to get your grandparents, you need to query all fathers and mothers of your father and mother. Ideally, the query produces 4 results. However, we need to take a note that all relationships I mentioned before are optional. You can’t force someone to have father and mother because you’ll hit that limit where you don’t know the father of your father of your father of your father… Since in the example above the person doesn’t have any grandparents, that means in our database his father & mother are what we call roots.

Then there’s your grandchildren. This is not a particularly hard problem because you just need to find all your children, then find the children of those children.

Also note that one of the example person’s children above has his spouse next to his name, that is also derived from the person-father, person-mother, or person-husband relationship.

 

Okay. That was a lot of queries and calculations just to display the information of a family member. But we’re just getting started now.

Family Tree Visualisation

This is where it gets interesting. Before I found the CSS solution (I honestly forgot where I got it from due to a lot of Google searches, but credits to whoever created it), I only showed the family registry in form of a table (with all those rowspans). It was ugly I tell you, but readable. Surely I wasn’t satisfied with it.

Then I stumbled upon the CSS solution for it, tried to adapt my data to it, and made some modifications. I’m posting the scss file later in this section, but let’s walk through it first.

The idea is to create a table recursively, from the roots to the grandchildren. For each family member, we add CSS classes that correspond to their condition (deceased or alive, sex, marriage status, divorced, etc). Then for each condition, we define the CSS styling to generate the tree. You can also add their age next to their name. Let’s go through the recursive method and the member class first:

def recursive_print(members, current_day, sole_parent: nil, root: nil)
  return '' unless members.present?
  
  klass = []
  klass << 'sole-parent' if sole_parent
  klass << 'family-tree' if root

  res = "<ul class='#{klass.join(' ')}'>"
  sole = members.count == 1

  members.each do |member|
    spouse = member.spouse
    klass = []
    klass << 'sole' if sole
    klass << 'married' if spouse

    res += "<li class='#{klass.join(' ')}'>"

    res += "<span class='#{member.css_class}'>"    
    res += link_to member.full_name, member_path(member) # rails routing to get the information page previously explained
    age = age(member.birth_date, current_day)
    age = "#{age(member.birth_date, member.death_date)}/#{age}" if member.deceased?
    res += " (#{age})"
    res += '</span>'

    if spouse
      klass = [spouse.css_class, 'spouse'].join(' ')
      res += "<span class='#{klass}'>"
      res += link_to spouse.full_name, member_path(spouse)
      age = age(spouse.birth_date, current_day)
      age = "#{age(spouse.birth_date, spouse.death_date)}/#{age}" if spouse.deceased?
      res += " (#{age})"
      res += '</span>'
    end

    res += recursive_print(member.children, current_day, sole_parent: spouse.blank?)
    res += '</li>'
  end
  res += '</ul>'
end

# in member.rb
class Member
  has_many :children_as_father, foreign_key: :father_id
  belongs_to :father
  has_many :children_as_mother, foreign_key: :mother_id
  belongs_to :mother
  has_one :wife, foreign_key: :husband_id
  belongs_to :husband

  def children
    sex == 'M' && children_as_father || children_as_mother
  end

  def spouse
    # assume ideal family where there's only one spouse (no second spouse, no stepchildren)
    if sex == 'M'
      children.first&.mother || wife
    else
      children.first&.father || husband
    end
  end

  def deceased?
    death_date.present?
  end

  def css_class
    sex = self.sex == 'M' ? 'male' : 'female'
    status = spouse.present? : 'married' : 'single'
    res = "#{sex}--#{status}"
    res += "#{sex}--deceased" if deceased?
    res.strip
  end
end

WHAT IN THE WORLD WAS THAT?!

Well, with the CSS I adopted and modified, it will create a page that looks just like this:

A family tree of you (maybe)
A family tree of you (maybe)

The idea of the CSS styling is that (pardon wrong CSS terms, not a front end fan):

  • Set the layout so that the nested HTML table is structured to the right, i.e. the lis come in to the right of the uls.
  • Add elements for each ul‘s and li‘s before and after that have borders, they form the lines of the tree. Set the margins appropriately.
  • Modify the different shape of lines for first, last, or sole elements.
  • For the span (the name box), create different colouring and pattern for different member’s CSS class. In the example above, the males are coloured yellow, the females are pink, the married ones are darker, and the deceased ones have diagonal stripes pattern.

When I first adapted the CSS styling to my tree, it was not a really aesthetically pleasing result. So I read up about CSS for a bit, tried to modify the CSS, and with the trials and errors I ended up with what I showed above. I’m pasting the CSS file at the end of the article (because it’s kinda long and you may miss the next section if I post it right here).

Birthday and Death Anniversary Calendar

Since we have everyone’s birth date, it’ll be nice if we can have a calendar that marks the dates where something happens, and that something (for now) can be one of birthday or death anniversary. There’s always other possibilities, say maybe you want to also add wedding anniversary to it, then you’ll only need to modify the database to add a new column and incorporate the change to the displays.

I have two different calendar displays for this need. The first one I built was a monthly calendar that looks like this:

Censored some of my previous test data
Censored some of my previous test data

That calendar is pretty useful if you want a good and quick visualisation of how many events are on a certain day of the month. For each day, it shows who have birthday, what their age are, who died on that age in the past and how long it has been since then. However, you might want a single page containing the whole year, and so I created the second calendar:

I cut the screenshot but the calendar goes up to December
I cut the screenshot but the calendar goes up to December

It shows the whole year, highlights the dates in which there are events, and lists the events below each month. It contains more information per page and is simpler.

I built the calendar visual manually using Ruby. Let’s look at the snippet for the monthly calendar (it’s modified so that it’s purely Ruby, originally it’s a haml file):

@year = params[:year]
@month = params[:month]

@members = Member.all.select { |member| member.birth_date.month == @month }.group_by { |member| member.birth_date.day }
@deceased_members = Member.all.select { |member| member.death_date&.month == @month }.group_by { |member| member.death_date.day }

dt = Date.new(@year, @month)
num_of_days = dt.end_of_month.day # how many days in a month
starting_day = dt.beginning_of_month.wday # find the day of the week of the 1st of the month
row_count = ((num_of_days + starting_day) / 7.0).ceil # find how many rows the calendar for that month contains
current_day = dt.beginning_of_month - starting_day.days # start filling the calendar holes from the top left (regardless of which month that hole belongs to)

row_count.times do
  7.times do
    active = current_day.month == dt.month # check if the current hole is part of the month
    active ||= nil # turn false to nil to utilise Ruby safe navigation operator
    puts current_day.day # print the date
    
    print_alive = active && @members[current_day.day].presence
    print_alive&.each do |member|
      next if member.birth_date > current_day || (member.death_date < current_day rescue nil) # print only if the member has already been born and is still alive 
      puts "(#{age(member.birth_date, current_day)}) #{member.full_name}" 
    end 

    print_deceased = active && @deceased_members[current_day.day].presence 
    print_deceased&.each do |member| 
      next if member.death_date > current_day # print only if the member has died
      puts "(#{age(member.death_date, current_day)}) Death of #{member.full_name}"
    end

    current_day = current_day.next # advance one day to the next hole
  end
end

The code is pretty self-explanatory. First we need to find how many rows the month have, then loop through that count 7 times each (one for each day of the week) and check if there’s any event for that day, then print if there’s any (valid ones only).

The code for the yearly calendar is pretty similar, with more calculations and thinking needed when I built it. The code goes like this:

@year = params[:year]
@members = Member.all.group_by { |member| member.birth_date.strftime("%m%d") } # group members by their MMDD birthday format
@deceased_members = Member.all.group_by { |member| member.death_date&.strftime("%m%d") }

# you can build the calendar in any configuration you want, 4x3, 3x4, 2x6, 6x2, or even 1x12 and 12x1
col = 4
row = 3

row.times do |ii|
  current_day = {} # store current day for each column
  row_count = 4 # minimum row count for each month, to be calculated later
  col.times do |jj|
    month = col * i + j + 1
    # same logic as the previous calendar
    dt = Date.new(@year, month)
    num_of_days = dt.end_of_month.day
    starting_day = dt.beginning_of_month.wday
    current_row_count = ((num_of_days + starting_day) / 7.0).ceil
    if current_row_count > row_count
      row_count = current_row_count
    end
    current_day[jj] = dt.beginning_of_month - starting_day.days
    puts dt.strftime("%B") # print the month name
  end
  
  col.times do
    7.times do |w|
      puts Date::DAYNAMES[w][0..1] # print first two letters of the day name
    end
  end
  
  any_birthday = {}
  any_death_an = {}

  row_count.times do
    (7 * col).times do |c|
      jj = c / 7
      current_month = col * ii + jj + 1
      active = current_day[jj].month == current_month
      if active
        idx = "#{"%02d" % current_month}#{"%02d" % current_day[jj].day}" # convert month & date to MMDD format
        birthday = @members[idx].presence&.any? { |m| m.birth_date <= current_day[jj] && (m.death_date >= current_day[jj] rescue true) # same validation logic as the previous calendar
        death_an = @deceased_members[idx].presence&.any? { |m| m.death_date <= current_day[jj] } 

        eventful = birthday || death_an # highlight flag for different background colour 
        any_birthday[jj] ||= birthday # check list to print for that month 
        any_death_an[jj] ||= death_an 
      end 

      puts (active && current_day[jj].day || '') 
      current_day[jj] = current_day[jj].next # advance to the next day for that month 
    end 
  end 

  col.times do |jj| 
    month = col * ii + jj + 1 
    
    if any_birthday[jj] 
      puts "Birthdays" 
      Date.new(@year, month).end_of_month.day.times do |day| 
        day += 1 # loop for each date 
        current_day = Date.new(@year, month, day) 
        idx = "#{"%02d" % month}#{"%02d" % day}" # convert to MMDD
        @members[idx]&.each do |member| 
          next if member.birth_date > current_day || (member.death_date < current_day rescue nil) # same validation as the previous calendar 
          puts "#{current_day.strftime("%d %B")} - (#{age(member.birth_date, current_day)}) #{member.full_name}" 
        end 
      end 
    end 

    if any_death_an[jj] 
      puts "Death anniversaries" 
      Date.new(@year, month).end_of_month.day.times do |day| 
        day += 1 
        current_day = Date.new(@year, month, day) 
        idx = "#{"%02d" % month}#{"%02d" % day}" 
        @deceased_members[idx]&.each do |member| 
          next if member.death_date > current_day # still the same
          puts "#{current_day.strftime("%d %B")} - (#{age(member.death_date, current_day)}) #{member.full_name}"
      end
    end
  end
end

PHEW. That is all. I hope I covered everything in this long post. Feel free to hit up the comment section.

And of course, the CSS (in scss format):

$box-width: 320px;
$box-height: 30px;
$distance-generation-line: 15px;
$distance-generation: $box-width + $distance-generation-line * 2;
$distance-spouse: 16px;
$buffer: 16px;

$border: #555555;
$male-single: #ffffdd;
$male-married: #ffff77;
$female-single: #ffeeee;
$female-married: #ffbbbb;
$blackish: #777777;
$white: #ffffff;

.family-tree {
  padding-top: 35px;
  padding-bottom: 20px;

  list-style: none;

  &, * {
    margin: 0;
    padding: 0;
  }

  ul {
    padding-left: 0;
    position: relative;
    margin-left: $distance-generation;

    &:before {
      content: "";
      position: absolute;
      left: -($distance-generation-line * 2 + $box-width / 2) + 2px;
      top: 50%;
      border-top: 2px solid $border;
      width: $distance-generation-line + $box-width / 2;
    }

    &.sole-parent:before {
      left: -$distance-generation-line * 2;
      width: $distance-generation-line
    }
  }

  li {
    display: flex;
    flex-direction: row;
    align-items: center;

    list-style: none;
    position: relative;

    min-height: $box-height + $buffer;
    $margin-married: -($box-height + $distance-spouse) / 2;

    &.married {
      min-height: $box-height * 2 + $distance-spouse + $buffer;

      > span:not(.spouse) {
        margin-top: $margin-married;
      }

      &:before {
        margin-top: $margin-married;
      }
    }

    &:before {
      content: "";
      position: absolute;
      left: -$distance-generation-line;
      top: 50%;
      border-top: 2px solid $border;
      width: $distance-generation-line;
    }

    &:after {
      content: "";
      position: absolute;
      left: -$distance-generation-line;
      border-left: 2px solid $border;
    }

    &:first-of-type:after {
      height: 50%;
      bottom: 0;
      width: $distance-generation-line / 2;
      border-radius: $distance-generation-line 0 0 0;
    }

    &:first-of-type.married:not(.sole):after {
      height: calc(50% + 22px);
      bottom: 0;
      width: $distance-generation-line / 2;
      border-radius: $distance-generation-line 0 0 0;
    }

    &:first-of-type:not(.sole):before {
      height: $distance-generation-line / 2;
      border-radius: $distance-generation-line 0 0 0;
    }

    &:last-of-type:not(.married):not(.sole):after {
      height: 50%;
      top: 0;
      width: $distance-generation-line / 2;
      border-radius: 0 0 0 $distance-generation-line;
    }

    &:last-of-type.married:not(.sole):after {
      margin-top: $margin-married + 2px;
      height: 50%;
      top: 0;
      width: $distance-generation-line / 2;
      border-radius: 0 0 0 $distance-generation-line;
    }

    &:last-of-type:not(.sole):before {
      height: $distance-generation-line / 2;
      margin-top: -$distance-generation-line / 2 - 2px;
      border-top: none;
      border-bottom: 2px solid $border;
      border-radius: 0 0 0 $distance-generation-line;
    }

    &:last-of-type.married:not(.sole):before {
      margin-top: $margin-married - ($distance-generation-line / 2);
    }


    &:not(:first-of-type):not(:last-of-type)::after {
      height: 100%;
    }

    &.sole:after {
      height: 0;
    }

    &.sole.married {
      &:before {
        height: -$margin-married;
        border-left: 2px solid $border;
        border-radius: $distance-generation-line 0 0 0;
      }
    }
  }

  > li {
    &:before, &:after {
      display: none;
    }
  }


  span {
    text-align: center;
    min-width: $box-width;
    line-height: $box-height;
    border: 2px solid $border;
    border-radius: 10px;
    position: absolute;
    left: 0;

    &.male--single {
      background: $male-single;
    }

    &.male--married {
      background: $male-married;
    }

    &.male--deceased {
      background: repeating-linear-gradient(
        45deg,
        $male-single,
        $male-married 10px,
        $white 10px,
        $white 20px
      );
    }

    &.female--single {
      background: $female-single;
    }

    &.female--married {
      background: $female-married;
    }

    &.female--deceased {
      background: repeating-linear-gradient(
        45deg,
        $female-single,
        $female-married 10px,
        $white 10px,
        $white 20px
      );
    }

    &.spouse {
      margin-top: ($distance-spouse / 2) + $distance-spouse;

      &:before {
        content: "";
        height: $distance-spouse;
        border-left: 2px solid $border;
        position: absolute;
        left: $box-width / 2;
        top: -$distance-spouse;
      }
    }
  }
}

How I Organise My Photos

I’m a sucker for cloud storage.

I have unlimited storage in Google Drive and I put everything up there in the clouds. Files that consume most of my Google Drive space (more than 1 TB as of now) are photos. I take LOTS of photos. I store the RAW files from my camera in Google Drive too.

I’m a bit obsessed about how my photos are organised too, sadly.

Currently, I’m using both Dropbox and Google Drive to organise my photos. I use Dropbox to upload the photos from my phone automatically. I use Dropbox because it converts the filenames to a format I like, unlike Google Photos (which retains the original filename). However, I still upload my photos–albeit being in ‘High Quality’ size–to Google Photos because it has so many awesome features.

Because I only have 16 GB of Dropbox space, I regularly move the photos to my Google Drive. Not only being in the right cloud, I also want my photos to be organised in such way it’s easy for me to find (and reminisce about) them. So here’s how I do it.

Uploading

For camera photos from my phone and tablet, I use Dropbox’s Camera Upload feature to upload the camera results to Dropbox using WiFi. The photos will then be downloaded to my computer using the Dropbox desktop app.

For DSLR photos, I upload them directly from my computer to Google Drive using Insync.

For other types of photos, I either upload them directly from my computer, or use BitTorrent Sync to upload them from my phone/tablet to my computer (and subsequently be uploaded to Google Drive).

Organising

Here’s the best part.

With a little magic from my programming knowledge I automatically organise all of my photos collection. One thing: I like the filename format provided by Dropbox:

/20[0-9]{2}-[0-9]{2}-[0-9]{2} [0-9]{2}.[0-9]{2}.[0-9]{2}/

It basically names the photo based on its creation date. For example: “2016-07-24 01.28.25.jpg“. So, I want all my photos to be named in that format. Aside from being in that format, I also want them to be organised in directories based on the year and month. So the photo with the filename previously mentioned would be placed in “$SOME_DIR/2016/07“.

Photos from phone/tablet are automatically named as such. However, they’re uploaded to Dropbox! So I created a Ruby script (which runs on my local machine) to move them to the destination directory (which syncs to Google Drive).

require 'fileutils'

def move_file(new_dir, new_file, f)
  unless File.exist?(new_file)
    FileUtils.mkdir_p(new_dir)
    FileUtils.mv(f, new_file)
  end
end

fn = Dir.new("/home/araishikeiwai/$DROPBOX_CAMERA_UPLOADS")
FileUtils.cd(fn)
fn.each do |f|
  if File.file?(f)
    year = f[0..3]
    month = f[5..6]
    new_dir = "/home/araishikeiwai/$GOOGLE_DRIVE_CAMERA_DIR/#{year}/#{month}/"
    new_file = new_dir + f
    move_file(new_dir, new_file, f)
  end
end

I run the script every two hours using Cron. This way, I always have them organised by year and month on Google Drive. Further, if there are certain events, I move the photos for that events and put them in a directory in root photo directory, based on the structure:

$ROOT_PHOTO_DIR/$YEAR/%YY%MM%DD %EVENT_NAME

For DSLR photos, I don’t always convert them all to JPG. Most of the photos from my DSLR camera are RAW. I only convert what I need to share or post on Instagram/500px/Facebook. I use Darktable for my post-processing needs and set up the export filename format as above. And when there are many photos from certain events, I follow the rule above.

Other photos from phone apps, such as Instagram, I created a script to automatically move them and rename them to the same format.

One interesting thing to note is screenshots. I organise all my screenshots in one directory. So the screenshots from my computer (taken using Shutter), phone (QuickMemo+), and tablet (standard iOS screenshot) are all moved and renamed (following the same format, of course) into the same directory. Again, based on year and month.

This seems tedious, with heavy use of internet (downloading from Dropbox and uploading back to Google Drive) and only works when my computer is running (although my computer is almost always running). I tried creating a Google Apps Script to download the photos from Dropbox and upload them directly to Google Drive (bypassing my local computer), however there’s a 10 MB limit on file size, which can be a problem when I have videos to be moved. I’ll create another post on how I utilise Google Apps Script and Dropbox API to migrate files automatically between them.