- Use well defined and consistent names for tables and columns (i.e. School, StudentCourse, CourseID ...).
- Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
- Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you'll write “Student Course”. StudentCourse is much better).
- Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.).
- Keep passwords as encrypted for security. Decrypt them in application when required.
- Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing ...).
- Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
- Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
- Provide authentication for database access. Don’t give admin role to each user.
- Avoid “select *” queries until it is really needed. Use "select [required_columns_list]" for better performance.
- Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis ...) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
- Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
Read more: CodeBalance
QR:
