Over the years I’ve experienced various problems with temporary and custom database objects (as in objects created specifically for certain client systems). Development and deployment teams are often distinct and this can create issues. These issues have included;

  • Temporary objects persisting for months or years beyond their initial purpose.
  • Temporary objects spreading from development to live environments.
  • Client specific objects spreading to other client systems.

These issues can be exacerbated as developers leave the organisation and sometimes by reluctance to identify redundant objects. This is especially true in organisations with multiple systems accessing the same data sources. Who knows if that legacy system still running needs that table? Here are some conventions I have developed to try and eliminate these issues with minimal fuss.

Temporary Naming Conventions

  • For objects only required on the created date start table names with ‘temp_’, i.e.
CREATE TABLE temp_customer_import
(
	/*
	-- Table definition
	*/
);
  • For tables required for an indeterminate period of time start with ‘temp_<contact_name>’ where <contact name> is the person to check with as to its status. i.e.
CREATE TABLE temp_rhyscampbell_customer_import
(
	/*
	-- Table definition
	*/
);

Client Specific Naming Conventions

Often I have seen client specific stored procedures created in development environments which have then been deployed to other client sites.

  • Include the company name in client specific database objects, i.e.
CREATE PROCEDURE usp_ACME_LTD_process_customer
AS
BEGIN
	/*
	-- Proc definition
	*/
END

Adherence to these simple rules should solve many of these issues painlessly. Remember, a clean database, is a happy database!