Shulou(Shulou.com)05/31 Report--

What this article shares with you is the thinking and practice on how to design the "status" field of the database. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Text

Recently, in the system related to order making and payment, in the design stage of the order table, team members have some differences on the design of the "order status" database field, and there are also a lot of thoughts and discussions on this aspect on the Internet. combined with these materials and the actual situation of the project, I intend to think deeply about some common problems.

1. Summary of problems

The divergence points here are not only the differences within the team, but also some common differences on the network. First, throw out the existing differences:

1) what status values should be included in the dictionary values corresponding to the 'order status' field of the order table? Do you put the status of 'commented', 'returned' and 'refunded' in the 'order status'? Or is it a separate field identification?

2) how to represent the dictionary value corresponding to the 'order status' field of the order table? Options are: use digital identification, use multi-bit storage identification, and use English string identification with clear business meaning.

3) what type is used in the 'order status' field of the order table? Options are: number (N), char (N), varchar2 (N)

If you think the analysis process is too verbose, you can go straight to the conclusion.

two。 Business analysis

Instead of looking at the problem, let's take a look at what the business is like with the 'Order' entity. Below we will analyze in detail the possibility that the behavior that may change the state of the order entity has changed.

The business processes related to an order business entity are as follows: placing an order (create)-> buyer payment (pay)-> seller delivery (deliver)-> buyer receipt (receive)-> return (rereturn); in addition, there are refunds (refund) and comments (comment), which are special and may have multiple forward behaviors.

First of all, you can change the order business status (the status here does not refer to the database field of 'order status' (OrderState), but refers to the actual business status, which we abbreviated as (BizState) to distinguish from OrderState). According to the business process of typical e-commerce, the main actions (action) are: placing order, payment, delivery, receiving, refund / return, comment. The occurrence of each behavior will lead to a change in the BizState of the business status of the order. For example, the 'place order' behavior will create the order, the 'payment' behavior will make the order become 'paid', the 'delivery' behavior can make the order status become 'shipped', the 'receiving' behavior will make the order status become 'received', and the 'comment' behavior will make the order status become 'commented'. Refund / return action is not supported on all orders and will not be considered for the time being to reduce complexity.

Secondly, under the subdivision of the impact of each action on BizState, we will find that it can also be subdivided into four sub-states (subState): action not started (marked as 0), action in progress (marked as 1), action success (marked as 2), action failure (marked as 3); theoretically, all subState of all action are arranged to get 4 "4" 4 "4" 4 "1024 (return is not considered for the time being). In fact, many combinations have no business significance and are impossible to exist, such as' payment has not started.'(* 20) this kind of combination is impossible and should be abandoned. Use the table to analyze the above combinations as follows:

From the above table, we can find some rules:

The first four action of 'order', 'payment', 'delivery' and 'receipt' are dependent, that is, the latter action depends on the completion of the previous action; therefore, their SubState combination will be very rare.

'There are many possibilities for commenting on comment', a combination of SubState and other states of action, except that the first two lines are 'Xrays, followed by'?' Or 'Yizhen, last minute,' It refers to whether comments are allowed on the corresponding BizState in terms of requirements, and if so, there are 4 more possibilities for each BizState, so the possibility of combination becomes very large.

The SubState portfolio that has no business significance is discarded. The marked black cell in the table indicates that the BizState is meaningless, because the order for "unplaced order" does not exist for us, and this kind of combination needs to be abandoned; similarly, there are many other combinations that do not exist, are abandoned, and are not shown in the above table, such as' orders have been placed, orders have been paid, and goods have been received.

Usually, when the SubState of an action is'1 in progress'or'3 failed', it will be ignored, but there are exceptions, such as the'3 failure 'status of' payment 'action and the' 1 in progress' status of 'payment' action. For more information, see below.

Ignore the'0 not started 'SubState status of all action. Because this type of SubState does not make a difference to BizState.

To sum up, we get the BizState in the above table. Note that the Comment action here is not refined. If you refine it, you will find that BizState is much more likely.

Next, we will discuss these issues one by one.

3. Question 1. What status values should be included in the 'order status' field of the order table?

What kind of 'order business status' (BizState) needs to be recorded in the 'order status' (OrderState) field at the system level? If there are more records, the complexity of the system processing will increase; if there are fewer records, then the 'order status' (OrderState) field can not fully represent the changes in the status of the order entity.

Core state

From the above business analysis, we can see that most of the action with dependencies (create, pay, deliver, receive) produce very few reasonable SubState combinations, and the dependency between them is one-way, and the processing of the state machine is very simple, so let's first incorporate this part of BizState into the OrderState:

