Two database design instances

(Note: From http://blog.163.com/jiang-640/blog/static/86403594200932994637923)

1. Data Tables with tree relationships

Many programmers have encountered tree-based data during database design. For example, a common category table, that is, a large category, contains several sub-classes, some sub-classes have sub-classes. When the category is not determined, you want to add a new subclass under any category, or delete a category and all its subclasses, and the number of them is expected to increase gradually in the future, in this case, we will consider using a data table to save the data. According to the textbooks, the second type of programmers will probably design a data table structure similar to this:

 

Category Table _ 1 (type_table_1)
Description of name type constraints
Type_id int no duplicate category ID, primary key
Type_name char (50) cannot be empty or repeated
Type_father int cannot be null. If it is a top node, it is set to a unique value.

 

This design is short and concise, fully satisfying 3nf, and can satisfy all user requirements. Is that all right? The answer is no! Why? Let's estimate how the user wants to list the data of this table. For the user, he certainly expects to list all the categories at a time based on the hierarchy set by him, for example:

Total category
Category 1
CATEGORY 1.1
CATEGORY 1.1.1
CATEGORY 1.2
Category 2
CATEGORY 2.1
Category 3
CATEGORY 3.1
CATEGORY 3.2
......

 

Let's see how many times the above table needs to be searched to display such a list (first-order tree traversal? Note that although category 1.1.1 may be a record added after category 3.2, the answer is still n times. This efficiency does not affect a small amount of data, but after the data type is expanded to dozens or even hundreds of records in the future, the table will be retrieved dozens of times for a single column, the efficiency of the entire program is not flattering. Maybe the second type of programmers will say, then I will create a temporary array or temporary table to save the first order traversal results of the type table, so that the results will be retrieved dozens of times at the first run, when listing all the type relationships again, you can directly read the temporary array or temporary table. In fact, you don't need to allocate a new memory to save the data. You just need to expand the data table and then constrain the number of types to be added, you only need to retrieve the list once. The expanded data table structure is as follows:

 

Category Table _ 2 (type_table_2)
Description of name type constraints
Type_id int no duplicate category ID, primary key
Type_name char (50) cannot be empty or repeated
Type_father int cannot be null. If it is a top node, it is set to a unique value.
Type_layer char (6) is limited to three layers. The initial value is the first sequential traversal of the 000000 category, mainly to reduce the number of times the database is retrieved.

According to this table structure, let's take a look at the data recorded in the table in the above example:

 

Type_id type_name type_father type_layer
1. Total Category 0 000000
2 Category 1 1 010000
3 category 1.1 2 010100
4 category 1.2 2 010200
5 Category 2 1 020000
6 category 2.1 5 020100
7 category 3 1 030000
8 category 3.1 7 030100
9 category 3.2 7 030200
10 category 1.1.1 3 010101
......

 

Search by the type_layer size: Select * From type_table_2 order by type_layer lists the record sets as follows:

 

Type_id type_name type_father type_layer
1. Total Category 0 000000
2 Category 1 1 010000
3 category 1.1 2 010100
10 category 1.1.1 3 010101
4 category 1.2 2 010200
5 Category 2 1 020000
6 category 2.1 5 020100
7 category 3 1 030000
8 category 3.1 7 030100
9 category 3.2 7 030200
......

 

The record order is exactly the result of first-order traversal. When controlling the hierarchy of display classes, you only need to judge the values in the type_layer field, each of which has two digits. If the value is greater than 0, move two spaces to the right. Of course, in this example, the limit is a maximum of three layers, each layer can have a maximum of 99 sub-categories, as long as you modify the length and number of digits of the type_layer according to user requirements, you can change the number of restricted layers and the number of sub-categories. In fact, the above design is not only used in the category table, some online forum programs that can be displayed by tree type list mostly adopt similar design.

 

Some people may think that the type_father field in type_table_2 is redundant data and can be removed. In this case, when inserting or deleting a category, you have to make a tedious judgment on the content of the type_layer, so I have not deleted the type_father field, this is also in line with the principle of appropriately retaining redundant data in the database design to reduce Program Complexity. I will introduce a case of intentionally increasing data redundancy.

　　
Ii. Design of commodity information table

Assume that you are an electronic brain developer of a department store. One day, the boss asks you to develop an online e-commerce platform for the company. The department store sells thousands of products, however, currently, we only plan to sell dozens of products online for convenient transportation. Of course, new products may be added to the e-commerce platform in the future. Now we have begun to design the product information table of the platform database. Each sold item has the same attributes, such as the product ID, product name, product category, related information, supplier, number of items included, inventory, purchase price, sales price, and discount price. You will soon design four tables: wares_type, wares_provider, and wares_info ):

 

