I find this method much easier to manage and cleaner to manipulate the different fields while having the same benefits of multiple fields (except for null values which was not important). On top of that, I only need two fields to manage instead of three fields and still have the flexibility to expand to include times if needed.
The first field is a date column. For unknown parts, any value will suffice. For example, if you knew someone started at the company 10 years ago, that person started in 2006. Without knowing the date and month, I just enter into the system 1/1/2006 (this can be 2/4/2006, 10/29/2006, etc.).
The second field is a flag field to let me know which parts of the date is unknown. I have designated that the column as a binary to minimize the size as I do not really need that many:
1 = day
2 = month
4 = year
8 = decade
16 = century
32 = millennium
If I do not know the day (for example someone's birthday is in March 2016), the date would be 3/1/2016 with a flag of 1. For the company start date example, the date would be 1/1/2006 with a flag of 3. 3 in binary would be 11 designated both day and month.
Unfortunately, I didn't think this completely through so the logic kind of changes for decade, century, and millennium. Here, I use these flags when year is 0 otherwise it overrides the remaining values because unknown year would also mean unknown decade, century, and millennium.
If I had time to redo the logic, the each flag could be used for each digit of the year. For example:
4 = 201X
8 = 20X6
16 = 2X16
32 = X016
The reason I think this is cleaner is because I would have all the flexibility to manipulate the year without losing any benefits of the current method. I have not switched because the flexibility is not great enough for me to update all the data and code because data degradation is consistent from smaller value to larger value. In other words, if the month is unknown, the day is also unknown. It is rare that the day is known without the month. It can happen but I have not had a need for partial years.
Most importantly for the class is the ToString() function. This saves me a lot of time of having to display partial dates. Currently, I just display the missing date as ?? (ie. 02/??/2016, 02/16/????, ??/??/2016, etc.). This could be expanded with more date formats.
No comments:
Post a Comment