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

This article mainly introduces the common problems of sql design table structure, the article is very detailed, has a certain reference value, interested friends must read it!

How to design the table structure when 1.sql rewrites the business that table fields often have to be added? two。 What is the business problem?

To put it simply, there is a table, and the fields change frequently. How to design the table structure?

Example: gold order form (there are already 29 fields). Due to business development, gold orders have expanded business, there are three expansion business, paygo gold orders, self-operated gold orders, phone charges into gold orders. 4 additional fields (ADDRESS,LONGITUDE,LATITUDE,DEVICE_NO) are required for paygo deposit. The expansion of self-financing requires the addition of 7 fields (COUNTER_NO,BRANCH_NO,BUSINESS_CREATE_TIME,BUSINESS_ORDER_NO,BRANCH_NAME,SERVICE_FEE,ORDER_FEE). A field needs to be added to the deposit order for telephone charges.

I don't know. Have I made myself clear about this? That is, a table often adds fields, and not all the added fields are used.

3. How is the table structure designed?

Let's call it the key-value method. The Ext_key store adds the field name, and ext_value stores the field value.

Advantages and disadvantages of 4.key-value Design

This design brings a lot of flexibility, but writing sql is not easy to use. Design to row transfer column, column transfer row. It is not convenient to maintain and expand.

Let's take a look at the sql of one of the businesses:

It will be more complicated if we count it. Later maintenance will also be difficult.

5. Another kind of table structure design, which is temporarily called extended table.

Add an order extension table 1 to put frequently changed extension fields into the extension table. After this design, it is conceivable that it will become a simple join for order tables and extension tables, and sql will be more refreshing.

6. What are the disadvantages of the extension table?

(1) if the table structure is changed frequently, will the table be locked? Of course. But at present, oracle, including mysql (5. 7), adds table fields and adds fields very fast.

(2) there are a lot of redundant data. It is conceivable that paygo gold input and self-operated gold investment do not use each other's fields, and the corresponding columns are empty data. Empty data does not take up a lot of space, which I think can be ignored.

(3) suppose such a situation, paygo gold order expansion has 10 million data, self-operated gold order expansion has 500 data, telephone fee gold order expansion has 500 data, paygo order expansion table appears to be very redundant. When I encounter this kind of problem, I think it can be split: gold order expansion table, paygo gold order expansion table. When writing sql in this way, it is also a very simple 3-table join.

The above is all the content of the article "what are the common questions about sql designing table structure?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to 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.