database standards for object naming conventions

Database standards and naming conventions are important for large technology departments and helps when a person moves from one project to another, if object names are consistent then he will take less time to know the purpose of each object. The object names should be descriptive enough to state the purpose, also if you have more than one database technology and moves object from one engine to another, your company might be using SQL Server, MySQL and Oracle in different departments then make sure you know the limitations of each database technology. SQL Server is the most flexible when naming objects, among SQL Server, Oracle and MySQL database servers mainly because it allow upto 128 character long table names.

There is no "one for all" naming standard for database objects, so make sure you use object names as per your company requirements, here are some which i use.

Database limitations by number of characters allowed in object name.

General
Keep them short and descriptive, you should not use space in your object name. Convert the white space to under scores ( - ) e.g Customer Orders to Customer_Orders.

Database Name
The Database name should define the application it is used for, if you are running all databases on single server that is production, QA and development then using QA, PROD, DEV is a good idea.

Schema Name
If you are using schema to group objects then use easy to understand schema names to define which objects are inside a particular schema. You can have multiple schemas in single database.

Table Names
Data table should define the purpose of table, as tables are most commonly used database objects. If you have a table which is dependent on another table then the dependent table name should define it e.g Orders table and Order_Details table.

You should also use plurual words like Customers instead of Customer, you can also use Customers or Customers_Info, try making the first letter capital and rest small to make it easily readable. Seperate multiple words with underscore.

Column Names
The column names should also be descriptive and easy to understand, same example of Customers table if you are storing customer last name then use Last_Name not LN or LName or L_Name. I have seen many from programing background using str, int in column names to define the data type but i personally feel that its way too descriptive, not a good idea, hard to read.

View Names
Since views are dependent on data tables then its a nice idea to use it in name but also name should define that its a view not a table. As an example you might have a view with customers and orders you can name it v_Customers_Orders this way its easy to recognize that its a view with ( V ) prefix and also defines which tables it is pulling the data from or the purpose of the view in our case its customers and orders.

Stored Procedures
The stored procedures should have prefix to distinguise them from tables, views and other database objects, in SQL Server you should NOT use sp_ to follow Microsoft standard as prefix because SQL Server engine will look for the stored procedure first in Master database which will increase the look time.

Stored procedures are usually attached with some kind of action and its a good idea to define that too in name, i usually use Get and Set. If my stored procedure inserts or updates data in customers table i use usp_Set_Customers or Set_Customers, same with stored procedure i use to fetch data usp_Get_Customers or Get_Customers.

Functions
The functions should also define the purpose, as an example you may have a function to return full name of customer, you can name it f_Customer_Name, func_Customer_Name, Get_Customer_Name or Customer_Name, this way your function will be easy to understand by name.

Index Names
Database Indexes are always dependent on some table, since they are special kind of objects, you should use a prefix in index name, Indexes can have multiple columns and each table can have multiple indexes so you can have some kind of numbering assigned to each index. As an example you can use IDX or IND on Customers table we can create first index as idx_Customers_1, ind_Customers_2, ind_Customers_3, in SQL Server you can also define idx_CL_Customers or ind_nonCL_Customers.

Database Constraints
We create constraints for data integrity purpose, each constraint is also dependent on a table so following our logic we should use the data table name in constraint name. When creating primary key constaint on table Customers you should use PK_Customers, if you are creating foreign key constraints on Orders table for Customer_ID then it should use primary table name followed by second table name FK_Customers_Orders, this will help to recognize that this foreign key constraint is dependent on Customers and Orders table. You can use CK prefix for check constraints, with unique constaints you can use UK, you can also use CC or UC the K is used to follow primary Key and foreign Key K's.

Here is a list of prefixes i have seen most widely used in my personal experience.

 5/19/2008

Copyright © 2010 SQLDBA.Org. Designed by WebSite4me

Puppies for sale | Kittens for sale | About | Contact