I am working on a table that will hold batch data.
The table will have a BatchDate column as a DateTime type. I'm considering adding a column for BatchYear as an integer and BatchPeriod (which could be a week number, a month number or a quarter number) as an integer, even though both of these values can be derived from the BatchDate and using another column in the table.
I'm considering doing this because I'm thinking it would be easier to work with queries where someone doesn't have to pull the date from the table first, compute the period they want and then query the table again. Also, if someone is looking for a batch from the 3rd quarter of 2012, the server would have to convert each BatchDate to it's quarter equivalent and do the same for the year and then test for a match, but if the values are there in separate columns, one could simply query the BatchPeriod and the BatchYear column for a match.
Is this a bad idea to break normal form this way?
Best Answer
It is against either 2nd or 3rd normal form, depending on whether the datetime column is part of the key.
You could be harmed by an update anomaly if you change a batch date, but forget to change one of the fields that are derived from it. Now the database is self cntradictory.
When I was faced with this, I adopted an alternative approach. I created a table called almanac, with a date type key, and all those other fields like weekday, fiscal quarter, and so on. I then wrote a program to fill this table with ten years worth of dates. Then, whenever I wanted all those fields with respect to any date, I just did a join. This was especially useful because fiscal quarter was a company defined attribute, and wasn't in any built in function. We had dozens of date fields, and reporting by fiscal quarter was a requirement.