Flashback database design skills (on)

Apr 28 posted in Development (RSS), comments.

Speaking of databases, in my opinion can not but talk about data structures. In 1996, I joined the University of studying computer programming, when teachers told us that: a computer program = data structure + algorithm. Even though the current process-oriented program development has been mainly of a gradual transition to the main object-oriented, but I deeply agree with 8 years ago, teachers told us the formula: a computer program = data structure + algorithm. Object-oriented development, the first thing to do is, first analysis of the whole process to deal with data extracted from an abstract template to design the abstract template, and then gradually add the treatment at one of its data function ( that the algorithm), and finally, another type of data members and function into access in order to achieve package.

The initial prototype of the database is said to originated from a dairy farm in the United States accounting thin (paper-based, and we can see that the database is not necessarily stored in the computer data are recorded ^_^), inside the dairy farm income and expenditure accounts, programmers at its collation, entry to the computer when to be inspired. When good data structure in accordance with the provisions of the data collected a large quantity to a certain extent, the procedures for the implementation of efficiency considerations, programmers will be one of the retrieval, update maintenance functions separated into a separate module called the module Later on slowly evolved into what we are exposed to the database management system (DBMS) - procedures for the development of an important branch.

The following into the subject, first of all, according to my personal contact with the process give the database designer's knowledge about sub-categories:

1, there is no systematic study of programmers over the data structure. This type of programmer's work is often only their impromptu toys, they often used to design only a limited number of tables, implementation of certain functions of all the data in Cyprus at a table, the table is almost no connection between. A lot of free web management software are all kinds of things, when the program features are limited, small amount of data when its program up and running no problem, but if compared with its management of important data, the risk is very great.

2, the system data structures have been studying, but have not yet developed the program had a relatively high efficiency requirements management software programmers. Most of these people graduated from the School soon, they in the design database table structure, in strict accordance with the provisions of textbooks, Die buckle ER diagram and 3NF (Do not lose heart, all are from the master database design step in the beginning). Their work, for general-type lightweight access management software, has been enough. However, if the system needs to add new features, the original database table is almost too big to bring in new blood.

3, the second category programmers, experienced at a number of procedures to enhance efficiency, and feature upgrades after disruption, and finally escalated into a database designed Old birds , the first category of the eyes of an expert programmer. Such programmers can do more than 20 tables of data management system for medium-sized commercial development. They know what the circumstances must retain the redundant data to improve process efficiency, and its design database scalability is better, when users add new features when required, the original database table can only make a small amount of modification .

4, at 10 on experienced database management software similar to duplicate the design, the third category of programmers insist it does not switch, but want to find out "lazy" tricks of the people will gradually consciousness, and thus achieve quantitative change to qualitative change conversion. They designed the structure of database tables must have the vision, can predict the future upgrade functions required by the data, thereby pre-future. At present, most of these programmers into advanced data mining aspects of advanced software developers.

5, the third category or the fourth category programmers programmers, at each of the existing database management system and development of the principle must have, after intensive study, either at its foundation on a second development, either on its own to develop a have their own copyright Universal Database management systems.

I personally are at the end of the third category, so the following list is only suitable for some design skills and part of the second category third category of database designers. At the same time, because I rarely encountered in this respect there is interest in deep drilling indefinitely counterparts, so the text will inevitably lead to errors and omissions, in this first statement, welcomed the U.S. correction, not to Hide private Oh 8 )

First, the relationship between the data table tree

Many programmers in the database design when they are encountered tree relationship data, such as common types of form, that is a big category, the following has a number of sub-categories, some sub-categories and sub-categories such circumstances. When the type of uncertainty, users want to be able to arbitrarily add a new category of sub-categories, or delete a category and all sub-categories, and it is expected that their number will gradually after growth at this time we will consider the use of a data table to save the data. Up in accordance with the teachings of the textbook, the second category will be approximately programmers design a data table similar to this structure:

Category Table _1 (Type_table_1)
Name	Type	Constraint condition	Description
type_id	int	No duplicate	Category logo, primary key
type_name	char (50)	Does not allow for air	Type the name, does not allow duplicate
type_father	int	Does not allow for air	The categories of the parent category ID, if it is the top node, then set to a unique value

