20 database Design A brief talk on suggestion

1. Use a complete and consistent data table name and field name, such as: School, Studentcourse, CourseID

2. The data table name uses the singular, such as using Studentcourse instead of studentcourses, the data table represents a collection of entities, so it is not necessary to use the plural name.

3. Do not use spaces for data table names, such as Studentcourse better than student course.

4. Do not use unnecessary prefixes and suffixes for data table names. such as Tblschool, schooltable are not as good as school.

5. The password must be encrypted and decrypted in the application only if needed.

6. Use integer ID as field for all data tables, if no ID is needed now, it may be needed in the future, such as related tables or indexes.

7. Selecting a field of integer type as the index, the varchar field index can cause performance problems.

8. Using the bit field as a Boolean value, using an integer interger or varchar as a Boolean field increases unnecessary storage consumption, and these Boolean field names start with "is".

9. Provide authorized access to the database and do not assign the Administrator role to everyone.

10. Avoid "SELECT *", if necessary, need to confirm the necessity, use "select Field name" To have better performance.

11. The ORM (object and Relational database mapping) framework, such as Hibernate ibatis, is used only on a certain scale by the application system, which requires detailed configuration parameters for tuning.

12. Cut the large, rarely used tables into multiple tables and store them on different physical storage, which gives good performance.

13. For large and important database systems, use the failed recovery cluster for automatic backup replication.

14. Use Constraints (foreign keys or not NULL) to maintain complete data consistency, and do not give all controllers to the application code.

15. Lack of database documentation is evil.

16. For frequent queries using indexes, analysis tools can help you decide which indexes are needed.

17. The database server and the Web server are placed on different machines. This has better performance.

18. Do not define the image and BLOB data types in a table that is frequently queried. These special data needs to be placed in separate tables.

19. Normalization (normalisation) contributes to performance gains, non-normalization causes data redundancy, and too much normalization introduces too many tables, both of which cause performance problems.

20. If it takes time to design a database model, saving design time can lead to lengthy maintenance or redesign time.