Waiting for the buyer to pay

The buyer paid successfully.

The seller has delivered the goods.

The buyer has received the goods

Current order status flow:

The failure of 'action behavior'

For the processing that the SubState of action is'3 failure', it needs to be analyzed for different action. For an action such as' place an order Create', if it fails, you can directly set the OrderState to 'order creation failure', because Create action is * action, its failure means that the Order entity is born and dead, and the BizState is set to the final state. This BizState should be included in the OrderState record, but this OrderState is not of much use to the user, because the user does not care about the failed order, he is more concerned about reissuing the order

For 'payment' failure, it depends on the demand. If the demand requires the user to continue to pay, the order needs to be retained, and the status is still 'waiting for payment by the buyer'. If no further payment is allowed, then theoretically, BizState can be set to the final state of 'payment failure'. Therefore, the BizState final state of 'payment failure' should also be recorded in the OrderState field.

For 'delivery' failure, 'receiving' failure, usually will not happen, even if it does not belong to the scope of the system control, system records are meaningless, a more constructive approach is to solve the problem as soon as possible through offline means, re-delivery and so on, so the OrderState field of these status systems is not recorded.

In this way, our OrderState dictionary value is increased to 6, and the bold item is added:

Failed to create order (final)

Waiting for the buyer to pay

Failure of payment by buyer (final, depending on demand)

The buyer paid successfully.

The seller has delivered the goods.

The buyer has received the goods

Current order status flow:

The situation of action behavior in progress

For the processing that the SubState of action is'1 in progress', it also requires specific analysis of specific scenarios. The behavior of 'payment' is initiated by the user, but it is not the interaction with the order system, it involves the processing of the payment system, and this field is not controllable by the order system, but it is related to money and the user compares the relationship, so for such an intermediate state, we need to record so that the user can query the order status through the order system. In order to facilitate the user's understanding, this status is recorded as' payment confirmation'in OrderState The situation of 'delivery' and 'receiving' is not an area that can be controlled by the order system. We can treat them as' not started', for example, 'delivery in progress'. The OrderState value of the order system is "buyer has paid", but the prompt to the user is "buyer has paid, waiting for seller to deliver goods". In fact, the seller may be delivering goods at this time. But users don't care about whether the goods are packed or not, so this kind of'in progress' state can be abandoned. In this way, a dictionary value is added to the OrderState field of the order system: 'payment confirmation':

Failed to create order (final)

Waiting for the buyer to pay

Payment confirmation in progress

Failure of payment by buyer (final, depending on demand)

The buyer paid successfully.

The seller has delivered the goods.

The buyer has received the goods

Current order status flow:

A situation in which the 'action behavior' has not started

Ignore the'0 not started 'SubState status of all action. Because this type of SubState does not make a difference to BizState.

'comment on the handling of comment'

*, let's take a look at the comment on the action of comment'. If the demand requires that the 'comment' operation can be initiated only after the buyer has received the goods, then the task 'comment comment'' can be one-way dependent on the 'receive receipt' behavior. Then a small amount of BizState corresponding to the subState of this action (which should be only 'buyer commented' and 'seller commented' status) can be included in the unified record of the OrderState field. But if the demand is: the buyer can start to comment after placing the order, for example, if the seller delivers goods slowly, the buyer can go up and complain, then 'comment comment'' does not one-way rely on 'receive receiving' behavior, but more depends on 'pay payment', 'deliver shipping' and 'receive receiving', then the possibility of subState combination of these actions will soar, and the dictionary value of BizState will also increase sharply, obviously. Instead of handing over so much BizState to OrderState to record, there should be a separate database field responsible for recording the SubState of the comment comment', which we can name.

For 'CommentState' (comment status), its dictionary value is not much, only:' not commented', 'buyer commented', 'seller commented' In fact, for the former requirement, it is not necessary to include the BizState generated by the SubState corresponding to the comment comment' into the OrderState, because users are not so concerned about the comment or not, that is to say, the comment comment' is not the core business process. In order to reduce the system processing complexity of the core business process, it is better to separate it from the core business process.

To sum up, we should record the BizState corresponding to the 'comment comment'' in a single field.

'The handling of return rereturn'

Let's take a look at the processing of the BizState corresponding to the return rereturn' behavior. Return rereturn' is not a common experience for all orders, but once it is involved, the return rereturn' must be one-way dependent on 'receive receipt' in the business process, so the BizState generated by the return rereturn' ('returning goods', 'return success', 'refund failure' and 'not returned goods' are ignored, as explained above) should be recorded in OrderState. In this way, our OrderState has two more dictionary values. Here we do not consider the situation where there are multiple items in an order, so we treat the "return success" as the final state. If it is an order with multiple goods, we need to re-analyze it carefully. Bold item is added:

