Hello Again my fellow programmers out there,
你好我的同事们那里的程序员,
I'm designing and programming from scratch a online shop. It has a Module to manage "Orders" that are recieved via the frontend.
我正在从头开始设计和编程在线商店。它有一个模块来管理通过前端收到的“订单”。
I'm needing to have a status to know whats happening with an order in s certain moment, let's say the statuses are:
我需要有一个状态来了解某个时刻发生的订单是什么,让我们说状态是:
- Pending Payment
- Confirmed - Awaiting shipment
- Shipped
- Cancelled
确认 - 等待装运
My question is a simple one, but is very important to decide on the store design, and is: What would you do so store this status: Would you create a column for it in the Orders table or would you just "calculate" the status of each order depending if payments has been recieved or shipments has been made for every order? (except I suppose for a is_cancelled column)
我的问题很简单,但是决定商店设计非常重要,并且:你会做什么存储这个状态:你会在Orders表中为它创建一个列,还是只是“计算”状态每个订单的数量取决于是否已收到付款或已为每个订单发货? (除了我想的is_cancelled列)
What would be the best approach to model this kind of problem?
什么是模拟这类问题的最佳方法?
PD: I even wish in the future to have these statuses configurable buy other clientes using the same software..
PD:我甚至希望将来可以配置这些状态使用相同的软件购买其他客户。
5 个解决方案
#1
If you can calculate it, you should calculate it. Otherwise you have redundant data and run the risk of having inconsitencies.
如果你可以计算它,你应该计算它。否则,您将拥有冗余数据并存在不一致的风险。
That's not to say you can't add a status column for performance reasons or to make querying easier, but start without it and make sure your authoritative data stays that way. Personally I prefer to stick with the calculate-on-query approach unless I can prove the performance isn't good enough.
这并不是说您无法为性能原因添加状态列或使查询更容易,而是在没有它的情况下启动并确保您的权威数据保持这种状态。我个人更喜欢坚持使用查询计算方法,除非我能证明性能不够好。
#2
I would store the status against each line in the order. You need to take into account if someone orders 2 things then cancels one of them.
我会将状态存储在订单中的每一行。你需要考虑是否有人订购2件事然后取消其中一件。
#3
Create a status column for Orders
The rationale is that if Orders can be associated with multiple Payments or Invoices (depending on your implementation), then the status of an Order does not have an absolute relation to any one pending or completed Payment and hence is not directly calculable.
理由是,如果订单可以与多个付款或发票相关联(取决于您的实施),则订单的状态与任何一个待处理或已完成的付款没有绝对关系,因此无法直接计算。
An Order could be marked as Paid or Completed even though no payment has been received for it. An order represents exactly that: a scheduled shipment to a customer. It should not be representative of actual income, which in turn is the domain of your Payments table.
即使没有收到任何付款,订单也可以标记为已付或已完成。订单恰好代表:向客户的预定发货。它不应该代表实际收入,而实际收入又是您的Payments表的域。
Exact implementation will depend on your workflow; I have accounted "completed" orders as "substitutes" for payments before, but it gets messy. Especially when you run promotions or want to give stock away and have to account for lost stock and no income received. Welcome to Accounting Hell.
确切的实施将取决于您的工作流程;我之前已将“已完成”的订单视为付款的“替代品”,但它变得混乱。特别是当你进行促销或想要放弃股票并且必须考虑到丢失的库存而没有收到收入时。欢迎来到会计地狱。
When a Payment is received for an order, your business logic should decide whether to adjust the status of the order. If the order was pending and the total received Payments now add up to equal to or more than the order value, the Order should be marked as Paid, indicating that it is ready to be shipped.
收到订单付款后,您的业务逻辑应决定是否调整订单状态。如果订单处于待处理状态且现在收到的总付款总额等于或大于订单价值,则订单应标记为付费,表示已准备好发货。
To summarise: Completed Payments represent income, whereas Completed Orders represent shipments and/or stock changes.
总结:已完成付款代表收入,而已完成订单代表出货和/或库存变化。
#4
You could do it in two different styles of which I can think at the moment:
你可以用两种不同的风格来做,我现在可以想到:
Option one:
Create a Table for Orders and insert a Column that says 0,1,2 or 3 depending on the amount of statuses is.
为订单创建一个表,并根据状态量插入一个表示0,1,2或3的列。
In another table the primary Keys are ascending from 0 to 3 (as above) and the next colum says: status as a varchar or string, depending on your sql server. There you can insert stuff like 'pending'
在另一个表中,主键从0升到3(如上所述),下一列表示:状态为varchar或字符串,具体取决于您的sql server。在那里你可以插入'待定'之类的东西
Option two:
You store it in an extra colum on each order. Depending on the amout of orders, this is the best solution for small shops. With the amount of orders getting bigger this takes more and more time.
您可以在每个订单的额外列中存储它。根据订单的数量,这是小商店的最佳解决方案。随着订单量越来越大,这需要越来越多的时间。
The reason why the second solution is slower is that in general selecting strings is slower than selecting integer values.
第二种解决方案较慢的原因是通常选择字符串比选择整数值慢。
Greetings
#5
You would have a status_id column in your orders table, then a separate orders_status table with the ids and status descriptions: 'pending, on hold' etc.
您的订单表中会有一个status_id列,然后是一个单独的orders_status表,其中包含ID和状态说明:'待处理,保留'等。
Your payment module upon completion of an order will set the status to pending.
完成订单后,您的付款模块会将状态设置为待处理。
I tend to have two other fields: -
我倾向于有另外两个领域: -
Boolean ispaymentcompleted to indicate whether the order has actually been completed in payment terms and is ready to be processed. This is not an issue if you only save the order details after successful payment.
Boolean ispaymentcompleted指示订单是否已在付款条件中实际完成并且已准备好进行处理。如果您仅在成功付款后保存订单详细信息,则不会出现此问题。
Then I'll have a isneworder boolean field simply to highlight the new orders in the admin system and also to quickly determine new orders that need to be processed.
然后我将有一个isneworder布尔字段,只是为了突出显示管理系统中的新订单,并快速确定需要处理的新订单。
PS. You do need to keep track of order status for an order. Calculating on the fly will lead to less flexibility. And this is standard implementation for all the ecommerce systems I have encountered.
PS。您需要跟踪订单的订单状态。即时计算将导致灵活性降低。这是我遇到的所有电子商务系统的标准实施。
#1
If you can calculate it, you should calculate it. Otherwise you have redundant data and run the risk of having inconsitencies.
如果你可以计算它,你应该计算它。否则,您将拥有冗余数据并存在不一致的风险。
That's not to say you can't add a status column for performance reasons or to make querying easier, but start without it and make sure your authoritative data stays that way. Personally I prefer to stick with the calculate-on-query approach unless I can prove the performance isn't good enough.
这并不是说您无法为性能原因添加状态列或使查询更容易,而是在没有它的情况下启动并确保您的权威数据保持这种状态。我个人更喜欢坚持使用查询计算方法,除非我能证明性能不够好。
#2
I would store the status against each line in the order. You need to take into account if someone orders 2 things then cancels one of them.
我会将状态存储在订单中的每一行。你需要考虑是否有人订购2件事然后取消其中一件。
#3
Create a status column for Orders
The rationale is that if Orders can be associated with multiple Payments or Invoices (depending on your implementation), then the status of an Order does not have an absolute relation to any one pending or completed Payment and hence is not directly calculable.
理由是,如果订单可以与多个付款或发票相关联(取决于您的实施),则订单的状态与任何一个待处理或已完成的付款没有绝对关系,因此无法直接计算。
An Order could be marked as Paid or Completed even though no payment has been received for it. An order represents exactly that: a scheduled shipment to a customer. It should not be representative of actual income, which in turn is the domain of your Payments table.
即使没有收到任何付款,订单也可以标记为已付或已完成。订单恰好代表:向客户的预定发货。它不应该代表实际收入,而实际收入又是您的Payments表的域。
Exact implementation will depend on your workflow; I have accounted "completed" orders as "substitutes" for payments before, but it gets messy. Especially when you run promotions or want to give stock away and have to account for lost stock and no income received. Welcome to Accounting Hell.
确切的实施将取决于您的工作流程;我之前已将“已完成”的订单视为付款的“替代品”,但它变得混乱。特别是当你进行促销或想要放弃股票并且必须考虑到丢失的库存而没有收到收入时。欢迎来到会计地狱。
When a Payment is received for an order, your business logic should decide whether to adjust the status of the order. If the order was pending and the total received Payments now add up to equal to or more than the order value, the Order should be marked as Paid, indicating that it is ready to be shipped.
收到订单付款后,您的业务逻辑应决定是否调整订单状态。如果订单处于待处理状态且现在收到的总付款总额等于或大于订单价值,则订单应标记为付费,表示已准备好发货。
To summarise: Completed Payments represent income, whereas Completed Orders represent shipments and/or stock changes.
总结:已完成付款代表收入,而已完成订单代表出货和/或库存变化。
#4
You could do it in two different styles of which I can think at the moment:
你可以用两种不同的风格来做,我现在可以想到:
Option one:
Create a Table for Orders and insert a Column that says 0,1,2 or 3 depending on the amount of statuses is.
为订单创建一个表,并根据状态量插入一个表示0,1,2或3的列。
In another table the primary Keys are ascending from 0 to 3 (as above) and the next colum says: status as a varchar or string, depending on your sql server. There you can insert stuff like 'pending'
在另一个表中,主键从0升到3(如上所述),下一列表示:状态为varchar或字符串,具体取决于您的sql server。在那里你可以插入'待定'之类的东西
Option two:
You store it in an extra colum on each order. Depending on the amout of orders, this is the best solution for small shops. With the amount of orders getting bigger this takes more and more time.
您可以在每个订单的额外列中存储它。根据订单的数量,这是小商店的最佳解决方案。随着订单量越来越大,这需要越来越多的时间。
The reason why the second solution is slower is that in general selecting strings is slower than selecting integer values.
第二种解决方案较慢的原因是通常选择字符串比选择整数值慢。
Greetings
#5
You would have a status_id column in your orders table, then a separate orders_status table with the ids and status descriptions: 'pending, on hold' etc.
您的订单表中会有一个status_id列,然后是一个单独的orders_status表,其中包含ID和状态说明:'待处理,保留'等。
Your payment module upon completion of an order will set the status to pending.
完成订单后,您的付款模块会将状态设置为待处理。
I tend to have two other fields: -
我倾向于有另外两个领域: -
Boolean ispaymentcompleted to indicate whether the order has actually been completed in payment terms and is ready to be processed. This is not an issue if you only save the order details after successful payment.
Boolean ispaymentcompleted指示订单是否已在付款条件中实际完成并且已准备好进行处理。如果您仅在成功付款后保存订单详细信息,则不会出现此问题。
Then I'll have a isneworder boolean field simply to highlight the new orders in the admin system and also to quickly determine new orders that need to be processed.
然后我将有一个isneworder布尔字段,只是为了突出显示管理系统中的新订单,并快速确定需要处理的新订单。
PS. You do need to keep track of order status for an order. Calculating on the fly will lead to less flexibility. And this is standard implementation for all the ecommerce systems I have encountered.
PS。您需要跟踪订单的订单状态。即时计算将导致灵活性降低。这是我遇到的所有电子商务系统的标准实施。