This design dapper, fully satisfy the 3NF, and meet all the requirements of users. Is this it on the line? The answer is NO! Why?

Us to estimate what the user wants to set out the data in this table. Of users, of course, look forward to him as he set the level of relations between the time set out all the categories, such as this:
Total categories categories 1
Type 1.1
Category 1.1.1
Type 1.2
Category 2
Type 2.1
Category 3
Type 3.1
Type 3.2
... ...

Take a look at the list in order to achieve this show (the first tree traversal), to the above table the number of times search? Note that even though the categories are possible at 1.1.1 categories by 3.2, add the record, the answer is N times. The efficiency of such a small amount of data for nothing, but later expanded to several types of 10 or even hundreds of records, only one type is necessary to search out dozens of the table, the whole process leaves much to be desired on the operating efficiency. Perhaps programmers would say that the second category, then I build a temporary array or temporary table, specifically to preserve the type of table the results of the first traversal, so that only at first search dozens of run-time, once again listed all the types of relations on directly read the temporary array or temporary table will do. In fact, no need to go a new distribution of memory to save the data, as long as the data table must be expanded, and then to add the quantity to what types of constraints on the list, it is necessary to achieve the above list just one search on the list. The following is the expanded data table structure:

Category Table _2 (Type_table_2)
Name	Type	Constraint condition	Description
type_id	int	No duplicate	Category logo, primary key
type_name	char (50)	Does not allow for air	Type the name, does not allow duplicate
type_father	int	Does not allow for air	The categories of the parent category ID, if it is the top node, then set to a unique value
type_layer	char (6)	Limited to 3 layers, the initial value is 000000	Categories of first traversal, mainly to reduce the number of searchable database

In accordance with this table structure, we take a look at the example above, records the data in the table are how:

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
 ……

Now according to the size to retrieve type_layer click: SELECT * FROM Type_table_2 ORDER BY type_layer

Record Set list is 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
 ……

Record is now listed in the order are precisely the results of the first traversal. At the level of control when the display category, as long as the numerical field type_layer judge, each 2 1 group, such as more than shifted to right 0 to 2 spaces. Of course, this example I set restrictions are a maximum of 3 layers, each layer can be located up to 99 sub-categories, as long as the demand by the user changed a bit type_layer the length and the median, you can change the restrictions on the number of layers and sub-categories. In fact, the above design is not just only in the type of table used, tree may be a list of some online forums shows that the procedures are mostly similar design.

Some may think that, Type_table_2 in the field are type_father redundant data can be removed. If so, in the insert, delete a category, the need to compare the contents of type_layer cumbersome to determine, so I did not eliminate type_father field, and this is consistent with the database design appropriate to retain data to reduce redundancy procedures complexity of the principles behind, I will give you an intentional increase in the case of data redundancy.

Two, the design of merchandise information sheet

Assuming you are in a department store Department of computer developers, one day the boss asked you for the company to develop a set of online e-commerce platform, the department store and there are several thousand kinds of goods sold, but currently only the intention to dozens of online sales at the convenience of transport merchandise and, of course, probably will continue later in the e-commerce platform to add new merchandise on sale. The platform will now begin the merchandise information database table design. Each sale of merchandise will have the same property, such as merchandise code, merchandise name, merchandise category, relevant information, supply manufacturers, including the number, stock, stock price, sales price, price. You quickly design a four table: the type of merchandise table (Wares_type), suppliers table (Wares_provider), merchandise information sheet (Wares_info):

The type of merchandise table (Wares_type)
Name	Type	Constraint condition	Description
type_id	int	No duplicate	Category logo, primary key
type_name	char (50)	Does not allow for air	Type the name, does not allow duplicate
type_father	int	Does not allow for air	The categories of the parent category ID, if it is the top node, then set to a unique value
type_layer	char (6)	Limited to 3 layers, the initial value is 000000	Categories of first traversal, mainly to reduce the number of searchable database

Table vendors (Wares_provider)
Name	Type	Constraint condition	Description
provider_id	int	No duplicate	Supplier logo, primary key
provider_name	char (100)	Does not allow for air	Supplier Name

