Database Design & Coding Standards

Database coding standards vary each place you go. Here are a few of mine.

  • Every table should have an auto-incrementing integer primary key column
  • Name the primary key “id” in every table, even if you intend to use other values as the actual identifier. This makes life really easy since the row identifier is consistent across the entire database. In a replication or data archival scenario, you can easily use the id to ensure the data is copied to the new location prior to deleting the data from the original holding place
  • Table names should be plural
  • Never use spaces in your object names, whether they are tables, columns etc.
  • Foreign Key columns are named: entity name + “_” + “id” e.g. email_id, customer_id, member_subscription_id.
  • Do not use SELECT * in any permanent code. The introduction of new columns or other changes can break dependent systems.
  • Always explicitly specify column names in an INSERT statement.
  • Avoid cursors where possible, and try to re-think the problem using set based logic. Helper tables can significantly assist with this issue.
  • Use SET NOCOUNT ON in your stored procedures.
  • Do not prefix stored procedure names with “sp”
  • Organize your stored procedures by naming them “prefix + entity name + action” . For example
    usp_company_delete usp_delete_company
    usp_company_insert usp_insert_company
    usp_company_update usp_update_company
    usp_company_upsert usp_upsert_company
    This saves a lot of time since all the objects which affect a specific entity are neatly arranged in the object browser in Query Analyzer or SSMS. Items become much easier to work with than procedures named usp_insert_company
  • Avoid dynamic SQL during every day coding, but remember it is a very powerful feature, so just choose carefully when to use it. General practice is to avoid dynamic SQL for code items that the general public will access.
  • Use consistent character casing when writing queries, it makes code so much easier to read. Capitalize keywords such as SELECT, FROM , UPDATE, INSERT, etc. use lower case for all columns and object names. Define constants using all caps. It just makes them easier to see in code. e.g. DECLARE @PI NUMERIC(18,10)
  • Use “_” to separate words instead of camel casing
  • Do not use column numbers in SQL Statements e.g. ORDER BY 2 ASC
  • Use constraints to enforce referential integrity. Even if your application does all the validations on the front end, implement the constraints in the database also. It is a big safety net.
  • Whenever a value is going to be used in multiple places, declare a variable and store the value there. If the business requirements changes, modifying the code is much faster and safer.
  • When archiving data, use a left outer join to ensure that only data which has not been copied before is moved to the new location.
    INSERT INTO new_location (id, a,b,c)
    SELECT o.id, o.a, o.b, o.c
    FROM
    old_location o LEFT OUTER JOIN
    new_location n ON (o.id = n.id )
    WHERE
    n.id IS NULL
    Likewise when deleting do an INNER JOIN so that only data which has been moved to the new location is deleted from the old location.
  • Do not return multiple result sets from stored procedures, use only 1 result set
  • Use only 1 format for your result set. Do not return a different result set if there is an error. Either succeed or fail. e.g. Do not do
    IF ( condition )
    SELECT a, b, c, d FROM table_name
    ELSE
    SELECT 'Some sort of error Message' as error_message
    END
  • Do not develop any code on your production database. If you do not have the resources for isolated development, testing and staging environments, simple test on the same machine in a separate database ( db_name_prod, db_name_dev, db_name_test , db_name_stage )
  • Do not hard code any personal email addresses or contact information in code. If necessary to send emails via this method, have a group email address created and have the emails go to the group address. This way if your team changes, someone leaves or joins your team, there is no need to change the code.
  • Explicitly define the names of all constraints and indexes. Though the database server will give system generated names.
  • For every single change that is made have a release script and a rollback script.
  • Perform Unit tests on all your code to make sure each object works as expected.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s