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

Viewed 815

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, about the answers: I marked one answer as accepted, because I think it will work for me. It's worth looking at the other answers too though, if you've stumbled here with the same question.

6 Answers
Related