In all the applications I have made where a database is used I typically store the calculated value along with the variables needed to calculate that value. For example, if I have tonnage and cost I would multiply them to calculate the total. I could just recalculate the value every time it is needed, I was just wondering if there was an standard approach. Either way is fine with me, I just want to do what is most common.
在我使用数据库的所有应用程序中，我通常将计算的值与计算该值所需的变量一起存储。例如，如果我有吨位和成本，我会乘以它们来计算总数。我可以在每次需要时重新计算值，我只是想知道是否有标准方法。无论哪种方式都适合我，我只想做最常见的事情。
If I store the calculate variables it makes my domain classes a bit more complex, but makes my controller logic cleaner, if I don't store the calculated variables it is the other way around.
如果我存储计算变量，它会使我的域类更复杂，但使我的控制器逻辑更清晰，如果我不存储计算的变量，那么反过来。
The calculations would not be extremely frequent, but may be moderately frequent, but math is cheap right?
计算不会非常频繁，但可能适度频繁，但数学便宜吗？
5 个解决方案
#1
8
The standard approach is not to store this kind of calculated values - it breaks normalization.
标准方法不是存储这种计算值 - 它打破了规范化。
There are cases you want to store calculated values, if it takes too long to recalculate, or you are running a data warehouse etc. In your case, you want stick to the normalization rules.
在某些情况下，您希望存储计算值，如果重新计算需要很长时间，或者您正在运行数据仓库等等。在您的情况下，您希望遵守规范化规则。
#2
3
This violates Normal Form to have this calculated value. Unless there is a reason to denormalize (usually performance constraints) then you should make every attempt to normalize your tables, it will make your database much easier to maintain/improve and denormalize may lock you into a design that is difficult to alter easily and exposes your data to inconsistencies and redundancy.
这违反了普通表格以获得此计算值。除非有理由进行非规范化（通常是性能约束），否则你应该尽一切努力规范化表格，它会使你的数据库更容易维护/改进，而非规范化可能会把你锁定在一个难以改变的设计中并暴露出来您的数据不一致和冗余。
#3
1
In my experience, the most common thing to do is to a) store the calculated value, b) without any CHECK constraints in the database that would guarantee that the value is correct.
根据我的经验，最常见的事情是a）存储计算值，b）数据库中没有任何CHECK约束，可以保证值是正确的。
The right thing to do is either
正确的做法是
- don't store the result of the calculation
- 不存储计算结果
- store the calculated value in a column that's validated with a CHECK constraint.
- 将计算值存储在使用CHECK约束验证的列中。
MySQL doesn't support CHECK constraints. So your options are
MySQL不支持CHECK约束。所以你的选择是
- don't store the result of the calculation
- 不存储计算结果
- switch to a dbms that supports CHECK constraints, such as PostgreSQL.
- 切换到支持CHECK约束的dbms，例如PostgreSQL。
#4
1
It all depends on what resources are scarce in your environment. If you do pre-calculate the value, you'll save CPU time at the cost of increased network usage and DB storage space. These days, CPU time is generally much more abundant than network bandwidth and DB storage, so I'm going to guess that as long as the calculation isn't too complicated then pre-calculating the value is not worth it.
这完全取决于您的环境中缺少哪些资源。如果您预先计算了该值，则会节省CPU时间，但会增加网络使用量和数据库存储空间。目前，CPU时间通常比网络带宽和数据库存储更加丰富，所以我猜想只要计算不太复杂，那么预先计算该值是不值得的。
On the other hand, perhaps the value you're calculating takes a substantial amount of CPU. In this case, you may want to cache that value in the DB.
另一方面，您计算的值可能需要大量的CPU。在这种情况下，您可能希望将该值缓存在DB中。
So, it depends on what you have and what you lack.
所以，这取决于你拥有什么，你缺乏什么。
#5
0
Simple math is relatively cheap, however you need to weigh up the additional storage cost vs performance saving when storing these values. Another thing you may want to consider is the affect this will have on data updates, where you cant simply just update the field value, you need to update the calculated value too.
简单的数学运算相对便宜，但是在存储这些值时，您需要权衡额外的存储成本与性能节省。您可能想要考虑的另一件事是这将对数据更新产生影响，您只能更新字段值，您还需要更新计算值。
#1
8
The standard approach is not to store this kind of calculated values - it breaks normalization.
标准方法不是存储这种计算值 - 它打破了规范化。
There are cases you want to store calculated values, if it takes too long to recalculate, or you are running a data warehouse etc. In your case, you want stick to the normalization rules.
在某些情况下，您希望存储计算值，如果重新计算需要很长时间，或者您正在运行数据仓库等等。在您的情况下，您希望遵守规范化规则。
#2
3
This violates Normal Form to have this calculated value. Unless there is a reason to denormalize (usually performance constraints) then you should make every attempt to normalize your tables, it will make your database much easier to maintain/improve and denormalize may lock you into a design that is difficult to alter easily and exposes your data to inconsistencies and redundancy.
这违反了普通表格以获得此计算值。除非有理由进行非规范化（通常是性能约束），否则你应该尽一切努力规范化表格，它会使你的数据库更容易维护/改进，而非规范化可能会把你锁定在一个难以改变的设计中并暴露出来您的数据不一致和冗余。
#3
1
In my experience, the most common thing to do is to a) store the calculated value, b) without any CHECK constraints in the database that would guarantee that the value is correct.
根据我的经验，最常见的事情是a）存储计算值，b）数据库中没有任何CHECK约束，可以保证值是正确的。
The right thing to do is either
正确的做法是
- don't store the result of the calculation
- 不存储计算结果
- store the calculated value in a column that's validated with a CHECK constraint.
- 将计算值存储在使用CHECK约束验证的列中。
MySQL doesn't support CHECK constraints. So your options are
MySQL不支持CHECK约束。所以你的选择是
- don't store the result of the calculation
- 不存储计算结果
- switch to a dbms that supports CHECK constraints, such as PostgreSQL.
- 切换到支持CHECK约束的dbms，例如PostgreSQL。
#4
1
It all depends on what resources are scarce in your environment. If you do pre-calculate the value, you'll save CPU time at the cost of increased network usage and DB storage space. These days, CPU time is generally much more abundant than network bandwidth and DB storage, so I'm going to guess that as long as the calculation isn't too complicated then pre-calculating the value is not worth it.
这完全取决于您的环境中缺少哪些资源。如果您预先计算了该值，则会节省CPU时间，但会增加网络使用量和数据库存储空间。目前，CPU时间通常比网络带宽和数据库存储更加丰富，所以我猜想只要计算不太复杂，那么预先计算该值是不值得的。
On the other hand, perhaps the value you're calculating takes a substantial amount of CPU. In this case, you may want to cache that value in the DB.
另一方面，您计算的值可能需要大量的CPU。在这种情况下，您可能希望将该值缓存在DB中。
So, it depends on what you have and what you lack.
所以，这取决于你拥有什么，你缺乏什么。
#5
0
Simple math is relatively cheap, however you need to weigh up the additional storage cost vs performance saving when storing these values. Another thing you may want to consider is the affect this will have on data updates, where you cant simply just update the field value, you need to update the calculated value too.
简单的数学运算相对便宜，但是在存储这些值时，您需要权衡额外的存储成本与性能节省。您可能想要考虑的另一件事是这将对数据更新产生影响，您只能更新字段值，您还需要更新计算值。