Our database naming conventions

A messy database is a real nightmare. Modeling a schema is easy; everybody knows that. But creating a model that is both complete and simple – that’s hard. There are libraries full of books written to help you decide your approach. Good luck.

[Simple is the Best Design]

But naming conventions in a database are very important. Poorly named objects overcomplicate queries, burden developers, and make everyone hate you forever.

Here are our conventions for database objects:

.Net development has rightly stopped using Hungarian notation (prefixing objects with type indicators like strName and dteBirth). But for databases, prefixes still serve a vital role. 

Like .Net development, our overarching rule is to avoid abbreviations. Never abbreviate, with three exceptions:

1. 1) if the full word is ridiculously long (like MiscellaneousAttributes could be MiscAttributes), or
2. 2) if the full word is commonly misspelled (like MassachusettsUsers could be MassUsers), or
3. 3) if the abbreviation is more recognizable than the full word (like KentuckyFriedChicken could be KFC).

I understand you may have your preferences. These are ours. With them, I can guess within 99% of exactly what objects are just by seeing their case. Cool.

Note: every object is prefixed except the database itself. We never do anything like dbDatabase because that’s just stupid.

Prefixes (all prefixes are lower case):

Example	Standard	What is it?
tb_Users	tb_ prefix	Table
UserName	Pascal Case	Column
up_User_Insert	up_ prefix	Stored Procedure
v_Users	v_ prefix	View
fn_UpdateUsers	fn_ prefix	User Function
syn_Users	syn_ prefix	SQL Synonym
idx_Users_001	idx_ prefix	Index
@userName	@ + Camel Case	func, proc Parameter
@UserName	@ + Pascal Case	Local variable
LINK2Database	LINK2 prefix	Linked Server

Additional rules:

1. Tables are always plural (tb_Users, never tb_User)
2. Columns are in Pascal Case (UserId, FirstName)
3. Primary Keys are single columns whenever possible
4. Primary Keys are named with the table (tb_Users.UserId)
5. Primary Keys end with Id (UserId, not Key or Pk)
6. Foreign Keys end with Fk (UserFk, never Id, Key or Pk)
7. Stored Procedures never use the sp_ because this is a known SQL Server performance problem
8. Stored Procedures are named up_NOUN_VERB such as up_User_Insert or up_User_Search or up_User_Delete, not up_Delete_User or up_DeleteUser or up_UserDelete.
9. Triggers are named tr_TABLE_ACTION such as tb_Users_UpdateSecurity, not tb_UpdateUserSecurity
10. Index names don’t really matter. But if we want to conform them we use idx_TABLE_TYPE_COLUMNS like idx_Users_Clustered_LastNameFirstName. If the number of columns is too long, then idx_Users_Clustered_001

There are special rules for cross reference tables.

1. Names should include the parent table’s name
2. Names should include the static term “cross”
3. Names should include the child table’s name
4. They always have a primary key called CrossId
5. Many to One = tb_Groups_cross_User (in all reality, this should never happen – use One to Many).
6. One to One = tb_User_cross_Group (singular child)
7. One to Many = tb_User_cross_Groups (plural child)
8. Many to Many = tb_Users_cross_Groups (all plural)

An example schema would be:

dbo.tb_Users
dbo.tb_Users.UserId int primary key
dbo.tb_Users.FirstName varchar(50) not null
dbo.tb_Users.LastName varchar(50) not null

dbo.tb_Groups
dbo.tb_Groups.GroupId int primary key
dbo.tb_Groups.Name varchar(50) not null

dbo.tb_User_cross_Groups
dbo.tb_User_cross_Groups.CrossId int primary key
dbo.tb_User_cross_Groups.UserFk int
dbo.tb_User_cross_Groups.GroupFk int

Optional idea. One thing we have enjoyed is grouping similar objects together with a preceding keyword. Something like tb_Configuration_SecuritySettings and tb_Configuration_General. This helps them to be near each other in the UI – which can help users understand dependencies. The reason this is optional is because of the prevalent use of schemas which accomplish the same thing. So, you can decide how you want to do it. Note that there is no special naming standard for schemas.

I more vigorously defend database naming standards than I do .Net code standards. Both are important. But databases seem to live forever and code comes and goes. We all share the database, while code is often isolated. It’s better to be a jerk about the database, I have found. When you aren’t everyone eventually will suffer – including the customer.