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?

It is bad practice to have duplicate data. That could lead to inconsistencies. I advise you to store the date as one column and create a view with the derived columns. That gives you non-redundant columns plus the convenience of pretending that the derived coulmns actually exist (simpler queries).

commented: My thoughts too +1 +15

Good idea!

Thanks!

While I'm certainly not disagreeing about this particular instance, personally I don't think that it's always best practice to never duplicate data in a database. There are many instances in which it's beneficial to denormalize your data, being that CPU resources are much more expensive than hdd space. Sometimes it's the more efficient solution to save yourself from large and resource intensive table joins in favor of duplicating columns across tables. I speak from MySQL experience. Not sure if MS SQL is entirely a different beast entirely.

commented: Fer sher. +14
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.