Optimal Database Design

Optimal Database Design (transfer)

1. Primary Key
Auto-increment primary keys, such as Oracle sequence or UUID algorithm, or creating a class that generates unique numbers by yourself. The primary key is meaningless.
Avoid using a composite primary key.
Double primary key, that is, a meaningless Id field is used as the primary key, such as a sequence. The other is that the business number no is used as the primary key (such as the product number and user code .) For example, if cancel can only have one primary key, this is a unique key. When the meaning of service no cannot be fully determined, it is defined as varchar2, which may affect performance.

The business no. is not used as the primary key because a sequence "Number" is used as the primary key, which provides better performance. Second, there may be changes in the encoding rules of the Business Code at the later stage of operation, and inaccurate Length Estimation. For example, if ID card is used as the primary key, there are many problems. For example, if it was a number in the past 15 hours, it was designed as number (15). However, when it was increased to 18 bits, the length was not enough, what's worse is that there may be letters in the 18-digit identity. Changing data types is a big problem. Another example is that for orders, the order number went smoothly at the beginning. Later, the customer said, "the order can be voided and the order can be generated again, and the order number must be consistent with the original order number ", in this way, the original primary key is in danger.
The dual-primary key also uses the operating system design to delete a Peter user, and then another Peter, then the two users should have different permissions (in the authorization table ). However, if the authorization table uses user no as the key for the associated user, the later person will have the permissions of the original person, which is wrong. (This also describes what we will mention below. If it is not absolutely certain, do not set foreign keys or delete restrictions. For foreign key applications, is a problem that can be studied .)
Composite primary keys are automatically processed, such as page batch deletion or encoding public usage. Of course, the performance is not as good as a single primary key. If Hibernate and other O/R tools are used, refer to the ing example. For a simple single object to express the many-to-many relationship, there are only two relative table IDs, this table has no other meaning. You do not need the meaningless ID mentioned above as the primary key. However, there are few such items. In the order/order entry/product example, if the product item is implemented as the set/List of the order, it seems that the ID cannot be added in the order item. (However, the business table design should be the master, while hibernate should be the end. We do not recommend using hibernate to promote the business database design. Therefore, the ID Identifier in Hibernate is mainly used as the identification of hibernate, which may be separated from the ID in the table design I mentioned above, but for the above mentioned situations, it's just a good fit. In this case, if Hibernate is used, the ID field can also be processed as a property of the composite-element of list/set, but the generator identified by the hibernate class ID cannot be used, that is, you must assign values when inserting data, or you can obtain values such as sequence or UUID in programming to maintain database layer consistency)

Primary Key Selection: first, the business number is used as the primary key, there will be problems mentioned above. The second is the meaningless automatic serial number, such as the sequence. The third is Max plus one, and sometimes the same is true with the business number. If you use this table directly, max (NO) + 1 will seriously affect the performance when data increases. 4. Self-made increment one is an improved implementation of Max increment one, which is implemented using the no_ctrl table number control file. As we will talk about later, this is also a more feasible proposition. It is mainly used to generate business codes with business coding rules. Fifth, UUID/GUID, because UUID/GUID is meaningless, unordered, and longer, it has no advantage over sequence. (However, in some special cases, whether all tables or some tables must have unique primary keys. For example, when a unified design audit process and rules are reviewed, an order is placed, A primary key ID of a purchase order is used as the foreign key of the audit table. This can be customized in hibernate.

2. Name
Tables, fields, and views are all separated by underscores (_) to facilitate the transformation from data persistence to objects. Refer to the Java Naming rules, in this way, for example, if you use JB or write your own program, the conversion will be separated by '_' to form the upper and lower case letters of the Java attribute.
The table name/view name is differentiated by the prefix of your business module or 'T'/'V.

From the persistence to the object type, it is best to have the same rules for the conversion from table to Java classes (unless the attributes used in programming need to be added after Java is generated). The best way is to use tools to generate them, use JB to generate CMP. Of course, if Hibernate is used, it may also be reversed. The HBM. XML is generated by using Java class design (UML), XDoclet, and so on, and then the database DDL is exported according to HBM. xml.
Like such a complete rule, it may be useful when coding all the tables using such as reflection.

3. Generally, during the design, all tables should have the following fields (except for the fractional configuration table, as mentioned in no_ctrl below, you can add them when you are not sure. You don't need to use them first ),
ID: Number Primary Key
No/compound field: Business primary key (unique key)
Create_usr: creator (not null)
Create_time: creation time (not null)
Modify_usr: Last Modifier
Modify_time: last modification time
Delete_flag: 0/1, T/F, true/false Delete flag (default 'F' not null)
Delete_usr: deleted
Delete_time: the deletion time.
Version: Number default 1 version (not null)
During the design, you may not have to mark the deletion, but delete it directly. It may be because the customer has not yet imagined or proposed it. However, designers should think of possible extensions. The same applies to versions with optimistic user locks, which may not be used at the beginning, but should also be a method of locking.
You can choose the above time/version to use programming (application layer to assign values) or database layer tirgger.

When designing a field, it is best to think about whether there is a default value. Add comments, especially fields such as delete_flag.

4. Field Type (not written yet, mainly refers to the time type, and some use char (8) 20010101 or char (10. Is there no flag, such as status or type, using Char or number? (Please give your comments on the database layer and programming convenience .)

5. Design and consideration of Business Code or self-made serial numbers plus one.

As mentioned above, you can use a self-made number to add a primary key design. But it is also suggested that you do not set a meaningless pure primary key. Therefore, we generally use self-made designs for "business numbers", because business numbers may have prefix suffixes and generally require sequential and continuous numbers. Therefore, we will mainly discuss the problem of business coding as follows:
A. For serial numbers, use MAX + 1. If the insert statement is executed directly, the multi-user continuity is guaranteed. Insert into in_box values (max (in_no) + 1 ,...). If there is a prefix suffix, you can also use substr such as Oracle to implement the rule. If you first use a select max (in_no)... in programming and then execute insert, the latter repeats when multiple users exist. The bigger problem is the performance problem mentioned above, so it is not recommended.
B. Use a self-made no_ctrl table for storage (Note: Oracle sequence is also said to be implemented internally). The basic no_ctrl is like this, no_ctrl (ID, table_code, column_code, curr_num ). The basic practice is to add 1 when the insert is complete. (The data stored in a table in your design may be a document, and the basic data items of the document are the same, but the document numbers of different types are different and continuous, the table should be modified accordingly, if you want to satisfy all situations like this, you may have to think more ). Generally, there is a special class to achieve the no function.
C. For some businesses, especially documents in ERP, the document number must be displayed on the user input interface, which is complicated. The simplest is to generate the next no on the page, and use noctrl. nextno (Table, column). The page is transmitted to the background like other input items for data addition. The nextno Implementation of noctrl is select curr_no + 1, update curr_no + 1, that is, retrieve and add an update. A serious problem with this method is that even a single user cannot guarantee that the Service serial number is continuous, because it is updated during retrieval. If an exception occurs during service execution, this serial number is wasted. Therefore, this method should be improved mainly by "delayed Update. That is, the nextno () method only executes the query and returns the next number, which is displayed on the page. when the business is added in the background, the update and increment actions are executed in a transaction. Pay attention to the situation of multiple users, and re-execute to get the number in the new business (because the number may have been updated by another user when you execute the retrieval number, your ID has been used ). So your new business will be like this, TX. begin (), ctrlno. nextno (Table, column), insert (business), ctrln. update (), TX. end (). for ctrlno. update () to further ensure consistency, You can include where currno = currno. Like Optimistic Locking and version. At the same time, there may be situations where you need to use multiple numbers consecutively when adding a single number. It is best to use lock reading.
D. The preceding section mainly describes the continuity of business numbers, which are numbers. However, generally, the business number is not just a number, but a prefix and suffix, and is generated according to certain rules. Therefore, it is good to use no_ctrl. This may be the case, no_ctrl (ID, table_code, column_code, prefix, Postfix, curr_num, num_len ). That is, prefix, suffix rules, and font size.

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 428282