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.
我有一个遗留应用程序,我正在进行更新。数据库中的几个表保存月度信息。这些表基本上每年有一行,每个月有12个字段。我想知道这是否是存储月度数据的最佳方式。每个月都有更好的记录吗?虽然我认为从今年的角度来看会有更多的重复,并且每个月都会附加ID,但这可能是微不足道的。对于一种记录方法来说,编码似乎更容易。并不是说它有很大的不同,但我使用的是PHP / MYSQL。
Is there a best practice for this?
这是最好的做法吗?
6 个解决方案
#1
2
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.
如果您想加入月度数据(例如SELECT total_sales * monthly.tax),那么每月一行就是最佳选择。
#2
2
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,
您是否想要回答诸如“我们在2006年5月到2008年11月之间销售了多少个widgits?在这种情况下,每月记录(假设您存储月份和年份)更容易查询,
#3
2
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
SELECT sum(TotalSold)WHERE MonthColumn在5和7之间
OR
...I don't have that much time... ;-)
......我没那么多时间...... ;-)
#4
1
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.
我认为您应该以更加规范化的方式存储数据 - 每月一条记录。对数据的要求可能会发生变化,但您始终可以通过SQL语句获取必要的数据。
#5
0
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...
更好(更规范化)将有两个表,一个用于年度数据,每年一行,一个用于月度数据,每月一行,以及一年的列...
#6
0
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.
存在一些适合于非规范化的应用,特别是在关系数据集市中。如果企业对执行许多计算有浓厚的兴趣,例如“将1月份销售额超过2月销售额超过10%的项目”,那么非规范化数据集市将是合适的。
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.
您可以逃避这一点,因为数据集市不是维护数据完整性的主要方法,并且不受跨区域更新的影响。
#1
2
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.
如果您想加入月度数据(例如SELECT total_sales * monthly.tax),那么每月一行就是最佳选择。
#2
2
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,
您是否想要回答诸如“我们在2006年5月到2008年11月之间销售了多少个widgits?在这种情况下,每月记录(假设您存储月份和年份)更容易查询,
#3
2
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
SELECT sum(TotalSold)WHERE MonthColumn在5和7之间
OR
...I don't have that much time... ;-)
......我没那么多时间...... ;-)
#4
1
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.
我认为您应该以更加规范化的方式存储数据 - 每月一条记录。对数据的要求可能会发生变化,但您始终可以通过SQL语句获取必要的数据。
#5
0
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...
更好(更规范化)将有两个表,一个用于年度数据,每年一行,一个用于月度数据,每月一行,以及一年的列...
#6
0
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.
存在一些适合于非规范化的应用,特别是在关系数据集市中。如果企业对执行许多计算有浓厚的兴趣,例如“将1月份销售额超过2月销售额超过10%的项目”,那么非规范化数据集市将是合适的。
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.
您可以逃避这一点,因为数据集市不是维护数据完整性的主要方法,并且不受跨区域更新的影响。