Data Wise Business Solutions

Naming can be very hard to standardize on as everyone has their own way of naming variables, columns, tables, and etc. Many times, there are way too many rules to remember. I am going to propose what I think is a consistent way of naming.

History: If you look at history of naming objects back to the mainframe days. There were physical constraints that designers had to live with. Today, you would never name a column FN (first name) as it is too cryptic. But, when storage was very expensive, it was done. Many systems had restrictions on number of characters. In the early days, the limit was up to 6. Even the first version of dBase had a limit of 8 characters. I remember one system that didn't use ASCII and they could save a bit or two by only having upper case letters.

Today, we really don't have physical constraints. Most systems allow 256 character names. Some even allow Unicode characters. Oracle is the only exception to the rule.

What do you think of these examples: FIRSTNAME, FIRST_NAME, firstname, first_name, First_Name, FirstName, firstName. How about these: FrstNm, FstNme, fn, FN, Fn. I am sure you have picked out your favorite.

Before we have a standard, we have to look at the development language. Is the language case sensitive? Java and c++ are. PHP and ASP are not. If the language is case sensitive, many developers like to have all of the names in lower case (firstname) or proper case (FirstName) or the modified proper case (firstName).

Should the development language drive the naming convention for databases? The answer is maybe. It might be easier to have a standard that will work across all development efforts.

What ever you decide to be your standard, just be consistent.

The Standard to Think About:
There are many articles on the web talk naming conventions and none will be the same.   However there is a few common attributes that are the same.   They are:  1) don't use underscore (_) to separate names and 2) don't use all upper case characters.   Everything else is open to personal preference.     

A Standard: In the following, we will have a logical and consistent discussion of standard naming convention.  

A few consistent rules:

• No underscores (_)
• No s at the end (i.e. Names).  Every database or table will hold more than one item, plural is assumed.  Name should be singular.  I.e.  Name table will hold names.
• No Abbreviations.   All modern database systems allow more characters for a name they you will ever use.   For example:  "nbr" and "no"  spell out number.   "Amt"  spell out amount.   
• Be consistent.  

Databases
What data does it hold?   What is the system name?   Name the database.  Remember the rules above.  

Tables

• Simple Name:   Use Proper case. (i.e.  Name,  Address,  ClientName, and etc)
• Singular naming -- No s at the end of the name.
• Avoid prefix or suffix of List, Data,  Info.   (i.e don't use NameList).  All tables are a list or data or some sort as their nature.
• For Junction tables (many to many) or Parent/Child tables (1 to many) use all names that define the relationship.   (i.e.  Order (parent), OrderDetail (child))

Columns

• Simple Name:   Use Proper Case (i.e. FirstName, LastName, and etc)
• DO NOT: have the table name part of the column name.   If the table is named Client, do not have a column named ClientCode.  It should just be Code.
• Foreign key Column names should identify the parent table name.  Using the Client example above.  It is ok to have ClientCode in an Invoice table.  
• For Primary Keys, use ID suffix.  The only inconsistency in naming will be this:   Don't just use ID as the column name.  Use the table name too. Example: OrderID.    The reason is simple and logical.  When writing sql code,  PK and FK (which are used all of the time) are is easier to read when you can type OrderDetail.OrderID = Order.OrderID compared OrderDetail.OrderID = Order.ID
• Don't use abbreviations.  
• For consistent data typing, here are some suggestions. 

* this data type you will see many different opinions. Some standards suggest prefixing the column name with Is (i.e. IsActive). Some use the word flag or bit. Recommend using a suffix.

There are several reasons you may want to create script to add indexes. If you have used SQL Server DTS application to transfer data from one database to another, the data will be transferred over, but not the indexes. If you used the enterprise manager (or management studio) to script a table create, indexes again will not be created. Want a brute force way of recreating all of your indexes? Read further.