Best practices for Database Design

Best practices for Database Design 1. Use well-defined tables or column names and maintain consistency (for example, School, StudentCourse, CourseID ). 2. Use the table name in the singular form (that is, use StudentCourse instead of StudentCourses ). A table represents a collection of entities and does not need to be in the plural form. 3. Do not use spaces in table names. Otherwise you will

Best practices for Database Design 1. Use well-defined tables or column names and maintain consistency (for example, School, StudentCourse, CourseID ). 2. Use the table name in the singular form (that is, use StudentCourse instead of StudentCourses ). A table represents a collection of entities and does not need to be in the plural form. 3. Do not use spaces in table names. Otherwise you will

Best practices for Database Design

1. Use a well-defined table or column name and maintain consistency (such as School, StudentCourse, and CourseID ).

2. Use the table name in the singular form (that is, use StudentCourse instead of StudentCourses ). A table represents a collection of entities and does not need to be in the plural form.

3. Do not use spaces in table names. Otherwise, you will have to use characters such as "{" and "[" (that is, to access the Student Course table, you must write "Student Course ". It is enough to use StudentCourse ).

4. Do not add unnecessary prefixes or suffixes to the table name (that is, name School, instead of TblSchool or SchoolTable ).

5. encrypt the password to ensure security. You can decrypt them in the application as needed.

6. Use the integer ID field in all tables. Even if the ID is not needed at the moment, it will be used in the future (such as in associated tables and indexes ).

7. Create an index using INTEGER (or related) Data columns. Varchar column indexes may cause performance problems.

8. Use the bit field for the Boolean value. It is unnecessary to use Integer or Varchar storage. In addition, add the Is description to these column names.

9. Verify database access. No user should be assigned the admin role.

10. Avoid using the "select *" query statement unless necessary. For better performance, use "select [required_columns_list]".

11. If the program code is large, you can use the ORM (Object link ing) framework (such as Hibernate and iBatis) tool. You can configure parameters in detail to address performance issues.

12. Separate tables (table parts) that are not used or are not commonly used but are large into different physical buckets to provide better query performance.

13. Use disaster recovery solutions and security services for important database systems, such as failover clustering, automatic backup, and replication.

14. To ensure data integrity, use constraints (such as foreign keys, Check, and Not null constraints ). Do not give full control over the application code.

15. The habit of lacking database documents is very bad (edevil ). Use the erdiagram to describe the database design model. Remember to write code for triggers, stored procedures, and other scripts.

16. Use indexes for frequent queries. The Analyser tool can be used to determine where the index is defined. Clustering index is usually better for retrieving a large number of columns from a query. For vertex queries, you can use non-clustered indexes.

17. Place the database server and Web server on different computers. This provides better security performance (attackers cannot directly access data), and the server can also obtain more CPU and memory performance because it can limit the number of access requests and the number of processes.

18. Images and Blob columns must not be defined in frequently queried tables. This is also due to performance considerations. You can place the data in a separate table and create a pointer to them in the query table.

19. Normalization is required to further optimize application performance. Otherwise, there may be too many data copies. Of course, over-normalization will lead to a large number of connections across too many tables. Both of them will affect the performance.

20. We also need to spend some time on Database Modeling and design. If you save time for this, you are likely to face 10 or even 100/1000 times of maintenance/redesign costs.