Best method for storing monthly data in a database?

Question

I have a legacy application that I am making updates on. A couple of the tables in the database hold monthly information. The tables basically have one row per year and 12 fields for each month. I was wondering whether or not this was the best way to store monthly data. Is it better to have a record for each month? Although I suppose there will be a lot more duplication from the standpoint of the year and id being attached to every single month, but this may be negligible. It seems like the coding is easier for the one record method. Not that it makes a difference much but i'm using PHP/MYSQL.

Is there a best practice for this?

Answer 1

I think it depends what you want to do with the data.

If you always want to pull back the data for a whole year then a field per month makes sense.

If you want to join onto the monthly data (e.g. SELECT total_sales * monthly.tax) then one-row-per-month is the way to go.

Answer 2

Better (more normalized) would be to have two tables, one for the yearly data, with one row per year, and one for the monthly data with one row per month, and a column for the year...

Answer 3

Are you going to want to answer questions such as "How many widgits did we sell between May 2006 and November 2008? In this case the monthly records (assuming you store month and year) are far, far easier to query,

Answer 4

I think that you should store data in more normalized way- one record per month. Requirements to data could changed but you always will be able to get necessary data by SQL statements.

Answer 5

When you consider the creation and use of the data Monthly records make more sense.

Would you rather:

INSERT a row each month

OR

UPDATE a row and write code to figure out which column to SET?

Would you rather:

SELECT sum(TotalSold) WHERE MonthColumn between 5 and 7

OR

...I don't have that much time... ;-)

Answer 6

I would always go for the normalised month-per-row design, except ...

There are some applications where it is appropriate to denormalise, in particular in a relational data mart. If a business has a strong interest in performing many calculations such as "fnd the items where january sales exceeded february sales by more than 10%" then a denormalised data mart would be appropriate.

You can get away with this because the data mart is not the primary method of maintaining data integrity, and is not subject to transactonal updates.