CODEWITHSUNDEEP
sqldatabase
lisovaccaro
lisovaccaro

Reputation: 33966

Best practices to structure a table to hold data per month

When I need to save data for different months in a table. I see two ways to structure tables, either having a column for each month:

Year | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May...

Or a more slim structure by having only a month and an amount column:

Year | Month | Amount

Up to this point I have always used the first approach however I'd like to know if there are advantages of using one method over the other, specially regarding data integrity and performance.

Upvotes: 1

Views: 494

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Under most circumstances, the "slimmer" structure is more appropriate for a relational database. It readily lets you answer questions such as:

  • What is the first month with data?
  • In how many months is the amount more than $100?
  • How often is the amount less than $100?
  • What is the percentage improvement from one month to the next?

The pivoted format is quite appropriate for output purposes, because people are, well, people and we like to see the monthly data spread out. However, putting each month on a separate row is more versatile.

Upvotes: 3

Related Questions