SQL Coding Standards

Last Modified: 2009-04-13

Table of Contents

• 1. Overview
  • 1.1. Introduction
  • 1.2. Scope
  • 1.3. Goals
  • 1.4. Assumptions
• 2. Formatting
  • 2.1. General
  • 2.2. Indentation
  • 2.3. Maximum Line Length
• 3. Naming Conventions
  • 3.1. Talkative names
  • 3.2. No Upper Case Abbreviations
  • 3.3. Tables
  • 3.4. Columns
  • 3.5. Constraints
  • 3.6. Indexes
• 4. Coding Style
  • 4.1. SQL statements and functions
  • 4.2. SELECT queries
  • 4.3. INSERT/UPDATE/DELETE queries
  • 4.4. Other queries

Back to Coding Standards list

1. Overview

1.1. Introduction

...

1.2. Scope

Describing the rules and guidelines for all possible SQL code occurrences in web applications.

1.3. Goals

The goals of this text are the same as corresponding part of Web Development Coding Standards for PHP.

1.4. Assumptions

Current CS was written with intention to be as more abstract from specific SQL engine as possible. But of course we have to admit that MySQL is the #1 engine for most PHP Web developers. So, please take into account this fact (or at least author's assumtion) while reading the text below.

2. Formatting

2.1. General

The following common rules must be considered while writing any SQL statement:

• New line for each clause and logical condition.
• Identation for each part of the clause (multiple AND conditions) and for all sub clauses.
• Each clause in the query starts with the same identation level.

These rules will be explained with examples below in the Coding Style section.

2.2. Indentation

Using of tabs for identation is strongly advised, with tab's width set to 4 spaces.

2.3. Maximum Line Length

The target line length is 80 characters, i.e. developers should aim keep code as close to the 80-column boundary as is practical. However, longer lines are acceptable.

3. Naming Conventions

3.1 Talkative names

The same rules as described in corresponding section for PHP Coding Standards.

3.2. No Upper Case Abbreviations

The same rules as described in corresponding section for PHP Coding Standards.

3.3. Tables

The following rules are obligatory for table names:

• All tables are plural, lower cased and words separated with underscores.
• Map tables are named with main entity coming first is singular and sub entity is plural.
• Always keep in mind 3.1 rule, but in the same time try to avoid very long table names. E.g. if your table contains more than four words, try to check it one more time.
• Never use table prefixes unless it's specified explicitly.

Example

Incorrect

Photo           -- upper cased char 
photocomments   -- two words without any separator
commentsPhotos  -- camel cased

Correct

photos 
photo_comments

3.4. Columns

Columns are named taking into account following rules:

• Columns are Camel Cased with first letter is lower cased
• Do not use underscores and minus chars
• Do not use table prefixes in column names
• Never use unpredictable abbreviations (the same is true everywhere actually :)

Example

Incorrect

permission_group_id            -- underscores
permissions_permissionGroupId  -- table prefix 
prmGrpId                       -- try to read this without context in year or something 

Correct

permissionGroupId

3.5. Constraints

Next rules should be considered for Primary Keys (PK) constraints:

• Never use id value for PK column names. That's the only case when you need to use table name as the prefix. E.g. for table users PK column is userId (singular and camel cased).
• Never use PK prefixes of suffixes in column names.
• :TODO: - move this cool thought to some other place. Auto incremental columns should be used only when they are really necesseary (e.g. for further reference). In case of map tables they must be avoided and entity columns become PK by them self - composite PK. For exampl for photo_tags table (links photos and tags table) contains two columns photoId and tagId and these two columns are composite PK.
• For the PRIMARY indexes it's recommended to use pk_table_name format, but in the same time default one (PRIMARY) will do.

Rules Foreign Keys (FK) constraints:

• Never use FK prefixes of suffixes in column names.
• FK are the object of the DB level and not the table one, that means their names must be unique for the whole DB. In order to achive this next format must be used: fk_reference_table__referenced_table.
• For each FK corresponding index of the same name must be created in reference table.

3.6. Indexes

For indexes please use idx_column format. You can also use idx_table_name__column format, but it's really not necesseary, since indexes are table level objects.

For unque constraints approach is the same as for indexes but with uniq_ prefix.

4. Coding Style

4.1. SQL statements and functions

It is strongly advised to use upper case notation for all SQL reserved words and functions. In first case it's a strict rule, and a guideline in second one.

Example

Incorrect

select * from table where date = now()

Select * 
From table
where date = now()

Correct

SELECT * 
FROM table
WHERE date = NOW()

4.2. SELECT queries

Taking into account general formatting rules, the correct SELECT query will be formatted like this:

SELECT a.field1, a.field2, b.field3...
FROM table1 a
    INNER JOIN table2 b
        ON a.fieldId = b.fieldId
        AND a.field1 = b.field1
WHERE a.someOtherField = 'value'
    AND b.anotherCondition = 1
    AND b.field4 IN (
        SELECT c.field4
        FROM table3 c
	)
ORDER BY a.field1 

Some comments to the example above:

• Try to avoid wildcard statement in SELECT clause, only use it if it's really necesseary.
• Always use table aliases when there is more than one table involved.
• Never use CROSS JOIN, only INNER and OUTER ones.
• Do not place each column to the new line in SELECT clause unless you have actual reasons for this.

4.3. INSERT/UPDATE/DELETE queries

Examples of correct data modification queries:

INSERT INTO table1 
    (field1, field2)
VALUES
    (1, 'some data')

UPDATE table1 SET
    field2 = 'new data'
WHERE field1 = 1

DELETE FROM table1
WHERE field1 = 1

4.4. Other queries

There is no any strict rule for all other statements, but anyway please keep in mind #2.1 when writing them.