Database Naming Conventions by Microsoft

Database Naming Conventions by Microsoft?

The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.

To summarize:

• Object names are easily understood
• Table names are not pluralized
  ("User" table not "Users")
• Abbreviations are few, but allowed
  (i.e. Qty, Amt, etc.)
• PascalCase used exclusively with the
  exception of certain column names
  (i.e. rowguid)
• No underscores
• Certain keywords are allowed (i.e.
  Name)
• Stored procedures are prefaced with
  "usp"
• Functions are prefaced with "ufn"

You can find more details here:

• AdventureWorks Data Dictionary
• Stored Procedures in
  AdventureWorks
• Functions in AdventureWorks

One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."

Database, Table and Column Naming Conventions?

I recommend checking out Microsoft's SQL Server sample databases:
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

Microsoft or other sources of guidelines for naming SQL objects

There is no official microsoft documentation for best practices for sql naming conventions and that is the reason you find so many conventions for sql. Pick one convention and make sure its followed in your project. Here's an example

SQL Server schema naming convention

I found this reference for SQL Server Name Conventions

SQL Server Name Convention and T-SQL Programming Style

It says to use lowercase and no plurals in schemas

Naming conventions for type tables: to use a 'Type' suffix or not

I would define the table as:

create table Statuses (
    statusId int,
    code varchar(10),
    name varchar(10),
    description varchar(25)
);

Why?

• "status" is an entity. I name tables (generally) in the plural of the entity. Plurals are handy for two reasons. First, the table contains multiple instances, so plural makes sense. Second, plurals are less likely to conflict with SQL keywords/reserved words.
• The identity is statusId or status_id. That is, the entity name followed by "id". Most references would use the same name. This makes joins obvious.
• I see no reason to separate this as a "different" type of entity from other entities. If I did, I would use a prefix. For instance, I might use the cust prefix for entities specifically about customers.

What is your naming convention for stored procedures?

For my last project i used usp_[Action][Object][Process] so for example, usp_AddProduct or usp_GetProductList, usp_GetProductDetail. However now the database is at 700 procedures plus, it becomes a lot harder to find all procedures on a specific object. For example i now have to search 50 odd Add procedures for the Product add, and 50 odd for the Get etc.

Because of this in my new application I'm planning on grouping procedure names by object, I'm also dropping the usp as I feel it is somewhat redundant, other than to tell me its a procedure, something I can deduct from the name of the procedure itself.

The new format is as follows

[App]_[Object]_[Action][Process]

App_Tags_AddTag
App_Tags_AddTagRelations
App_Product_Add 
App_Product_GetList
App_Product_GetSingle

It helps to group things for easier finding later, especially if there are a large amount of sprocs.

Regarding where more than one object is used, I find that most instances have a primary and secondary object, so the primary object is used in the normal instance, and the secondary is refered to in the process section, for example App_Product_AddAttribute.

Naming Database Tables and Views

Consistency is the best approach. Adding a _TABLE or _VIEW at the end of an object name is overkill in my book, but if the database is designed that way, I wouldn't break from convention.

For your colleague to bring his naming convention from a previous organization into a new one without checking 'local' standards is bad practice.

C# naming conventions for acronyms

There is a convention, and it specifies initial uppercase, the rest lowercase, for all acronyms that are more than 2 characters long. Hence HttpContext and ClientID.

---

Related Topics