如何保存，处理订单，ordersDetails模式中的订单总金额? [英] How to save, handle the order total amount in an orders, ordersDetails schema?
问题描述
几个月前我开始设计应用程序数据库模式时，被告知不要在数据库的多个位置存储相同的数据/<计算结果> (规范化).如果这样做，则在一个地方更新数据而在另一个地方不进行更新的情况下会出现一系列错误.所以我做了一个orders表和ordersDetails表.像这样的东西.
When I started designing my application database schema few months ago I have been told not to store the same data/calculated data in more than one place in the database(normalization). If I do, I will make a scope of bugs when I update the data in one place and left the other without updating. So I did an orders table and ordersDetails table. Something like this..
-- orders table
+-----+---------+----------+
| ID | clintID | date |
+-----+---------+----------+
| 1 | 1 |2018-02-22|
| 2 | 1 |2018-02-23|
| 3 | 2 |2018-02-24|
+-----+---------+----------+
-- orderDetail table
+-----+---------+------------+----------+----------+
| ID | orderID | itemNumber | quantity | unitPrice|
+-----+---------+------------+----------+----------+
| 1 | 1 | 12345 | 3 | 100.75 |
| 2 | 1 | 12346 | 3 | 100.75 |
| 3 | 2 | 12347 | 3 | 100.75 |
| 4 | 2 | 12345 | 3 | 100.75 |
| 5 | 3 | 12347 | 3 | 100.75 |
| 6 | 3 | 12345 | 3 | 100.75 |
+-----+---------+------------+----------+----------+
为了使查询更容易，我制作了一个像"allOrdersSummary"的视图
And to make the the queries easier for me I made a view "allOrdersSummary" like
-- allOrdersSummary
SELECT
orders.*, SUM(orderDetail.quantity * orderDetail.unitPrice) totalAmount
FROM orders INNER JOIN orderDetail ON orders.ID = orderDetail.orderID
GROUP BY orders.ID;
and I used this view later for my queries, but now I started to get the MAX_JOIN_SIZE error.
所以我想到了将计算出的总订单金额与订单表ID, clintID, date, totalAmount一起保存，每当我更改orderDeatils表中的某些内容时，我都会更新订单表中的计算出的totalAmount列，我不知道这是好是坏！
So I thought of saving the calculated total order amount along with the orders table ID, clintID, date, totalAmount and whenever I change something in the orderDeatils table I update the calculated totalAmount column in the orders table, I don't know if this is good or bad!
这个问题-我不知道是否被认为是问题-多次遇到，例如，知道发出请求的客户端的未读消息我必须做sum(messages) unread from messages where to = ? and isRead = 0
This problem -I don't know if this is considered a problem or not- is encountered many times, for example to know the unread messages of the client making the request I have to do sum(messages) unread from messages where to = ? and isRead = 0
A)我应该在订单表中为计算的totalAmount作另外一列，还是数据库中的正常情况，每次需要时从orderDetails表中计算totalAmount它吗?
A) should I make another column for calculated totalAmount in the orders table or it is a normal thing in databases to calculate the totalAmount from the orderDetails table every time I need it ?
B)如果建议在订单表中添加另一列，那么每次orderDetails表中发生更改时更新它的最佳方法是什么?每当我更新orderDetails表时，应该在PHP层上对其进行更新，还是需要存储过程?
B) If you recommend making another column in the orders table, what is the best way to update it every time a change happens in the orderDetails table ? should I update it at the PHP layer whenever I update the orderDetails table, or this is something that needs a stored procedure ?
推荐答案
是的，在数据库中存储基于数据库中其他数据的预先计算的值是正常的.但这不一定是您提到的原因.我对MAX_JOIN_SIZE从未遇到过问题.
Yes, it is normal to store pre-calculated values, based on other data in the database, in a database. But not necessarily for the reason you mention. I never had a problem with MAX_JOIN_SIZE.
存储计算值的主要原因(可能也是唯一的原因)是速度.因此，您要针对不经常更改的值执行此操作，并且这些值可能会在使用大量数据的查询中使用，因此如果您不使用它们，则可能会太慢.
The main, and probably only, reason for storing calculated values is speed. So you do it for values that don't change that often and that may be used in queries that use a lot of data and may therefore be too slow if you didn't use them.
例如:如果您想知道数据库中所有订单的平均值，那么如果您已经有了订单总数，查询就会快很多.
For instance: If you want to know the average value of all the orders in your database the query would be a lot faster if you already have the order totals.
为什么以及如何更新值完全取决于您.但是您必须对此保持一致.如果您使用MVC模式，则将其集成到控制器中是有意义的.或简单地说:只要提交的表单可能会更改其中一个值(从中计算出预先计算的值)，您就需要重新计算.
Why, and how, you update the values is completely up to you. However you have got to be consistent about it. If you use the MVC pattern it would make sense to integrate it in the controller. Or in simple terms: Whenever a form is submitted that could change one of the values, out of which the pre-calculated value is computed, you need to recompute it.
这是一个清晰的演示，其中规范化"并未完全得到维持.它不是真的很漂亮，但有时值得.您当然可以辩称，计算出的值代表新"信息，因此不会冒犯规范化".
This is a clear demonstration where 'normalization' is not entirely maintained. It's not really pretty, but sometimes worth it. You could, of course, argue, that the calculated value represents 'new' information, and therefore does not offend against 'normalization'.
这篇关于如何保存，处理订单，ordersDetails模式中的订单总金额?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
