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
Related posts:
5 Comments
Great list, thanks.
I am web developer of about 3 years now and I just have a query about one guideline. Is there any reason you suggest “Do not use SELECT * in your queries, explicitly write the required columns names in the select statement.”?
I have found that using ‘SELECT *’ queries allows me to simply add fields to the database in order to have them accessible to my application without the needs to explicitly add them there too. I have read (can’t remember when or where) that using ‘SELECT *’ is not a performance hit so just wondering why you suggest against its use?
Cheers, Danny.
If u need all the columns in your application u can do that, but most of the time only selected fields are necessary. Doing a ’select *’ will definitely impact network, memory, etc.
You cannot know for sure the number and order of the columns when you select *. If, say, an unused column gets dropped in the database, your query will fail with the weirdest of errors (like an integer field found of type DATE)
# Avoid leaking your database design details, such as primary keys.
Can you clarify what you mean by this? Avoid leaking?
> Offload tasks like string manipulations, concatenations, row numbering, case conversions, type conversions, etc to the front-end application.
Wouldn’t that lead to a bloated DB design?
One Trackback
[...] public links >> bulk Database Best Practices Saved by thelamborghini on Sat 11-10-2008 Stranded bulk carrier freed – News.com.auA BULK carrier [...]