SQL Coding Convention

Naming Conventions

• All names should be unique.
• Use descriptive names.
• Names should not exceed 30 characters.
• Names should only contain letters, numbers and underscore.
• Underscores are used instead of spaces where it would naturally occur - (first_name not firstName).
• Avoid camelCase.
• All names should start with letters.
• Avoid abbreviations, if needed try to use commonly understood abbreviations.

Tables

• Use collective name or plural form as table name.
  • Example (staff or employees, not employee)
• Never give tables and columns the same name.
• Always use lower case letters.
  • Example (sensors, not Sensors)
• Tables must contain at least one key.

Columns

• Use singular names.
• • Example(temp_value not temp_values)
• Do not name a column with a table name.
• Avoid using suffixes as names. Instead use a specific name followed by a suffix.
  • Example ( sensor_id not id.)
• Always use lowercase letters.

Comments

• When comments are necessary, use C style comments “ /* */ for multiple lines“.
• For single line use "--"

Table creation

• Use indentations to separate CREATE TABLE, columns and constraints to read easier.
  • Example.

CREATE TABLE staff (
 PRIMARY KEY (staff_num),
 staff_num INT(5) NOT NULL,
 first_name VARCHAR(100) NOT NULL,
 pens_in_drawer INT(2) NOT NULL,
 CONSTRAINT pens_in_drawer_range
 CHECK(pens_in_drawer BETWEEN 1 AND 99)
);

Stored procedures

• Name should be a verb, describing the action.
• Do not use prefixes as sp_xxx.

Universal suffixes

• _id, a unique identifier such as a column that is a primary key.
• _status, flag value or some other status of any type such as publication_status.
• _total, the total or sum of a collection of values.
• _num, denotes the field containing any kind of number.
• _name, signifies a name such as first_name.
• _seq, contains a contiguous sequence of values.
• _date, denotes a column that contains the date of something.
• _tally, a count.
• _size, the size of something such as a file size or clothing.
• _addr, an address for the record could be physical or intangible such as ip_addr.

Query syntax

• Always use uppercase letters for keywords.Example(SELECT not Select).
• Use full length keywords instead of abbreviations. Example(ABSOLUTE not ABS).
• Use space after commas, before and after equalsigns.
• Keywords should be on separate lines and aligned to read easier.
  • Example

SELECT species_name, AVG(height) AS average_height
FROM flora
WHERE species_name = 'Banksia'
OR species_name = 'Sheoak'
OR species_name = 'Wattle'
GROUP BY species_name, observation_date