0

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?

Improve this question
3
  • 1
    You could use a GENERATED/COMPUTED BY/VIRTUAL field or TRIGGERs to ensure that there is consistency. Of course, probably the best thing to do would be to use the various date functions of SQL Server to extract the various data that you require "on the fly".
    – Vérace
    Commented Nov 22, 2016 at 1:54
  • Why not calculate the beginning of the period they want, the day after the end of the period they want, and then always query the datetime column using >= and <? This will eliminate the need for these additional columns and will also be able to use an index on the datetime column. The "pull the date from the table first" doesn't really make much sense, SQL doesn't work that way unless you literally mean run a query that gets ALL the dates, and then use that for a second query, which is not exactly efficient.
    – Aaron Bertrand
    Commented Nov 22, 2016 at 3:49
  • Aaron, The table would have essentially 4 columns: An ID column, BatchDate, a Frequency column that could be either 1, 2, 4 or 12 and a column that is a FK to another table. The Frequency column essentially says how many batches should occur in 1 year for the given FK. When I implied querying twice, I meant, getting the Frequency value in one query, and then computing what the BatchPeriod is, which could be annually, semi-annually, quarterly or monthly and then running another query to pull all the records that match the batch from the other table. Probably makes no sense does it?
    – Zachary Weber
    Commented Nov 22, 2016 at 19:59

1 Answer 1

Reset to default
1

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.

Improve this answer
3
  • I think your alternative approach is more generally called calendar table.
    – Andriy M
    Commented Nov 22, 2016 at 9:29
  • The table I am talking about wouldn't hold financial data, but is more of a "log" or "register" of data. Each row doesn't mean anything without some additional context. One row could represent a batch done in a month, the next for a whole year, the next for a quarter etc... I think I understand what you are talking about, but I don't think your suggestion would work for me specifically. Maybe I didn't provide enough detail in my original question.
    – Zachary Weber
    Commented Nov 22, 2016 at 19:48
  • I my teammate and I devised the approach independently, and I'm sure thousands of other database people haave as welll. It's good to know that it has acquired a common name. Thanks.
    – Walter Mitty
    Commented Nov 22, 2016 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.