CODEWITHSUNDEEP
database-design
Lee
Lee

Reputation:

Best method for storing monthly data in a database?

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?

Upvotes: 5

Views: 7641

Answers (6)

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 0

Chris Nava
Chris Nava

Reputation: 6802

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... ;-)

Upvotes: 3

Michael Baranov
Michael Baranov

Reputation: 819

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.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96600

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,

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146541

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...

Upvotes: 0

Greg
Greg

Reputation: 321756

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.

Upvotes: 2

Related Questions