Merchandise information sheet (Wares_info)
Name	Type	Constraint condition	Description
wares_id	int	No duplicate	Logo merchandise, primary key
wares_name	char (100)	Does not allow for air	Trade names
wares_type	int	Does not allow for air	The type of logo merchandise, and associated Wares_type.type_id
wares_info	char (200)	Permit is empty	Related information
provider	int	Does not allow for air	Availability manufacturers logo, and associated Wares_provider.provider_id
setnum	int	The initial value of 1	Intron number, defaults to 1
stock	int	The initial value of 0	Inventory, the default is 0
buy_price	money	Does not allow for air	Purchase price
sell_price	money	Does not allow for air	Sale price
discount	money	Does not allow for air	Price

You with this table 3 give the boss inspection, the owner would like to add one more picture of the merchandise field, but only part of picture there is merchandise. OK, you merchandise at information tables (Wares_info) added a haspic of the BOOL type field, and then built a new table - Table merchandise picture (Wares_pic):

Pictures merchandise table (Wares_pic)
Name	Type	Constraint condition	Description
pic_id	int	No duplicate	Pictures logo merchandise, primary key
wares_id	int	Does not allow for air	Their merchandise logo, and associated Wares_info.wares_id
pic_address	char (200)	Does not allow for air	Pictures stored path

Program development is complete, fully satisfy the requirements of the current owner, they were officially opened. After a period of time, the boss intends to set up a platform to introduce new merchandise sales, including certain types of merchandise all need to add "length" of the property. Toss to the first round of the ... ... Of course, you add the merchandise in accordance with the picture of the old table method, merchandise information in the table (Wares_info) added a haslength of the BOOL type field, but also to build a new table - the length of merchandise table ( Wares_length):

The length of merchandise table (Wares_length)
Name	Type	Constraint condition	Description
length_id	int	No duplicate	Pictures logo merchandise, primary key
wares_id	int	Does not allow for air	Their merchandise logo, and associated Wares_info.wares_id
length	char (20)	Does not allow for air	Note the length of merchandise

Has just been changed not long after, the boss also intends to up a new batch of merchandise, this particular type of merchandise all need to add the "width" of the property. The teeth and bite you, but also according to For some side , add the merchandise table width (Wares_width). After a period of time, the new boss on the merchandise in there is some need to add "height" of the property, are not you feel that you start by designing a database in this manner in accordance with the growth continues, soon it turned into a maze? Well, there is no way to curb this unpredictability, but similar expansion can duplicate a database? I read "Agile Software Development: Principles, Patterns and Practice" found authors cite a similar example: 7.3 "Copy" procedure. Among them, I very much agree with this point of Agile Software Development: hardly at the initial pre-design, but once the demand for change, the pursuit of excellence at this time as a programmer, it should be de novo review of the whole architecture design, in the revised design similar amendments to be able to meet the future system architecture. The following are required at my add "length" of the property provided by the modified program:

Remove the merchandise information sheet (Wares_info) in haspic fields, add additional merchandise property table (Wares_ex_property) and additional merchandise information sheet (Wares_ex_info) 2 tables to complete the property to add new functionality.

Additional merchandise table property (Wares_ex_property)
Name	Type	Constraint condition	Description
ex_pid	int	No duplicate	Additional property merchandise logo, primary key
p_name	char (20)	Does not allow for air	Additional property name

Additional merchandise information sheet (Wares_ex_info)
Name	Type	Constraint condition	Description
ex_iid	int	No duplicate	Additional information merchandise logo, primary key
wares_id	int	Does not allow for air	Their merchandise logo, and associated Wares_info.wares_id
property_id	int	Does not allow for air	Additional property merchandise logo, and associated Wares_ex_property.ex_pid
property_value	char (200)	Does not allow for air	Additional property value of merchandise

Additional property in the merchandise table (Wares_ex_property) to add two records:

ex_pid            p_name
1                 Merchandise pictures  
2                 The length of goods

Again at the entire e-commerce platform management functions in the background an additional merchandise additional property management functions, after adding new merchandise when a new property, simply use the function to the merchandise table extra property (Wares_ex_property) add a record that is can be. Do not be afraid to change, was the first hit by a bullet is not a bad thing, but the bad ones are the same as the track was the second coming, the third hit by bullets. Sooner than the first bullet, the more serious injuries, after the resistance is also more intense and 8) (Continued)