Speaking of the database, I don't think I can't talk about the data structure first. When I was studying computer programming in my first, the teacher told us: Computer program = data structure racing algorithm. Although the current development has gradually transitioned to the object-oriented process, I still endorsed the formula of the teacher in 8 years ago: computer program = data architectural algorithm. The first thing to do for the object is the first thing to do, first analyze the information you need to process throughout the program, and extract abstract templates, design the abstract template, and then add a function of processing its data (ie Running algorithm), finally, the information member and function in the class access permissions are reached, thereby achieving packaging. The initial prototypes of the database are said to be derived from a cows in the United States (paper, which can be seen, the database is not necessarily stored in the computer ^ _ ^), the record is that the dairy farm The accounting, the programmer is inspired when it is organized and entered into the computer. When the amount of information collected in accordance with the specified data structure is large enough, the programmaster will separate the retrieval, update maintenance, and make the module that are individually called. The module later developed slowly, evolving into an important branch in the Database Management System (DBMS) -Derting development we have expired.
Let's enter the topic. First, according to the program that I personally get touched to the category of the database designer: 1, there is no system to learn the formula of the information structure. The work of this type of staff is often just their impromptu toys. They are often used to design a limited number of tables, achieving all types of functions all plug in a table, almost unrelated between the tables. Many free management software online is something like this. The model is limited, and there is nothing wrong with the amount of data. It has no problem, but if it is used to manage, it is very important, and the risk is very large. 2. The system has learned the data structure, but has not yet developed a programmaster who has a high-quality management software. Most of these people have just graduated from schools. They are strictly in accordance with the provisions of the textbooks, and they are strictly buckled with E-R and 3NF in accordance with the textbook structure. Don't be discouraged, all the database design masters start from this step.). Their works are enough for the general Access type feather level management software. But once the system needs to add new functions, the original database table is almost a large blood. 3, the second type of staff, after experiencing the improvement of the efficiency of several times, and after the functional upgrade, finally upgraded to the old bird in the database, the first type of master. This type of program can be eligible for the development of more than twenty medal business data management systems. They know what to keep certain redundant data to improve program efficiency, and its design is better, and when the user needs to add new functions, the original database table only needs a small amount of modification. I.e. 4, after the repetition design of the top ten similar database management software, the third type of master adheres to no to turn, but hope that the universal people who are "lazy" tips will slowly understand, so that the completion of the completion The conversion of the quality change. The database table structure they designed has a certain vision, predicting the information required for future functional upgrades, thereby pre-left the film. This type of program is currently promoting advanced software developers in data mining. 5, the third type of programmaker or the fourth class teacher, after a certain research on the principles and development of the existing database management system, or on the basis of its second development, or develop a set of self-developed A general repository management system with independent copyright. I personally is in the end of the third category, so some of the design techniques listed below are only suitable for the second category and some third type of database designer. At the same time, because I rarely encounter interested in this area, it is inevitable to have errors and omissions in the text. In this first declaration, welcome everyone to correct, don't hide the private oh 8) 1. Tree relationship Many programmakers encountered information on tree-type relationships when they were designed, such as common categories, which were a large class. There are several subclasses below, and some subclasses have such a case. When the category is uncertain, the user wants to add a new subclass at any category, or delete all the subclasses under the category, and it is expected to grow gradually, and we will consider using a data sheet. To save these information. According to the teachings in the textbook, the second type of program will probably design a data sheet structure similar to this: Category Table_Table_1) Name Type Constrained Condition Description Type_ID INT No Repeated Category Identifier, Primary Key Type_name Char (50) is not allowed Empty type name, does not allow the Type_father INT to be repeated, which is not allowed to be empty, if the top node is set to a unique design short, completely satisfying 3NF, and meets all the requirements of the user.
Is it the case? The answer is NO! Why? Let's estimate how users want to list the information on this table. For the user, he certainly expects the relationship between the level of the level to list all the categories, for example: Total 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 ... In order to achieve such a list (the order of the tree is traversal), how many times is it to be retrieved? Note that although the category 1.1.1 may be added after the category 3.2, the answer is still N times. Such efficiency has no effect on small amounts of information, but after the lane type is expanded to dozens of or even hundreds of records, the single column must be retrieved for dozens of tables. The overall operation efficiency is not duck. Perhaps the second class teacher, then I built a temporary array or temporary table, saving the order of the type table, which only retrieves ten times in the first runtime, and then all types of relationships again Just read the temporary array or temporary table. In fact, it is not necessary to share a new memory to save these information, as long as you have a certain expansion of the information sheet, then constrain the number of add types, you must complete the above list. . The following is an expanded data sheet structure: Category table _2 (Type_Table_2) Name Type Constraint Condition Description Type_ID INT No Duplicate Category Identifier, Primary Key Type_name Char (50) Does Not Allows Type_father Int Not allowed to be empty The parent category identifier of this category, if the top node is set to a single value type_layer char (6) limit 3 layers, the initial value is the order traversal traversal, mainly for reducing the number of retrieval databases, in this table Structure, let's take a look at the information recorded in the table: TYPE_ID TYPE_NAME TYPE_FATHER TYPE_LAYER1 Total Category 0 0000002 Category 1 1 0100003 Category 1.1 2 0101004 Category 1.2 2 0102005 Category 2 1 0200006 Category 2.1 5 0201007 Category 3 1 0300008 category category 3.2 3.1 70301009 703020010 3010101 category 1.1.1 type_layer by size ...... now be retrieved once: SELECT * FROM Type_table_2 ORDER bY type_layer listing record set as follows: type_id type_name type_father type_layer1 general category categories 110,100,003 00,000,002 Category 1.1 2 01010010 Category 1.1.1 3 0101014 Category 1.2 2 0102005 Category 2 1 0200006 Category 2.1 5 0201007 Category 3 1 0300009 Category 3.1 7 0301009 Category 3.2 7 030200 ...... The record order listed is exactly the result of the order traversal . When the level of the display category is controlled, as long as the value in the Type_Layer field is judged, each 2nd group, such as greater than 0, to the right. Of course, the restrictions set in this example are up to 3 floors, up to 99 subcategories per floor, as long as the length and bits of Type_Layer can be modified according to the user's needs, you can change the limit layer and subclass number. In fact, the above design is not only used in the category table, and some of the online schedules that can be displayed on the web use similar design. Perhaps some people think that Type_father field in Type_Table_2 is redundant data, which can be removed.
If so, when inserted, delete a category, you have to decide the content of Type_Layer, so I don't eliminate the Type_father field, which is also in line with the appropriate redundant data in the database design. The principle of program complexity, and I will give a case in deliberately adding data redundancy. Second, the design assumption of the product information form is a developer of the department store computer department, and a boss requires you to develop an online e-commerce platform for the company. The department store has thousands of merchandise for sale, but currently only intended to be in the net The sale of dozens of convenient transportation, of course, will increase new commodities on the e-commerce platform in the future. Now start the design of the product information form for the platform material. Each sale of goods will have the same properties, such as product number, product name, product belongings, related information, suppliers, internal components, inventories, intrading price, sales price, discount price. You quickly designed 4 tables: Product Type Table (Wares_Type), supplier (WARES_PROVIDER), Product Information Form (WARES_INFO): Product Type Table (WARES_TYPE) Name Type Constraint Condition Description Type_ID INT No Duplicate Category ID, Primary key type_name char (50) does not allow the null type name, not allowing the Type_father Int not allowed to be empty, the parent category identifier of this category, if it is the top node, set to a single value type_layer char (6) limited 3 layers, initial The first sequence traversal of 000000 category is mainly for reducing the number of suppliers (WARES_PROVIDER) Name Type Type Constrained Conditions, the primary key provike, the primary key provike, the primary key provider, the main key provocator_name char (100) does not allow empty suppliers Name Product Information Sheet (Wares_INFO) Name Type Constrained Condition Description Wares_ID INT No Duplicate Product Identification, Primary Key WARES_NAME CHAR (100) Does Not Allow Empty Product Name WARES_TYPE INT Not allowed to be an empty product type identity, and WARES_TYPE.TYPE_ID association WARES_INFO CHAR (200 ) Allow empty related information Provider Int does not allow empty supply vendor identity, and WARES_PROVIDER.PROVIDER_ID association setNum int initial value is one internal component, the default is that the 1stock Int initial value is 0 stock, the default is 0buy_price Money is not allowed Air-forward price Sell_Price Money does not allow for empty sale Pricent Money not allowed to be empty, you have this 3 tables to check the boss, the boss hopes to add a product picture, but only some of the goods have pictures. OK, you add a HASPIC's BOOL type field in the Commodity Information Form (Wares_Info), then build a new table - Product Image (WARES_PIC): Product Image (WARES_PIC) Name Type Constrained Condition Description Pic_ID INT No Duplicate product image logo, the primary key Wares_ID INT does not allow the empty belonging, and the WARES_INFO.WARES_ID association PIC_ADDRESS CHAR (200) does not allow for the empty picture to save the path to the road, completely meet the current requirements of the boss, so it is officially enabled.
