Best way to store monthly data in a database?

Question

I have an outdated application in which I am making updates. Several tables in the database contain monthly information. Tables basically have one row per year and 12 fields for each month. I was wondering if this was the best way to store monthly data. Is it better to have a record for every month? Although I assume that from the point of view of the year there will be much more duplication, and the identifier is tied to each month, but this can be insignificant. Encoding seems to be easier for a single write method. Not that it matters, but I'm using PHP / MYSQL.

Is there any best practice for this?

Answer 1

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

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

If you want to join the monthly data (for example, SELECT total_sales * month.tax), then one step per month is the way to go.

Answer 2

You want to answer questions such as “How many widgets did we sell from May 2006 to November 2008? In this case, monthly records (provided that you keep the month and year) are much easier to request,

Answer 3

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

Would you prefer:

INSERT row every month

OR

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

Would you prefer:

SELECT sum (TotalSold) WHERE MonthColumn between 5 and 7

OR

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

Answer 4

I think that you should store data in a more standard way - one record per month. Data requirements can be changed, but you can always get the necessary data using SQL statements.

Answer 5

Better (more normalized) will have two tables: one for annual data with one row per year and one for monthly data with one row per month and a column for the year ...

Answer 6

I would always go for a normalized design for a month, except ...

There are several applications where it is advisable to denormalize, in particular, in a relational data array. If a business has a keen interest in performing many calculations, for example, “if goods sold in January exceed sales in February by more than 10%,” then a denormalized data file would be appropriate.

You can get rid of this because the data packet is not the main method of maintaining data integrity and is not subject to transplant updates.