Share 20 best Practices for database design _mysql

Database design is one of the key points of the whole program, in order to support the relevant program operation, the best database design is often impossible, can only repeatedly explore and gradually refinement, this is a complex process, but also the planning and structured database data objects and the relationship between these data objects process. Here are 20 database design best practices, of course, the best, or whether it fits your program. Let's get to know each other.

1. Use clear, uniform marking 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 the studentcourses.
3. Do not use spaces for data table names.
4. Do not use unnecessary prefixes or suffixes for data table names, such as using school instead of Tblschool, or schooltable, and so on.
5. The password in the database should be encrypted and decrypted in the application. (In fact, hash storage, one-way encryption)
6. Using integers as ID fields may not be necessary now, but will need to be in the future, such as associative tables, indexes, and so on.
7. Indexing with integer segments can cause significant performance problems.
8. Using bit as a Boolean field, using integers or varcha is a waste. At the same time, such fields should begin with "is".
9. To be authenticated to access the database, do not give each user administrator permissions.
10. Try to avoid using "SELECT *" and use "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 the ORM framework can be solved by detailed configuration.
12. Partition infrequently used data tables to different physical stores for better performance.
13. For critical databases, use secure backup systems, such as clustering, synchronization, and so on.
14. The use of foreign keys, not null and other restrictions to ensure the integrity of the data, do not throw everything to the program.
15. The lack of database documentation is fatal. You should design a document for your database, 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 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 commonly used data tables, or they can affect performance. The
19. Paradigm (normalization) is used as required to improve performance. Normalization does not do enough to cause data redundancy, and excessive normalization can cause too many joins and datasheets, both of which can affect performance.
20. Spend more time on database design, or you will pay twice as much time in the future.

In-situ: javacodegeeks.com