Naming Convention for Database Objects

random

Naming Convention for Database Objects

SQL Database Naming Convention

In this post I will explain about naming convention for database schema such as database table, field, procedure, function, primary key, constraint, Index. When doing database design you should keep the following convention or standards in your mind :-
DB Naming Convention
Table Name Convention

  • It should be in UpperCase.
  • It should not have spaces.
  • Multiple words should be split with underscore, since some of DB Client always shows DB name in uppercase, using case will not be good choice.
  • It should be plural (more than one in number) - For Example: EMPLOYEES Table, rather than EMPLOYEE. 
  • If it contains multiple words only last word should be plural. For Example: EMPLOYEE_PHOTOS
Field Name Convention
  • It should not have spaces
  • Multiple words should be split with underscore.
  • It should be singular. For Example: EMPLOYEE_ID column name, rather than EMPLOYEES_ID or EMPLOYEE_IDS.
Procedure Name Convention
  • Procedure name should be defined as TableName_ProcedureFunctionalityName. For Example: Employees_SelectAll,  Employees_Insert, Employees_Update, Employees_Delete.
  • If table name is too long, it is also better to use short name of table rather than full tablename prefix, For Example: Emp_SelectAll, Emp_Insert.
  • If table name contains multiple words like Employee_Locations then it is better to give name like EL_SelectAll, EL_Insert.
  • 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 BusinessLogicName_ProcedureFunctionalityName.
For Example: Procedure for employees quarterly sales report should be named something like Reports_Emp_Quaterly_Sales.  

That way you can combine all reports procedure together to easily find them in a complex database structure.

Function Name Convention
Function name are mostly generic utilities, but in case if they are associated with table, then follow procedure naming convention approach, else use meaningful name.
 
For Example:  AgeFromDOB  - If you pass a valid date, this function will return age, no. of years between current date and DOB.

Primary Key Convention
Primary key should be name as PK_TableName. 

For Example:  PK_Employees. If you are using SQL Server, whenever you are creating primary key in table designer, it will automatically follows above naming convention.

Foreign Key Convention
Foreign key should be name as FK_PrimaryTableName_ForeignTableName.

For Example: PK_Employees_Departments. If you are using SQL Server, whenever you are creating foreign key in table designer, it will automatically follows above naming convention.

Constraint Name Convention
Constraint name should be name as ConstraintShort_ConstraintColumnName.


For Example: Default value constraint for IsActive column field in employe table should be 1 (or true).  DF_IsActive. Here DF stands for Default value constraint and IsActive is column field in Employees Table.

Index Name Convention
Index name should be name with prefix idx_ColumnName.  

For Example: Idx_Employee_ID

Note: Remember, naming convention is to help finding things easily and a standard which can be easily explain to anyone joining a development team.


I hope you will enjoy the naming convention for database schema and about sql database naming convention. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome. 


Also If you like this article, don't forget to share this article with your friends and colleagues
Naming Convention for Database Objects Reviewed by Ravi Kumar on 4:26 PM Rating: 5

2 comments:

  1. Table names should ALWAYS be singular - plural names show a lack of understanding in the fundamentals of database design

    ReplyDelete
    Replies
    1. I do agree. Both have its advantages and disadvantages, and that everyone has their own opinion on this, and that the most important thing is that your naming conventions are consistent across the database since it would be annoying to remember which table names are plural and which are singular. I can therefore conclude that naming your database tables with Singular or Plural names is up to the developer.

      Delete

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form

Name

Email *

Message *

Powered by Blogger.