Database Best Practices

1. Table name, column name valid character set: lowercase letters, numbers, underscores, such as Student_course
2, table name, column name in singular rather than negative, such as student, not students
3, table name, column name does not use unnecessary prefixes or suffixes, such as Tbl_school, school_table
4, using an integer as the ID field, may not be necessary now, but in the future need, such as association tables, indexes and so on
5, the password in the database to encrypt, to the application of re-decryption (in fact, is hashed storage, one-way encryption)
6, using the whole number of fields to index, otherwise it will bring a lot of performance problems
7, using bit as Boolean field, using Integer or Varcha is wasted. At the same time, this type of field should start with "is"
8, to be authenticated to access the database, do not put the administrator rights to each user
9. Try to avoid using "SELECT *" While using "SELECT [Required_column_list]" for better performance
10, if the program code is more complex, using ORM Framework, such as Hibernate,ibatis. The performance problems of ORM framework can be solved by detailed configuration.
11. Split infrequently used data tables into different physical storage for better performance.
12, for the key database, the use of secure backup system, such as cluster, synchronization and so on
13, the use of foreign keys, non-empty and other restrictions to ensure the integrity of the data, do not throw all the things to the program; But for Internet applications, the environment should reduce the use of foreign keys because the application layer is more convenient for horizontal expansion.
14, should have the database design to write the document
15, for frequently used queries and large data tables, to use the index. Data analysis tools can help you decide how to build an index
16. Image and Blob fields should not be defined in a common data table, otherwise it will affect performance
17, the appropriate application paradigm (normalization). Normalization does not do enough to result in data redundancy, and excessive normalization can lead to too many joins, impacting performance.
18, spend more time on the database design, otherwise you will pay double time to repay

Http://www.javacodegeeks.com/2012/02/20-database-design-best-practices.html

This article is from the "GONE with the Wind" blog, please be sure to keep this source http://h2appy.blog.51cto.com/609721/1627697

Database Best Practices