20 best Practices for database design

Original: 20 best practices for database design

　　Database design is one of the key points of the entire program, in order to support the operation of the relevant programs, the best database design is often not possible, can only be repeatedly explored and gradually refinement, this is a complex process, is planning and structured database data objects and the relationship between these data objects process. Here are 20 best practices for database design, and of course, the best is to see if it fits your program. Get to know it together.

2. Use clear, unified labeling and listing names, such as School, Schoolcourse, Courceid.
4. The data table name uses the singular rather than the plural, such as studentcourse, rather than studentcourses.
6. Do not use spaces for data table names.
8. Data table names do not use unnecessary prefixes or suffixes, such as using school instead of Tblschool, or schooltable, and so on.
10. Passwords in the database are encrypted and then decrypted in the application. (In fact, hash storage, one-way encryption)
12. 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.
14. Using an integer number field to index, otherwise it will cause a lot of performance problems.
16. 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".
18. To be authenticated to access the database, do not give each user administrator permissions.
20. Try to avoid using "SELECT *" and "SELECT [Required_column_list]" for better performance.
22. 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.
24. Partition infrequently used data tables into different physical storage for better performance.
26. For critical databases, use secure backup systems such as clustering, synchronization, and so on.
28. Use foreign keys, non-null and other restrictions to ensure the integrity of the data, do not throw everything to the program.
30. A lack of database documentation is fatal. You should write documents for your database design, including triggers, stored procedures, and other scripts.
32. For frequently used queries and large data tables, use the index. Data analysis tools can help you decide how to build an index.
34. The database server and the Web service should be placed on different machines. This improves security and reduces CPU pressure.
36. Image and Blob fields should not be defined in a common data table, otherwise they may affect performance.
38. 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.
40. Spend more time on database design, or you will pay twice as much time in the future.

English Original: javacodegeeks.com