Failed to create order (final)

Waiting for the buyer to pay

Payment confirmation in progress

Failure of payment by buyer (final, depending on demand)

The buyer paid successfully.

The seller has delivered the goods.

The buyer has received the goods

Returning goods

Return successful (final state)

Current order status flow:

'The processing of refund refund'

* Let's take a look at the processing of the BizState corresponding to the refund refund' behavior. First of all, we need to know that "return" and "refund" are two different business behaviors. Their relationship is: generally speaking, "return" will inevitably lead to "refund". But "refund" can not be involved in "return" (special cases are not discussed here, for example, for virtual goods, successful payment usually means successful receipt, so it can only be caused by "return"). For example, e-commerce allows users to initiate a 'refund' before receiving the goods after successful payment. In other words, 'refund refund' is not one-way dependent on' return rereturn', is as many dependencies as' comment comment''. Therefore, we can refer to the handling of 'comment comment'' and create a separate field 'RefundState refund status' to record the BizState generated by the refund refund'. The dictionary values of this status field are as follows: the refund is successful.

Consider other circumstances

In addition, there may be some enhanced requirements to make the customer experience better, for example, the user can cancel the order before payment is made after the order is created, or the system runs the batch to close the order that the user has not paid for a long time, which will lead to a new action--'close shutdown', which will lead to a new meaningful BizState--' order closure / cancellation, which is not part of the core process and is not entangled. It will not be discussed in detail and is listed as follows:

Failed to create order (final)

Waiting for the buyer to pay

Payment confirmation in progress

Failure of payment by buyer (final, depending on demand)

The buyer paid successfully.

The seller has delivered the goods.

The buyer has received the goods

Returning goods

Return successful (final state)

Order closed (final)

Conclusion

To sum up, we can arrive at the criteria for putting the 'order status' field in the database: core business processes, forward one-way dependency. The extension to other business entities is the same, and the 'order status' field here actually refers to the main business status field of the data table corresponding to that business entity. Let's extend the conclusion:

If an action belongs to the core business process corresponding to a business entity, and the action is one-way dependent on its forward action, the BizState generated by the action needs to be recorded in the main status field of the database table corresponding to the business entity.

There are 10 BizState business states recorded in the OrderState field, of which 4 are final states and the rest are intermediate states. The flow relationships of these states are:

4. Question 2. What is the representation of the dictionary value of the 'order status' field of the order table?

First, list the options: use digital identification, use multi-bit storage identification, use English string identification with clear business meaning; explain the options one by one:

A, use digital identification-use a number to identify a status that is not required to be sequence; for example, 'waiting for payment from the buyer' means'0'.

B. use multi-bit storage to identify whether a behavior has occurred or not. For example, 'whether to pay' is defined in * bit, 'whether to ship' is defined in the second place, 'whether to receive goods' is defined in the third place, and 'whether to comment' is defined in the fourth place, then the status' seller has received goods without comment 'can be expressed as: 0111; while' waiting for buyer's payment'is expressed as' 0000'. Of course, the 'bits' here may be binary or N-ary, which we will discuss in detail later.

C. Use English string identification with clear business meaning-this scheme is similar to scheme a, but the dictionary value becomes an English payment string with clear business meaning, such as' waiting for payment by the buyer'as' WAIT_BUYER_PAY''

Scheme an is the usual way of the database field dictionary, which is simple and intuitive, but there is a disadvantage: when the dictionary value is large, the users of the database table can not remember the meaning of the dictionary and need to look up the information repeatedly to confirm it; some people will say that writing the dictionary value into the comments of the field is not very reliable in practice. usually after the table is established, if the field adds the dictionary value, the developer will ignore changing the dictionary value. And when using a tool such as pl/sql to query the database, all dictionary values are not displayed.

Through the analysis of question 1, it is known that the use of multi-bit storage mode in scheme b will increase the complexity, which is not necessary, and can be expressed by independent 'comment' status into a field.

Scenario c is similar to scheme a, with the advantage of knowing the business meaning directly through the dictionary value, but the disadvantage is that it will bring complexity to coding and manual query, and people usually can't remember the English dictionary of "waiting for payment by the buyer".

I was confused when WAIT_BUYER_PAY', manually wrote the sql query 'waiting for the buyer to pay'.

After compromise, we combine scheme an and scheme c, and get scheme d: another dictionary table is established, which stores dictionary values in digital form, dictionary English names, dictionary abbreviations and dictionary interpretations; the OrderState field of the order entity table uses numbers as dictionary values.

