Alex Rivera | Logout

How to design Date-of-Birth in DB and ORM for mix of known and unknown date parts

Asked 2011-06-21T21:00:41.170
11

Note up front, my question turns out to be similar to SO question 1668172.


This is a design question that surely must have popped up for others before, yet I couldn't find an answer that fits my situation. I want to record date-of-birth in my application, with several 'levels' of information:

  • NULL value, i.e. DoB is unkown
  • 1950-??-?? Only the DoB year value is known, date/month aren't
  • ????-11-23 Just a month, day, or combination of the two, but without a year
  • 1950-11-23 Full DoB is known

The technologies I'm using for my app are as follows:

  • Asp.NET 4 (C#), probably with MVC
  • Some ORM solution, probably Linq-to-sql or NHibernate's
  • MSSQL Server 2008, at first just Express edition

Possibilities for the SQL bit that crossed my mind so far:

  • 1) Use one nullable varchar column e.g. 1950-11-23, and replace unkowns with 'X's, e.g. XXXX-11-23 or 1950-XX-XX
  • 2) Use three nullable int columns e.g. 1950, 11, and 23
  • 3) Use an INT column for year, plus a datetime column for full known DoBs

For the C# end of this problem I merely got to these two options:

  • A) Use a string property to represent DoB, convert only for view purposes.
  • B) Use a custom(?) struct or class for DoB with three nullable integers
  • C) Use a nullable DateTime alongside a nullable integer for year

The solutions seem to form matched pairs at 1A, 2B or 3C. Of course 1A isn't a nice solution, but it does set a baseline.

Any tips and links are highly appreciated. Well, if they're related, anyhow :)


Edit, abo

Edit
Report

1 Answer

1

Interesting problem...

I like solution 2B over solution 3C because with 3C, it wouldn't be normalized... when you update one of the ints, you'd have to update the DateTime as well or you would be out of sync.

However, when you read the data into your C# end, I'd have a property that would roll up all the ints into a string formatted like you have in solution 1 so that it could easily be displayed.

I'm curious what type of reporting you'll need to do on this data... or if you'll simply be storing and retrieving it from the database.

answered 2011-06-21T21:15:05.963

Your Answer