Living in Crazy Town

DATE: 10/10/2011

FROM: Dan

TO: Development Team Leads

SUBJECT: Idea for new tables going forward

Hi guys,

I got the production DDL for the software we inherited to support from our vendor today, and am thoroughly impressed by the first two tables created in the script:

CREATE TABLE [dbo].[YEARS](

[YEAR] [numeric](4, 0) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[YEAR](

[YEAR] [numeric](4, 0) NULL

) ON [PRIMARY]

I couldn't find any tables with year type columns in them that had a foreign key constraint to the year (or years) tables. That may be due to the year column being nullable in both of the above tables. But I think this is something we can learn from.

For all of our new databases going forward, I think we should have a primary key table for every kind of numeric value we'll be using in our domain. That will allow us to not only maintain constraint integrity, but also reduce the amount of data we'll need to collect. e.g. Instead of a birthdate column that is allowed to stand on its own without any constraints, I think we should create a birthdate table. Then we'll populate it with all of the birthdates possible. Given we'll have a lot of dates possible, I think we should be efficient with the data and only populate rows where a known birthday has occurred. We could take the lazy route and populate every row with a birthdate, but if you're going to do a job, then you should it right.

Of course this also brings up some political and religious ramifications that our project management staff will need to be aware of. If we base the beginning of human history based on current scientific theories, that would bring us back about 2 million years worth of birthdays. Some would argue that 5,000 years worth of birthdates are sufficient. Given that our field of computer science is relatively new, and hardly considered a true science by any stretch of the imagination, I think that 5,000 years is sufficient.

To increase efficiency even further, we could create an algorithm as part of the birthdate population application to heuristically estimate days when there weren't births on the earth. (Sorry, making assumptions that all births were on Earth so far...but hey, let's be Agile about this and iterate non-Earth births in future releases.) That does mean this algorithm definitely needs to be part of the 1.0 release.

Whew - good stuff. I've got a feeling about this project already!