Database Best Practices
Every production application I have been involved with has made use of a database. As a programmer, you don’t have to be a DBA, but just as with software development you need to adhere to a core set of database best practices. Below are a few of the best practices I try to follow.
- Use a database modeling tool that is vendor neutral.
- Decide upon a database naming convention, standardize it across your organization and be consistent in following through with it. Always be consistent with the use of case and plural vs singular.
- Have a similar naming between your table columns and object properties.
- Try not to use system tables directly as they are not SQL standard and may change between releases.
- Do not use SELECT * in your queries, explicitly write the required columns names in the select statement.
- Always use a column list in your insert statements. This helps in avoiding problems when the table structure changes, like adding or removing columns.
- Try to avoid wildcard characters at the beginning of the word while searching using the LIKE keyword.
- Use the more readable ANSI standard join clause instead of he told style join.
- If you have a choice, do not store binary files in the database. Instead store the path to the binary file in the database and store the file in the disk, preferably another disk drive.
- Offload tasks like string manipulations, concatenations, row numbering, case conversions, type conversions, etc to the front-end application.
- Store computed string manipulations, concatenation, case conversion, type conversions, etc in the database so you don’t have to waste CPU cycles recalculating.
- Avoid leaking your database design details, such as primary keys.
- Just like your code, be sure to comment your database design.
- Use the bulk/multi insert statement.
- Study the SQL execution plan prior to a major release.
Again, as a software developer you don’t have to be a database tuning expert but you also can’t be excused for not following the above best practices.
Technorati Tags: database, db, sql, best practices, join