Several suggestions on database design

This article is guided by: Database design is the basis of information system design, a good database design to meet the requirements of software, but also easy to maintain, easy to expand and so on, but also to consider the consistency of data, redundancy, access efficiency, database design includes: library design, table design, field design, primary key and foreign key design, Index design, constraint design, and so on, here are some suggestions for database design

First, the general good database design needs to pay attention to the following points

1, a good database design first to meet the needs of users

All information systems will eventually be submitted to end-users, and for this, we believe that consensus has been reached. But to accurately grasp the user's needs is difficult, although the experts have been from different aspects of the solution, but user demand is still one of the most uncertain factors in software engineering.

2, a good database design to facilitate maintenance and expansion

In order to meet the needs of users to modify and add, but also to satisfy a variety of hardware and software environment under the use of the system, most of the information systems have to be in their life levels and adjustments. In these upgrades and adjustments, there will be a considerable part of the database design changes, therefore, the database design is best from the beginning can be easily maintained, can be extended to the point of view.

(1), do not set a fixed meaning for the various numbering fields

rather, it is better to use a comparison chart to establish a control relationship between numbers and meanings. For example, many designers are accustomed to the Department of Information to give a fixed number, this design has a life-giving flaw: that is because this control relationship is not reflected in the database, it is necessary to use business logic to explain, so that a new adjustment will have to Updating the business logic code is also very prone to inconsistent errors.

(2), enumeration information should be reflected in the corresponding in the table

Instead of the value field in the table that uses the information, the benefit is that when the user wants to use the enumeration information as a query condition, it can be done by referencing the table It is easy to build this information and also avoids inconsistencies that can arise when multiple tables contain the enumerated information.

3. Establish a many-to-many relationship between tables and tables with association tables

instead of a field resolution, for example, in order to describe the association between the user (UserInfo) and the Role (roleinfo), we need to set up a comparison table Userinfo_roleinfo, instead of trying to create a longer field in the user table, such as roles (with RoleID1; RoleID2 ... form) Instead, because this is a field that explains the code that needs to be parsed in the business code, and the extension of the user role cannot be satisfied because of the roles length.

3, a good database design to have "readability"

as the programmer repeatedly emphasizes in programming books, it is important to focus on the readability of the code, considering that the future upgrade and maintenance of the information system may have to be done by another batch of The database design must also be understandable.

(1), using design documents to improve the readability of database design

This basically corresponds to the comment in the "readability" code. In a qualified database design document, you must give each table in the database, each field, the relationship between the tables, and the meaning and origin of the constraints, which makes it possible for developers to understand the design of the correct database based on user requirements and design documentation.

(2), give the table and view a meaningful name

this corresponds to the naming of variables and functions in the coding specification, and it is clear that CustomerInfo's name is easily reminiscent of the table containing the customer information, and it is named Table0001 can only make people feel puzzling outside. In addition, if the DBMS provides case support for table and view names, the name is best stitched together by each constituent word (the first letter capitalized) .

(3), using prefixes to give information other than the contents of the table and view

If the reference table Ref_ prefix, so that the business logic implementation personnel according to the name of the table to know what he is going to do is not a reference table, thus helping him to understand the details more quickly design, and the possibility of early detection of errors inside. Similarly, adding a v_ prefix to all views makes it easy for business logic programmers to know whether he is facing a table or a view, thus avoiding the low-level error of updating the view.

(4), give each field a meaningful name

It is easy to understand the exact meaning of the e-mail field in the CustomerInfo table, and Field05 makes it unintelligible. Based on the same principle, it is not possible to give a pigtailed name to a field in the design of a database.

(5), field naming to consider context

For example, in the UserInfo table, it is more appropriate to use username to represent the user name field than name. This situation is more obvious in the design of the comparison table , such as the Department table (ref_department) in the Department ID field named DepartmentID, the Department name field named Departmentname and so on.

(6), the design of the view should not be involved in other views

It is best to avoid nesting too many levels in the code design, and in order for readers of the database design to understand the design well, the view is best built directly into the table on.

(7), the records in the same table are best not to quote each other

This kind of referential relation not only makes the reader of database design foggy, but also does not facilitate the writing of business logic code.

(8) The name of the associated table is underlined in the middle of the associated table name

The association tables for students (STUDENTINFO) and courses (Courseinfo) are named Studentinfo_courseinfo.

4, a good database design can meet the requirements of space and efficiency

