My first thought was to create columns for each part of a date DOBYear
, DOBMonth
, DOBDay
, and so on, where if the date of birth is only known to the year then month and day and so on contain a null marker. Yes, I know this could be considered a violation of 1NF.
A bit of playing around convinces me that it causes the constraints to make sure no-one put a null marker in DOBMonth and a value in DOBDay get a bit insane.
After a bit of thought, I'd go with what you have. I wouldn't care too much about validation of the data stored, I would limit the data returned by means of a case expression, something like the following (typed off the top of my head so untested).
select
case date_of_birth_precision
when 'years' then datename( year, date_of_birth )
when 'months' then datename( month, date_of_birth ) + ", " + datename( year, date_of_birth )
when 'days' then datename( month, date_of_birth ) + " " + datename( month, date_of_birth ) + ", " + datename( year, date_of_birth )
…
end as known_date_of_birth
Thus if the precision is "years" then the actual date of birth is "1985". If the precision is "months" then "March, 1985". If "days" then "25 March, 1985". And so on.
Something to consider is whether it is an error to have a precision column of "months" with higher precision actually specified? That is, is ("1965.04.25 5:43:28", "months") valid or does it have to be entered as ("1965.04.01 00:00:00", "months")? If the first answer is invalid then the check constraints get a bit lengthy.
As an aside, if this particular situation happened in many of the entities in the system and was used in a large number of select statements then I would consider creating a user-defined type (if using SQL server then it would be a CLR user-defined type) to avoid having to enter the same case expression over and over.