Database naming convention

Database projects generally benefit from a consistent use of a naming convention which makes it more readable and more easily scriptable when it comes to scripting the creation of database objects.

SQL syntax commands should be written in UPPERCASE to make the SQL more readable.

Database object names should generally avoid being all uppercase, and avoid using plurals (makes scripting harder), and avoid numerals, spaces and special characters, although underscore is often used when one wishes to avoid PascalCase style (as not all DBMS support PascalCase / CamelCase, for example, PostgreSQL) and just use traditional lower case only.

Where possible acronyms should be avoided and readable unambiguous names used instead as long as names are kept to less than 30 characters long.

If using acronyms,keep them consistent (all UPPERCASE, or camelCase or all lowercase) like URL / Url / url.

There is no need to prefix tablenames with tbl_, but one should prefix views with vw_ so that programmers don’t mistake a view for a table.

Consider prefixing tablenames with a scope identifier, for example, all tables that for part of a patient ED episode could be given a prefix of ED to ensure tables are displayed together, and also avoid conflicts with similarly named tables in other modules.

Consider prefixing lookup tables with the name of the table they relate to.

For a linking (or junction) table, concatenate the names of the two tables being linked in alphabetical order unless there is a natural order.

The primary key column should generally NOT have any business meaning but just be an integer value.

The primary key column of a table could be just called ID, although many prefer using an abbreviation of the tablename as a prefix for ALL columns in that table (but the benefits are reduced if one uses abbreviated table aliases in SQL statements), and thus the primary key would have id or ID or PK as the suffix.

Likewise a foreign key could the abbreviated lookup tablename followed by FK as the suffix.

Column names should not be SQL reserved words.

Column names should be unambiguous, for example, time as an integer value should be stated as Hours or Minutes, while a boolean field should be named IsPrinted to help. Timestamp columns should have the word Date or Time in their name. Functionally similar columns should be named consistently such as suffix of DateAdded for time stamping when the new record was created instead of DateLogged, DateOrdered, etc.

Records should never be deleted but have a column to mark it as inactive as deletions are expensive and not only do you lose data, but you may need to copy the data into a new table for audit trail purposes or to enable user to undo a deletion.

Ideally, primary keys should be created using a trigger.

Indexes can be named {TableName}{ColumnsIndexed}{U/N}{C/N} where U/N refer to unique or non-unique and C/N is for clustered or non-clustered.

Constraints can be named {constraint type}{table name}_{field name} where contraint type is an abbreviation of either Check (Ck), Referential Integrity / Foreign Key (Fk), Primary Key (Pk), or Unique (Un).