for an information system, on the basis of the realization of user requirements, to ensure that a low space occupancy and short response time are reasonable customers are willing to see. So in this respect, what does database design do?

(1), use varchar instead of char field

for indefinite length information such as user profile information, the use of varchar can reduce nearly half of the space occupied. Of course, this is not generalize, such as the corresponding length of char to store fixed-length text data is more appropriate than varchar.

(2), do not use the BLOB field to hold "Big data"

The same applies to the text fields introduced by some DBMS, where the Blob field is, by its very name, to store binary big data. Because for the general information System , the longest field tends to be some descriptive text information, and the DBMS's length to the char/varchar basically satisfies this requirement. It is therefore strongly recommended that the designer confirm the maximum allowable length of some seemingly lengthy text, based on the development manuals in the DBMS to determine whether to use large print segments.

(3), do not use the default field length of the designer

This practice on the one hand to allow designers to the user needs of the smattering and design perfunctory bad habits, on the other hand also in the data storage waste a lot of space, because using the default field length often occurs on a field.

(4), do not use Unicode text fields easily

The DBMS support for Unicode helps to internationalize the product while also contributing to a certain amount of wasted space, especially when the text to be stored is essentially ASCII character, this waste is particularly noticeable. Therefore, it is suggested that the reason for the designer to choose Unicode must be for international consideration, not others. Because most large character sets and ASCII characters coexist, the problem is basically solved by the DBMS provider.

(5), using the estimate table to improve the response speed

similar to some of the ideas in the Data Warehouse, it is best to rely on the system-independent precomputed module or the corresponding the DW tool periodically completes the pre-calculation of these statistics.

5, a good database design can simplify the design of business logic

all database design is not isolated, it is through the corresponding business logic implementation (three layer structure and the presentation layer) to form the final product, so a good database design should be able to help reduce the difficulty of writing business logic, at least not to the design of business logic, coding to bring extra work.

(1), all fields that are allowed to be empty must be based on the user's needs, not on design-friendly considerations.

the benefit is that some errors and omissions in the detailed design ( if the content check for non-null fields is not considered in the design, it is found in the Coding and unit test phases, which avoids further diffusion and helps to improve the quality of the software.

(2), do not implement uniqueness constraints of business logic code

The uniqueness constraints on some fields (or combinations of multiple fields) in a database table should be added to the database side as much as possible. This constraint is costly and unreliable because it is given to the business logic .

(3), Association constraints must be built on the database side

The main foreign key reference relationships involved in the design are analyzed and embodied in the database design. This article is for two reasons: reduce the difficulty of writing business logic and The requirement of Data association constraint.

Second, several suggestions of database design

1. Use clear, unified labeling and listing names, such as School, Schoolcourse, Courceid.
2. The data table name uses the singular rather than the plural, such as studentcourse, rather than studentcourses.
3. Do not use spaces for data table names.
4. Data table names do not use unnecessary prefixes or suffixes, such as using school, rather than Tblschool, or schooltable, and so on.

5. Encrypt the password in the database and decrypt it in the application. (In fact, hash storage, one-way encryption)
6. Using integers as ID fields may not be necessary now, but will be needed in the future, such as association tables, indexes, and so on.
7. Use an integer number field to index, otherwise it will cause a lot of performance problems.
8. Using bit as a Boolean field, using integers or varcha is a waste. At the same time, this type of field should start with "is".

9. To be authenticated to access the database, do not give each user administrator permissions.
10. Try to avoid using "SELECT *" and "SELECT [Required_column_list]" for better performance.

11. If the program code is more complex, use an ORM framework, such as Hibernate,ibatis. The performance problems of ORM framework can be solved by detailed configuration.
12. Split infrequently used data tables into different physical storage for better performance.
13. For critical databases, use secure backup systems such as clustering, synchronization, and so on.
14. Use foreign keys, non-null and other restrictions to ensure the integrity of the data, do not throw everything to the program.
15. Lack of database documentation is fatal. You should write documents for your database design, including triggers, stored procedures, and other scripts.

16. For frequently used queries and large data tables, use the index. Data analysis tools can help you decide how to build an index.
17. The database server and the Web service should be placed on different machines. This improves security and reduces CPU pressure.
18.Image and BLOB fields should not be defined in a common data table, otherwise they may affect performance.
19. Paradigm (normalization) to be used as required to improve performance. Normalization does not do enough to cause data redundancy, and excessive normalization can lead to too many joins and data tables, both of which can affect performance.
20. Spend more time on database design, or you will pay twice in the future

Several suggestions on database design