Juixe TechKnow knowing is half the battle

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