Naming Conventions in SQL

Why is a naming convention required in SQL? The short answer is READABILITY.

A naming convention is a set of rules that you decide on before you begin modelling your database. These rules will be applied when naming anything within the database, including tables, columns, primary, and foreign keys, stored procedures, user-defined functions, views, etc.

In other words, the primary reason for using a naming convention for database objects is to help everyone identify the type and purpose of all objects in the database. It reduces the amount of effort required to read and understand source code after a long period of time. Most of the interfaces sort the database objects in alphabetical order hence the naming convention helps keep the objects that are similar remain in sequence and reduces the time it takes to find a specific object in a database.

• The names of dynamic tables should begin with “tb” or “tbl,” while static tables (lookup tables) should begin with “stb”.
• For the views, it should start with “vw” or “v” or “vw_”.
• It is “usp” for user-defined stored procedures and “udf” for functions to indicate that it is a user-defined function.
• The columns should indicate the data type with which it is associated. If the column “salary” is mapped to decimal, for example, the column name should be dcSalary.
• For INT data types, if the column is Integer then it should start with “i”.
  For example: iQuantity, iFileID, iStudentID, iProductID, etc.
  Similarly, for Big INT, it should start with “bi”.
  For example: biTrackID, biTicketID, biTokenID, etc.
• For character data types, if the column is CHAR, then it should start with “c”.
  For example: cFlag, cStatus, etc.
  Similarly, for VARCHAR, it should be “vc”.
  Example: vcBusinessName, vcStudentName, vcProductDescription, vcNotes, etc.
• For BIT data type columns, use “b” as the starting letter.
  For example: “bPaid”, “bisSucceeded”, etc.
• For primary-keys use “PK_” along with table name and column name.
  Example: PK_tbStudents_iStudentID.
  And for foreign keys use “FK_”, along with the source table name and target table name.
  For example: FK_tbEmployee_tbDept.
  Similarly for unique indexes use “unq”.
  For example: “idx_unq_tbEmployee_iPhone”.
• For default constraints, use “DF” as a prefix along with the table name and column name. Example: DF_tbEmployee_dcCommission.
  For Check constraints, it is “CK”.

Most experts follow these, but the main purpose of the naming convention is to maintain readability, so different naming conventions can be used.

Hope you find this article helpful.