Share 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 relevant programs, the best database design is often impossible to be achieved without a single step, but can only be repeatedly explored and refined step by step. This is a complex process, and it is also a process of planning and structuring the data objects in the database and the relationship between these data objects. Here are 20 database design best practices that, of course, depend on whether they are appropriate for your program. 1 Let's get to know.

1. Use clear, generic 1 notation and column names such as School, SchoolCourse, CourceID.
2. Use singular rather than plural table names, such as StudentCourse instead of StudentCourses.
3. Do not use Spaces for table names.
4. Do not use unnecessary prefixes or suffixes for table names, such as School instead of TblSchool, SchoolTable, etc.
5. The password in the database should be encrypted and then decrypted in the application. (Hash storage, one-way encryption)
6. Use integers as ID fields, which may not be necessary now, but will be needed in the future, such as associated tables, indexes, and so on.
7. Use integer fields as indexes, otherwise it will cause a big performance problem.
8. Using bit as a Boolean field, using an integer or varcha is wasteful. Also, such fields should start with "Is".
9. You must be authenticated to access the database. Do not give every user administrator privileges.
10. Avoid using "select *" and use "select [required_column_list]" for better performance.
11. If the program code is complex, use the ORM framework, such as hibernate, iBatis. The performance issues of the ORM framework can be addressed through detailed configuration.
12. Split infrequently used tables into different physical stores for better performance.
13. For critical databases, use secure backup systems such as clustering, synchronization, and so on.
14. Use foreign keys, non-null restrictions to ensure data integrity, do not throw everything to the program.
Lack of database documentation can be fatal. You should document your database design, including triggers, stored procedures, and other scripts.
16. For frequently used queries and large data tables, use indexes. Data analysis tools can help you decide how to index.
The database server and the web server should be on separate machines. This improves safety and reduces stress on the CPU.
18. The Image and blob fields should not be defined in commonly used tables, as this can affect performance.
19. The paradigm (Normalization) is used as required to improve performance. Not doing enough of Normalization can lead to data redundancy and too much of Normalization can lead to too many join and data tables, both of which can affect performance.
Spend more time on database design, or you'll spend twice as much time paying it back later.

English original address: ES52en.com