Item type table (wares_type)
Description of name type constraints
Type_id int no duplicate category ID, primary key
Type_name char (50) cannot be empty or repeated
Type_father int cannot be null. If it is a top node, it is set to a unique value.
Type_layer char (6) is limited to three layers. The initial value is the first sequential traversal of the 000000 category, mainly to reduce the number of times the database is retrieved.

 

Supplier table (wares_provider)
Description of name type constraints
Provider_id int no duplicate supplier ID, primary key
Provider_name char (100) cannot be empty Supplier name

 

Item info table (wares_info)
Description of name type constraints
Wares_id int no duplicate item ID, primary key
Wares_name char (100) cannot be empty product names
Wares_type int cannot be a null product type identifier, which is associated with wares_type.type_id
Wares_info char (200) allows null Information
Provider int cannot be a blank supplier ID, which is associated with wares_provider.provider_id
The initial value of setnum int is 1. The default value is 1.
The initial value of stock int is 0. The default value is 0.
Buy_price money cannot be empty purchase price
Sell_price money cannot be empty sale price
Discount money cannot be empty discount price

 

You check the three tables for the boss. The boss hopes to add another item image field, but only some items have images. OK. You added a haspic bool field to the item info table (wares_info), and then created a new table -- Item image table (wares_pic ):

 

Product image table (wares_pic)
Description of name type constraints
Pic_id int no duplicate product image ID, primary key
Wares_id int cannot be empty product identifier, associated with wares_info.wares_id
Pic_address char (200) cannot be empty image storage path

 

After the program development is completed, it fully meets the current requirements of the boss and is officially enabled. After a while, the boss plans to launch new product sales on this platform. All of these products must be added with the "length" attribute. The first round of hard work ...... Of course, you added a bool field of haslength in the item info table (wares_info) according to the old method of adding the item image table, and created a new table-the item length table (wares_length):

 

Item length table (wares_length)
Description of name type constraints
Length_id int no duplicate product image ID, primary key
Wares_id int cannot be empty product identifier, associated with wares_info.wares_id
Length char (20) cannot be empty item length description

 

Not long after the change, the boss planned to add a new batch of products. This time, all the products of this type need to be added with the "width" attribute. You have bitten your teeth, caught your teeth, and added the commodity width table (wares_width ). After a while, the boss has some new products that need to be added with the "height" attribute. Do you feel that the database you designed is growing in this way, will it soon become a maze? So, is there any way to curb this unpredictability, but it is similar to repeated database expansion? I have read Agile Software Development: Principles, models, and practices and I have found a similar example: 7.3 "copy" program. Among them, I strongly agree with the idea of Agile Software Development: At first there was almost no pre-design, but once the demand changes, as a programmer pursuing excellence, the entire architecture design should be reviewed from the ground up to design a system architecture that can meet similar changes in the future. The following is the modification scheme I provided when I want to add the "length" attribute:

 

Remove the haspic field in the item info table (wares_info) and add two additional item attribute tables (wares_ex_property) and item extra information table (wares_ex_info) to add new attributes.

 

Additional item Attribute Table (wares_ex_property)

Description of name type constraints
Ex_pid int no duplicate item additional attribute identifier, primary key
P_name char (20) cannot be empty.

 

Additional item info table (wares_ex_info)
Description of name type constraints
Ex_iid int no duplicate item additional information identifier, primary key
Wares_id int cannot be empty product identifier, associated with wares_info.wares_id
Property_id int cannot be empty item additional property identifier, associated with wares_ex_property.ex_pid
Property_value char (200) does not allow null additional item attribute values

 

Add two records to the additional item Attribute Table (wares_ex_property:

Ex_pid p_name
1. product images
2 Commodity Length

 

Add an additional item property management feature to the background management function of the entire e-commerce platform. New attributes will appear when new items are added later, you only need to use this function to add a record to the additional product Attribute Table (wares_ex_property. Don't be afraid of changes. It's not a bad thing to be hit by the first bullet. The bad thing is to be hit by the second and third bullets flying in the same orbit. The sooner the first bullet arrives, the heavier the injury, and the stronger the resistance. 8)

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/jerry1089/archive/2009/11/10/4792176.aspx