Naming Convention in Tables, Stored Procedures, Views, and UDF's

Tue May 31, 2005 by Aldrich in microsoft-sql-server

I would like to share my standard naming practice in MSSQL. This naming convention wouldn't be only good for the eyes but its purpose mainly is to know what the SP,View, or UDF does by just looking its name, also to easily access all related objects.

Tables: Use a noun, instead of using an underscore just use Pascal Case Naming just like in .NET.

1. CustomerProfile - table for Customer's Profile

2. Customer - table for Customer

3. Account - table for accounts

Stored Procedure. In SP names I use noun + the purpose of the SP.

1. CustomerProfileInsProc - sp for inserting operations in CustomerProfile Table

2. CustomerProfileUpdProc - sp for updating operations

3. CustomerProfileDelProc - sp for deleting operations

4. CustomerProfileSelProc - sp for selecting operations

Views:

1. CustomerProfileListView - list of customer profile

2. CustomerAccountListView - list of customer accounts

In UDF use verbs + camel casing

1. getTotalCredit

2. getLastLogin

3. computeTotalProduction

Legacy Comments


Brett (Not just a Number...huh?) 2005-05-31	Unwieldy? Wouldn't you want to prefix your objects reather than suffix them?

Louis Davidson 2005-06-01	Why prefix or suffix at all with type I don't like to include the type of object in the name, even views. It is easy enough to get this information from the information_schema, and since it is not users who are doing the programming, they can figure it out by how they use them. I do put a break between the table name and the purpose of the procedure/function, but I try to treat views as tables, which they are for all intents and purposes. I use underscores for my data warehouse tables, since it makes it easier for a tool to parse, but camel case (start with lowercase letter) for OLTP systems. Naming is fun, and as long as you are consistent in any given database, your conventions are fine, even if I wouldn't use them :)

Alex Papadimoulis 2005-07-22	re: Naming Convention in Tables, Stored Procedures, Views, and UDF's You need to see ISO-11179 standards. Lots of people with lots of experience spent lots of time developing those. Surely they are better than the work of one person. It makes no sense to name your tables singularly. A table represents a set of entities, and should be named as such. Personel is better than Employees. Calling your table Employee makes sense if you'll have one and only one employee. As far as views, remember that a view is a table (a virtual table). A common theme in software engineering for the past 40 years has been "information hiding" -- the consumers of your database do not need to know that the table they are access is virtual or physical. Do you name your C# functions according to their accessors?