20 Database design Best Practices __ Database

In general, two aspects can be used to determine whether the database design of the comparison specification. One is to see whether there is a large number of narrow tables, and the second is that the number of wide tables is small enough. If these two conditions are met, it can be explained that the standard of the database is still relatively high. Of course, this is a two-generalities indicator. To meet the requirements of database design normalization, the following 20 rules are best met:
1, the use of well-defined and consistent name table and columns (such as school, Studentcourse CourseID ...).

2, use a single table name (that is, use studentcourse instead of studentcourses). A table represents a collection of entities, and does not require a plural name.

3, do not use a space to define the table name. Otherwise you will have to use the ' {', ', ' ' ' and ' ' ' character definition table (for example accesing Student course you will write "Student Course". Studentcourse much better).

4, do not use unnecessary prefix or suffix table name (for example, use school instead of Tblschool, schooltable, etc.).

5, keep the encryption password security. Decrypt the application when it is needed. (hash storage, one-way encryption)

6, using an integer type ID applied to all tables. If the ID is not needed for the time being, it may need to be in the future (associated table, index ...).

7. Select the column index of the integer data type (or its variant). A varchar type of column index can cause performance problems.

8, use bit type to apply to Boolean value. Using an integer or varchar can result in unnecessary storage consumption. When naming these columns, it is best to start with "is".

9, the need for database access rights. Do not give each user admin role.

10, avoid "SELECT *" Query, until it is really necessary. Use Choose [Required_columns_list] to get better performance.

11, if the program code is more complex, use ORM (Object Relational Mapping) framework. The performance problems of the ORM framework can be configured by a detailed parameter.

12. Assigning a table with little or no use to a different physical storage allows for better query performance.

13. Use disaster recovery and security services for large, sensitive, and most-needed database systems. such as failover clustering, automatic backup, replication, and so on.

14. Use constraints on the integrity of the data (foreign key, check, NOT null ...). Do not give all control logic to the application code.

15, the lack of database documentation is very unfortunate. Write the database design document using the ER diagram and explain. and write comments for your triggers, stored procedures, and other scripts.

16. Add indexes for large tables and frequently used queries. The parser tool can be used to determine where an index needs to be used. The federated index is usually better when querying a series of rows as criteria. For a conditional query for a single field, it is usually better to index the character.

17. The database server and the Web server must be deployed on different machines. This will provide more security (attackers cannot access data directly) and server CPU and memory performance will be better because of reduced requests and processing.

18. Graphics and BLOB data columns cannot be defined in frequently queried tables, which is based on performance considerations. Separate the data into separate tables. Then use the reference to point to the data, which is the associated query.

19, must use the database design third normal form normalization request, optimizes the performance. Not using a paradigm will result in too much duplication of data in too many tables, overuse of the paradigm will cause too many tables to be jion. They all use the system's performance to become very poor.

20, it is necessary to spend time on the database modeling and design. Otherwise, to reduce the design time will result in spending 10 times times/100 times times even 1000 times times for maintenance and redesign.
Original address: Database Design Best Practices