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: , , , ,


6 Responses to “Database Best Practices”

  • lostsock Says:

    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.

  • mpjw Says:

    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.

  • Ignacio Coloma Says:

    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)

  • AzMoo Says:

    # Avoid leaking your database design details, such as primary keys.

    Can you clarify what you mean by this? Avoid leaking?

  • panzi Says:

    > 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?

  • Recent Links Tagged With "bulk" - JabberTags Says:

    […] public links >> bulk Database Best Practices Saved by thelamborghini on Sat 11-10-2008 Stranded bulk carrier freed – News.com.auA BULK carrier […]

Leave a Reply