Speaking of the database, I don't think you can't talk about the data structure first. In 1996, when I first study computer programming, the teacher told us: Computer program = data structure algorithm. Although the current program development has gradually transitioned to the object-oriented process, I still endorsed the formula of the teacher to tell us 8 years ago: Computer Program = Data Structure Algorithm. Object-oriented program development, the first thing to do is to analyze the data that needs to be processed throughout the program, extracts the abstract template, designs classes in this abstract template, then add a function of processing its data (ie, Algorithm), finally, the data member and function in the class are partitioned to achieve a package. The initial prototype of the database is said to be a record of a cows in the United States (paper, which can be seen, the database does not necessarily be the data stored in the computer ^ _ ^), recorded the balance of the dairy farm Accounts, the programmer is inspired when it is enjointed it and enters the computer. When the amount of data collected in accordance with the specified data structure is large to a certain extent, the programmer separates the retrieval, update maintenance and other functions for program execution efficiency, and makes a separately called module, this module Later, slowly develop, evolve into a database management system (DBMS) - a important branch in program development now. Let's enter the topic, first click on the skill of the database designer in the program that I personally touched: 1. There is no system to learn the programmer of the data structure. Such programmers' works are often just their impromptu toys. They are often only a limited number of tables, achieving all types of functions in a table, almost unrelated between the tables. There is a lot of free management software online, when the program is limited, the data is not much, and the program runs does not have any problems, but if it is more important, it is very important. 2, the system has learned the data structure, but has not developed programmers for management software for program efficiency requirements. These people have just graduated from school shortly. They are strictly in accordance with the provisions of the database table, and they will die in accordance with the provisions of the textbook, and the E-R map and 3NF (do not discourage, all database design masters start from this step). Their works are enough for general Access type light level management software. But once the system needs to add new functions, the original database table is almost a large blood. 3, second types of programmers, after experiencing the efficiency of several programs, and after the functional upgrade, finally upgraded to the old bird designed by the database, the highest people in the eyes of the first class. Such programmers can be eligible for the development of medium-sized commercial data management systems above more than twenty tables. They know what to keep certain redundant data to improve program efficiency, and their design is better, when the user needs to add new functions, the original database table is only necessary to do a small amount of modification. . 4. After the repetition design of the top ten similar database management software, the third type of programmer insisted that there was no transfer, but it hoped that the "lazy" tricks will slowly realize, so that the completion of the volume change Conversion. The database table structure they designed has a certain vision, predicting the data required for future functional upgrades, thereby pre-detail. Such programmers currently advance in advanced software developers in data mining. 5. Third class programmers or collected programmers, after a certain research on the principles and development of existing database management systems, or on the basis of the second development, either develop a set A general database 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 class and some third type of database designer.
At the same time, because I rarely encounter interested in this area, I will inevitably have errors and omissions in the text. In this first declaration, welcome everyone to correct, don't hide, 8), tree-type relationship data sheet Many programmers encountered data of tree-type relationships when they perform database design, such as common categories, which are a large class, and there are several subclasses, some subclasses, and subclasses. 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 this data. According to the teaching of the textbook, the second type of programmer will probably design a data sheet structure similar to this: Category table _1 (Type_Table_1) Name Type Constraint 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 data from this table. For the user, he certainly expects the relationship between the level of the level, such as this: That is: 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 ...... See how many times will you do with the top table in order to achieve such a list (sequence traversal of the tree)? 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 data, but after the lane type is expanded to dozens of or even hundreds of records, the single column is to retrieve dozens of tables, and the operation efficiency of the entire program does not dare. Perhaps the second type of programmer will say, then I built a temporary array or temporary table, specifically saving the order of traversal traversal of the type table, which only retrieves ten times in the first runtime, once again all types of type relationship Read the temporary array or temporary table directly. In fact, use no new memory to save these data, just make certain expansion of the data table, and then constrain the number of added types, and to complete the above list. Just retrieve it.
Below is an expanded data table 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 Allow Empty Type Name, does not allow repeated Type_father Int not allowed empty The parent category identifier of this category, if the top node is set to a single value type_layer char (6) Limited 3 layers, the initial value is the order traversal of the 000000 category, mainly for retrieving the number of retrieval databases, in such a table structure , Let's take a look at the above example of the data recorded in the table: TYPE_ID TYPE_NAME TYPE_FATHER TYPE_LAYER1 Total 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 3.1 7 0301009 Category 3.2 7 03020010 Category 1.1.1 3 010101 ...... Press TYPE_LAYER to search: select * from type_table_2 Order by Type_Layer lists the record set as follows:
TYPE_ID TYPE_NAME TYPE_FATHER TYPE_LAYER1 Total 0 0000002 Category 1 1 0100003 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 0300008 Category 3.1 7 0301009 Category 3.2 7 030200 ... now column The record order 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 2-bit group, such as greater than 0, one to 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 discussion of the forum programs displayed by the tree list is mostly similar design. Perhaps some people think that type_father fields in Type_Table_2 are redundant data, which can be removed. If so, insertion, delete a category, you have a cumbersome judgment on the content of Type_Layer, so I don't eliminate the Type_father field, which is also in line with the appropriate retention of redundant data in the database design to reduce the complexity of the program. The principle of degrees, I will give a case in deliberately add data redundancy. Second, the design of the product information form is assumed to be the developer of the department store computer department, a day boss requires you to develop a set of online e-commerce platforms for the company, and the department store has thousands of merchandise for sale, but currently intended to be There are dozens of convenient transportation, of course, will increase new commodities on the e-commerce platform in the future. The design of the product information table of the platform database is now started. Each sale of goods will have the same properties, such as product number, product name, product belongings, related information, suppliers, internal components, inventories, in stock, price, price.
You quickly designed 4 tables: Product Type Table (Wares_TYPE), supplier (WARES_PROVIDER), product information table (Wares_INFO): Product Type Table (Wares_Type) Name Type Constrained Condition Description Type_ID INT No Trust Category Identifier, Primary key type_name char (50) does not allow the 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 floors, The initial value of 000000 category is traversed, mainly for retrieving the number of suppliers of the retrieval database (WARES_PROVIDER) Name Type constraint conditions, the Provider_ID INT has no reply supplier, the primary key provider_name char (100) does not allow air supply Business Name Product Information Table (Wares_Info) Name Type Constrained Condition Description Wares_ID INT No Duplicate Product Identity, Master Wares_Name Char (100) Does Not Allow Empty Product Name WARES_TYPE INT Not allowed to be an empty product type ID, 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 1 The number of items, the default is the 1stock int initial value of 0 stock, the default is 0Buy_price Money not allowed to feed the price Sell_Price Money does not allow empty sales price discount monéy is not allowed to be empty, you hold these 3 tables to the boss The boss hopes to add a field of items, but only some of the goods have pictures.
OK, you add a HASPIC's Bool type field in the product information sheet (Wares_INFO), then build a new table - Product Image Table (Wares_Pic): Product Image (WARES_PIC) Name Type Constrained Condition Description Pic_ID INT No Duplicate Product image logo, primary key WARES_ID INT does not allow empty belonging, and WARES_INFO.WARES_ID INT PIC_ADDRESS CHAR (200) does not allow for an empty picture storage path program, fully meets the current requirements of the boss, so it is officially enabled. After a while, the boss intends to launch new product sales on this platform, of which certain types of goods need to be added "Length" attributes. The first round is tossing ... Of course, you add a new table in the product information sheet (Wares_Info) in the product information sheet (Warength) in the product information sheet (Wares_Info), and built a new table (Wares_Length) : Product Length Table (Wares_length) Name Type Constrained Condition Description Length_ID INT No Duplicate Product Image Identification, Primary Key Wares_ID INT Does Not Allowed Empty Ordinary Product Identity, and Wares_info.wares_id Association Length Char (20) Does Not Allow Empty Product Length Description Just Not long after the change, the boss intends to put a batch of new products, this type of goods need to add "width" attributes. You bite your teeth and take care of the medicine and add a commodity width table (Wares_WIDTH). After another time, there are some properties that need to add "height" in new products. Do you start to think that the database you designed is growing in this way, soon become a maze? So, is there any way to curb this unpredictability, but is similar to repeated database expansion? I am reading "Agile Software Development: Principle, Mode and Practice", the author has found similar example: 7.3 "Copy" program. Among them, I very much agree with agile software development. At present, I originally designed it, but once the demand changes, it is a programmer who pursues excellence, it should be reviewed from the beginning of the architecture, and designed in this revision It is possible to meet a system architecture similar to modified in the future. Here is the modification scheme I provide when you need to add a "length" property: remove the Haspic field in the product information sheet (Wares_Info), add a product extra property table (Wares_ex_property) and the product extra information sheet (Wares_ex_info) 2 tables Complete the function of adding new properties.
Additional Property Name (Wares_ex_Property) Name Type Constrained Condition Description EX_PID INT No Duplicate Product Additional Properties Identification, Motor Keys P_NAME CHAR (20) Does Not Allow Empty Additional Properties Name Product Extra Information Table (Wares_Ex_info) Name Type Constraint Condition Description EX_IID INT No Duplication Product extra information identification, primary key WARES_ID INT does not allow the empty product identity, and WARES_INFO.WARES_ID association property_id Int does not allow additional attribute identification for empty goods, and WARES_EX_PROPERTY.EX_PID Association Property_Value Char (200) Does not allow for an empty product additional attribute value Add 2 records in the extra property of the product (Wares_ex_Property): EX_PID P_NAME1 Product Image 2 Product Length Appends additional attribute management functions in the background management feature of the entire e-commerce platform, and new items appear when adding new products Attributes, just add a record to your product additional property table (Wares_ex_property). Don't be afraid of changes, it is not a bad thing in the first bullet, the bad is the second, the third bullet hits by the same orbit. The first bullet is getting more early, the stronger the injury is, the stronger the resistance is 8) · Talk about database design skills (below)
