What are 20 best practices for database design ?

Photo by Christina Morillo from Pexels

1. Use a well-defined table or column names and keep them consistent (for example, School, StudentCourse, CourseID).
2. Use the singular form of the table name (ie, use StudentCourse instead of StudentCourses). The table represents a collection of entities and does not need to be plural.
3. Do not use spaces in the table name. Otherwise, you will have to use characters such as "{" and "[" when defining the table (that is, in order to access the table Student Course, you have to write "Student Course". Using StudentCourse is sufficient).
4. Do not add unnecessary prefixes or suffixes to the table name (that is, just name School, not TblSchool, SchoolTable, etc.).
5. Encrypt the password to ensure security. You can decrypt them in the app when needed.
6. Use integer ID fields in all tables. Even if the ID is not used right now, it will be useful in the future (for example, in the associated table, index, etc.).
7. Use integer (or related) data columns to create indexes. Varchar column indexes can cause performance problems.
8. Use bit fields for boolean values. It is unnecessary to use Integer or Varchar type storage. At the same time, add the description of Is to these column names.
9. Verify database access. No user should be given the admin role.
10. If not necessary, avoid using "select *" query statements. For better performance, please use "select [required_columns_list]".
11. If the program code is large, you can use the ORM (Object Relational Mapping) framework (such as Hibernate, iBatis) tools. Regarding its performance problems, it can be dealt with through detailed configuration parameters.
12. Separate large tables (table parts) that are not used or not commonly used into different physical storage spaces to provide better query performance.
13. For important database systems, use disaster recovery solutions and security services, such as failover clustering, automatic backup, and replication.
14. In order to ensure data integrity, please use constraints (such as foreign keys, Check, Not null constraints, etc.). Don't give complete control over the application code.
15. The lack of database documentation is very bad (evil). Use ER diagram to describe the database design model. Also remember to write code for scripts such as triggers and stored procedures.
16. Use indexes for frequent queries. The Analyser tool can be used to determine where the index is defined. For queries to fetch a large number of columns, a clustered index is usually better. For point queries, non-clustered indexes can be used.
17. Place the database server and the Web server on different computers. This can provide better security performance (the attacker cannot directly access the data), and because the number of access requests and the number of processes can be limited, the server can also obtain better CPU and memory performance.
18. Image and Blob columns must not be defined in frequently queried tables, this is also for performance considerations. You can put these data in a separate table, and create a pointer to them in the lookup table.
19. Normalization (Normalization) is necessary to further optimize application performance. Otherwise, you may face too many data copies. Of course, over-normalization will result in a large number of connections across too many tables. Both of these will affect performance.
20. It is also necessary to spend some effort on database modeling and design. If you save time for this, you are likely to face 10 times or even 100/1000 times the maintenance/redesign cost.