My Database design Practice (i)

Round and round, suddenly found that I am now an old driver, has been writing code are very busy, did not put a lot of bits and pieces of the record, today began a series, analysis of the year I contacted or I designed the table structure, there are good and bad, there is joy and tears. A lot of practical experience comes from stepping on one after another, from the perspective of the present, in the design of the time if you do so, maybe I do not need to change the code ...

Welcome you in the comment area discussion, I just want to share the view, perhaps has the superior has the better solution. The following cases are simplified from my practice, individual naming or design should not be named, field names, etc. are also casually written, just for example.

Problem: How a Process-managed module designs table structures

Demand details: Product requirements about this, an order to cancel the return process, the middle of the approval link, everyone through the later, you can cancel the action, all the unsubscribe application will need to have time to record.

Round1

Development questions:

(Here inwardly secretly scold product manager 1000 times, again to engage me, a word demand.) ）

How big is the amount of data? I don't know

What does the audit action need to record? Approver, audit time.

How many people are there in the audit? 3, Sales supervisor, sales Director, finance

Round2

Product looked, it seems that he said the same, no problem. After turning around and asking, can we get a time-consuming order for each cancellation? Mother Egg, two times demand or not to say all the needs at once. This time development needs to help the product manager's brain repair. So

The sponsor requesting the refund is not necessarily a customer, does it need to be recorded?

Even if the audit is completed, the final return action will take time, then the return start and end time actually need another two fields to record?

OK, then add all the time to it. This time the design is not difficult, the difficulty is who knows the product manager to give you miss what key needs ...

Round3

After the product manager retired, I called the sales manager and heard that you have a return request.

Is, is, recently not 315, customer to retreat, then must give back Ah, customer is God ah. Oh yes, we have returned here to the full refund and partial retreat, this can support it?

What the heck? Do I have to charge a fee if it is partially refunded?

Yes, the finance is so fixed, 30 days free return, 30 days after the proportion of the collection.

Mom, I want the product manager and you to refine your requirements.

(Here and the product manager tore the Force 100 times ... ）

Detailed analysis:

Returns have a category of distinction, the return in fact need to follow the operation process, return content, review process, etc. to subdivide the table, through a unified return ID to associate.

The operation process and other records suggest separate, otherwise the expansion of demand will have hidden trouble.

Related table design modifications such as:

Cancellation_info: Return information, considered as the main table

Cancellation_audit: Return approval

Cancellation_product: Return products and details

Notes

1. return products and related information the master-slave table is used here, why this is used or perhaps there are other designs, which do not unfold here, because I have encountered in the actual case.
2. Also saw the return product plus a category to be placed directly into the order table design, so that after the statistical calculation of a product sales total is indeed convenient, and orders to separate 2 sheets such a design is acceptable.

Round4

OK, finally basically fix the sales manager's needs, then give the financial call to confirm the demand.

Hello, I hear we have a return request here and you confirm it.

OK, now the return approval is simplified, I do not need approval here, only need to execute, the sale over there confirmed, calculate the refund amount to me, I only perform the refund.

What the? ......

(The Heart Cries silently)

Question point:

Does the process need to be changed? What is the frequency of change?

Is the audit process a one-way process? For example, the reason for cancellation is not clear, is to perform the return operation, after the same cancellation request can go back to the review process, or must be another process?

Analysis:

Process changes such things are difficult to control, so process and time node records cannot be extended with a horizontal table, and the consequence is that once the process changes, the data table will be changed.

Horizontal tables also cannot handle the process of jumping back and executing repeatedly.

Today's system design, operator operating time and other records need to be more perfect, so can be considered to consider it, plus a variety of note,memo, record a variety of anomalies or notes in case.

The new audit table is no longer a record by Cancellationid, but a cancellationid corresponds to multiple records and has a separate auditid. And each step of the auditor can independently record results and memo, the record will be more detailed, each audit time also has a unified field, after the statistics of a return review of the time-consuming, you can use Cancellationid to retrieve, the maximum minimum time to subtract. In addition, my personal advice is to record the launch time and execution completion time of the return.

Others

In the actual case, there are a lot of details, such as financial need to record vouchers and other things. There's a return address in the sale, and so on.

Sales there 90% will deeds have a current return status of the real-time report, so in cancellation_info add a status flag, etc., convenient for practical application I think it is also possible to consider the design.

Summarize

I think the data sheet Design 2 main speculative direction: one is business-driven, and the other is statistical driven.

Business drivers are saying that business needs require you to record all kinds of data, not to be able to do the relevant functions after recording, and then we record the data according to the patterns of various database designs. Statistical drive is said to meet the basic business process needs, many of the actual application of data is mainly in various statistical reports, to take into account the statistical report of the convenience of data acquisition. In the design, take into account the needs of 2, the case is mainly about business-related drivers, to statistical audit time and other statistics-driven.

Designing multiple tables is actually not difficult for development, it is difficult to do business experience, anticipate possible changes, and plan ahead. A good product manager if you can think of these points early, then development can take a lot less detours. If the product manager can not help, then only on their own, more and the actual business to deal with.

Process records, step records, point-in-time records, recommended not to use landscape design.

My Database design Practice (i)