For scheme d, when you see the numerical form status of OrderState, you can first see whether the field comments have the definition of this dictionary. If not, look up the dictionary table to get the dictionary value and meaning; it will also become easier in coding and manual sql queries, with fewer digits after all. Other benefits of establishing a dictionary table are that the dictionary explanation can be written in detail, and when the Chinese name of the dictionary is required to be displayed in the report, it can also be queried directly from the database join table without having to do an additional mapping. (for reference: database table design (status field))

So is it necessary to create an additional dictionary table for status fields with a small number of dictionaries? This is considered according to the actual situation, it is usually not necessary to build it, and it is not too late to create a subsequent business scenario.

For the status of Syslog / run batch record tables, etc., of non-business entity tables, it is entirely possible to use digital dictionaries, because these dictionary values are usually not used in business scenarios, and these dictionary ranges should be relatively small. so there is no need to create separate dictionary tables for them.

To sum up, it is concluded that:

1), the business status field of the business entity table that will be used in business scenarios such as more dictionary ranges, changes, reports, etc., is processed by the scheme of "scheme d: new dictionary table". For example, the "order status" field in the "order business entity table".

2), the business status field of the business entity table that is not used in business scenarios such as fewer dictionary ranges, fewer changes, reports, etc., is processed by the scheme of 'solution a: using a digital identification dictionary'. For example, the 'payment pipeline status' field of Alipay's payment pipeline table.

3), the status field of the system log / run batch record table, which is processed by the scheme of "scheme a: use the digital identification dictionary", such as the "run batch status" field of the goods to be received record table.

5. Question 3. What type of status field is used in the database table?

Lists the options: number (N), char (N), varchar2 (N), where N is a length value.

This problem mainly needs to consider usage scenarios, scalability, performance, and storage.

The 'status' field is mainly used in query scenarios, and the query is usually'='or 'in'', and there is no interval query, so there is little difference between the three.

For performance, refer to [original] as primary keys in Oracle 10g number, Char and Varchar2 types, and query efficiency analysis shows that the performance of char (N) and varchar2 (N) is better than that of number (N), so number (N) is discarded.

Considering scalability, char (N) and varchar2 (N) are similar.

Considering the storage, varchar2 takes up less space, so choose varchar2 (N).

To sum up: select varchar2 (N) as the type of database 'status' field.

6. Summary of problem conclusions

1) what status values should be included in the dictionary values corresponding to the 'order status' field of the order table? For the status of 'commented' and 'returned', is it put in the 'order status'? Or is it a separate field identification?

If an action (behavior, such as payment) belongs to the core business process corresponding to a business entity, and the action is one-way dependent on its forward action, the business state generated by the action needs to be recorded in the main status field of the database table corresponding to the business entity.

The 'commented' action in the question is generated by the 'commented' behavior, which is not the core business process of the order business entity, and there may be multiple forward dependent action (payment, delivery, receipt, etc.), so it should be identified independently to a field.

The "returned goods" in the question is generated by the "return" behavior, and the "return" action is the core business process of the order business entity. Users are very concerned about it and only rely on the "receiving" action, so it should be recorded in the "order status" field of the physical table of the order business.

The 'refunded' in the question is generated by the 'refund' behavior, and the action of 'refund' is the core business process of the order business entity, which users are very concerned about, but this action has multiple forward dependencies on action (payment, delivery, receipt, etc.), so it should be identified independently to a field.

2) how to represent the dictionary value corresponding to the 'order status' field of the order table? Options are: use digital identification, use multi-bit storage identification, and use English string identification with clear business meaning.

I. The business status field of the business entity table that will be used in business scenarios such as more dictionary ranges, changes, reports, etc., is processed by the scheme of "scheme d: new dictionary table", such as the "order status" field in the "order business entity table".

J, the business status field of the business entity table that is not used in business scenarios such as fewer dictionary ranges, fewer changes, reports, etc., is processed by the scheme of "scheme a: use digital identification dictionary". For example, the "payment pipeline status" field of Alipay's payment pipeline table.

K, the status field of the system log / run batch record table, which is processed by the scheme of "scheme a: use digital identification dictionary", such as the "run batch status" field of the goods to be received record table.

3) what type is used in the 'order status' field of the order table? Options are: number (N), char (N), varchar2 (N)

Varchar2 (N) takes up less storage and has the same performance and scalability, so varchar2 (N) is selected as the type of database 'status' field.

7. references

Database table design (status field)

[original] query efficiency analysis in Oracle 10g Magi number, Char and Varchar2 types as primary keys

The above is the thinking and practice of how to design the "status" field of the database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.