简单乘法的计算列？
我制作了一个简单的库存应用程序。在表中，我有“数量”、“价格”和“总价格”列。 将 TotalPrice 设置为数量*价格的计算列 或在我的应用程序中进行此计算并将其保存在 TotalPrice 中哪个更好？
I making a simple inventory application. In a table i have columns Quantity, Price and TotalPrice.
Which is better, make TotalPrice a Computed Column as Quantity*Price or do this calculation in my application and save it in TotalPrice?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
数据库设计中的一条规则（一般来说）是：不要存储也可以轻松检索的信息。
在您的情况下，我不会创建计算列，而是在检索期间进行乘法。通过直接选择
价格 * 数量或创建包含此计算的视图。该视图还有一个额外的好处，即您可以轻松更改公式（例如包括增值税），而无需更改应用程序中的所有报表。
One rule in database design is (in general): do not store information that can also be retrieved easily.
In your case I would not create a computed column, but do the multiplication during retrieval. Either by directly selecting
price * quantityor by creating a view that contains this computation.The view has the added benefit, that you can easily change the formula (e.g. including VAT) without the need to change all statements in your application.
如果您更新价格或数量，您将被迫对 TotalPrice 进行 2cd 更新。如果其他开发者忘记了 2cd 更新怎么办？现在你的数据不好。
您可以使用触发器使其保持同步，但仍然存在出现错误数据的可能性。在某些 RDMS 中，触发器不会针对基于集的更新中更新的每一行触发。
但有时需要存储计算字段。如果表很大并且您计划搜索计算字段。 （您必须存储数据才能为其建立索引）。在某些设计中，您不仅存储计算字段，还存储计算结果。
存储计算字段并不总是错误的，只要确保您知道权衡即可。
* 编辑 *
还有一点。如果您计算“Amount_Paid”，则当您更改计算公式时，客户实际支付的金额将会丢失。
设计是为了解决问题。如果这意味着打破一条经验法则，那就打破一些经验法则。
* 结束编辑 *
If you update price or quantity you are forced to make a 2cd update on TotalPrice. What if another developer forgets the 2cd update? Now you have bad data.
You could have a trigger keep it in sync, but the possibility for bad data still exists. In some RDMS's the triggers do not fire for each row updated in a set-based update.
But sometimes it's needed to store calculated fields. If the table is BIG and you plan on searching the calculated field. (you must store the data to index it). In some designs you not only store the calculated fields, but aggregate calculations as well.
It's not always wrong to store calculated fields, just make sure you know the trade offs.
* EDIT *
Another point. If you calculate "Amount_Paid", the actual amount the customer paid will be lost when you change the calculation formula.
Design to solve the problem. If that means breaking a rule of thumb, then break some thumbs.
* END EDIT *
当您可以在查询中进行计算时，为什么要存储计算列？
why store a computed column when you can do the computations in the query?