Standard Naming Conventions for SQL Database Design – Cybarlab

Naming Con

ventions is an important factors for any type of database design. It’s benefits are high. It makes a simple database generic for others. This article describes what standard should we follow when naming database, tables, columns, stored procedures etc. Summary of the article:

• What is Naming Convention for Database?
• Standard Naming Convention for Tables
• Standard Naming Convention for Fields/Columns
• Standard Naming Convention for Views
• Standard Naming Convention for Stored Procedures or SP
• Standard Naming Convention for Functions
• Standard Naming Convention for Triggers
• Standard Naming Convention for Indexes
• Standard Naming Convention for Primary Keys
• Standard Naming Convention for Foreign Keys

What is Naming Convention for Database?
In programming language naming convention have great benefits to reduce the effort needed to read and understand source code. It provides better understanding in case of reuse code after a long interval of time. It is an initial step for beginner to learn any programming language. It is a very important element.

There exist so many different naming conventions for database objects, none of them is wrong. It’s more of a personal preference of the person who designed the naming convention. However, in an organization, should have one naming convention standard and everybody should follow it.

Standard Naming Convention for Tables
The standard naming convention for tables are as follows:

• It should be in Pascal Case.
• It should not have Spaces.
• Multiple words should not be split with Underscore (use StudentPhotos not Student_Photos).
• It should be Plural (more than one in number) – Example: Students,  rather than Student. If it contains multiple words only last word should be plural.  Example: StudentPhotos.
• If your database deals with different logical functions and you want to group your tables according to the logical group they belong to, use  two or three character prefix that can identify the group. For example, your database has tables which store information about Sales and Human resource departments, you could name all your tables related to Sales department as: SlCustomes, SlOrders. You could name all your tables related to Human resources department as like: HrCandidates, HrInterviewSchedules.

Standard Naming Convention for Fields/Columns
The standard naming convention for Fields or Columns are as follows:

• It should not have Spaces.
• Multiple words should not be split with Underscore.
• It should be Singular – Example: StudentId column name, rather than StudentsId or StudentIds.

Standard Naming Convention for Views
The standard naming convention for views are as follows:

• Views not always represent a single entity. A view can be a combination of two or more tables based on a join condition, thus, effectively representing two entities. In this case, consider combining the names of both the base tables. Example: A view combining two tables ‘Students’ and ‘Addresses’, name the view as ”StudentsAddresses”
• Try to avoid using spaces in view name.

Standard Naming Convention for Stored Procedures or SP
The standard naming convention for stored procedures are as follows:

• Never prefix your stored procedures with ‘sp_’. If you use it then, MS SQL Server first searches the SP in master database, if not found then search current database.Procedure name should be defined as table name + procedure functionality name. Example: StudentsSelectAll,  StudentsInsert, StudentsUpdate, StudentsDelete.  If table name is too long, it is also better to use short name of table rather than full table name prefix, Example: EmpSelectAll, EmpInsert.  If table name contains multiple words like StudentLocations then it is better to give name like SlSelectAll, SlInsert.  If short name are getting duplicate, then you can change of one of short name to avoid duplication or confusion.
• If you are creating procedure which is general in nature or combines 2 or more tables or mainly business logic which cannot be associated with any table, then it is better to use as business logic name + procedure functionality name.  Example:  procedure for students quarterly sales report should be named something like ReportsStdQuaterlySales.
• Always try to use such a name which describes the whole functionality of the procedure.

Standard Naming Convention for Functions
The standard naming convention for functions are as follows:

• Function name are mostly generic utilities, but incase if they are associated with table, then follow procedure naming convention, else use meaningful name.  Example:  CelsiusToFahrenheit  –If you pass Celsius temperature it will return Fahrenheit temperature.

Standard Naming Convention for Triggers
The standard naming convention for triggers are as follows:

• Trigger always depend on base table. So try to use table name with trigger name.
• Triggers are associated with one or more of the following operations: Insert, Update, Delete. So, the name of the trigger should reflect its nature. Example: StudentsInsTrg, StudentsUpdTrg, StudentsDelTrg.
• If you have a single trigger for more than one action (same trigger for insert and update or update and delete or any such combination), use the words ‘ins’, ‘upd’, ‘del’ together in the name of the trigger. Here’s an example. Example: StudentsInsUpdTrg.

Standard Naming Convention for Indexes
The standard naming convention for indexes are as follows:

• Index name should be name with prefix idx_ColumnName. Example: Idx_Student_Id.

Standard Naming Convention for Primary Keys
The standard naming convention for primary keys are as follows:

• Primary key should be name as PK_TableName.  Example:  PK_Students.

Standard Naming Convention for Foreign Keys
The standard naming convention for foreign keys are as follows:

• Foreign key should be name as FK_PrimaryTableName_ForeignTableName. Example:  PK_Students_Departments.

That’s all about standard naming convention of database objects.