locked
SQL Server Naming Convention RRS feed

Answers

  • No, there isn't a naming convention like that published by Microsoft, and if it was very few people would like it.  They make good tools, but their utilization of the tools can be... interesting.

    Naming is a highly charged topic, and there are several standards out there.  I use a naming standard based on the Idef1x modeling style. Table names represent a single instance of what the table is modeling, so it would be SalesOrder, not SalesOrders, and not SalesOrderHistory (unless it only contained old sales orders) and certainly not TableSalesOrder or Fred39.

    I use PascalCasing for OLTP databases (not camel casing, which too many people don't like because it starts with a lowercase :), and underscore seperated names for my BI databases.

    For columns, I try to limit prefixing columns with the table name unless they will be referenced in another table, or it feels natural. Like the account table will have an accountNumber, not just a number.  Like I wouldn't name all of the surrogate keys ID, nor would I have Table Person, PersonFirstName, PersonLastName, PersonThis, PersonThat

    The name should be as descriptive as possible. Using very few abbreviations in names unless it is a very well known abbreviation (like Id instead of Identifier).

    Columns are ended with a "class" word that tells the general purpose. Like %name says it is some form of identifier for the row, %code says it is an abbreviation/semi-cryptic value that users use; Time is a point in time like 2010/1/1 12:00 AM.  Date is just a day in time, so the 24 hour period where  you are, rather than a specific 24 hour period regardless of location.

    Mostly NO specific  typing NameVarchar32, or ModifyDateTimeOffset16.  And no ColumnName, ColumnDescription, TablePerson. If the person can't discriminate what the name is from the metadata and context, they probably shouldn't have and software using the database (especially not SSMS) installed on their machine

    Just my additional $ .02.  Most important thing is to be consistent and FOLLOW WHATEVER STANDARD is in force.  You never want to have to figure out what the names will be:

    create table Person
    (
          PerId int
          First_name varchar(20),
          LastName  varchar(20),
          CustomerIdentifierInt int
    )

    and so on.  This is really frustrating, but  as new "architects" add to the building, they often try to apply their own "style", but in the end, all you have is a freaking mess.

    I hope this helps a bit, or at least you maybe get a little bit of and idea to add to your conventions.
    Louis

    Saturday, March 13, 2010 10:47 AM

  • I have seen lot of database structures so far where the column name remains same in all the tables related to each other to avoid confusions and to mainain consistency, it also avoids confusion  during joins.
    Make it a practice, not to make id as "optional" but make EstablishmentId (TableNameID)"compulsary" 
    Chase Excellence - Success Will Follow!

    Yes, that is certainly desirable, but not always possible. Example follows with double FK to the same PK.

    BOL 2008:

    Sales(RequestedDate, OrderedProductID, ReplacementProductID, Qty)

    Product(ProductID, ProductName,Category)

     

    Relation: Sales.OrderedProductID -> Product.ProductID

    Relation: Sales.ReplacementProductID -> Product.ProductID



    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016





    Saturday, February 6, 2010 12:03 PM
  • The problem is, if you use Product instead of Name it doesn't make any sense out of contact.  Product.Product?  I would definitely consider using ProductName as the column name if you really want to avoid aliasing, but I generally prefer aliasing in the query rather than trying to come up with perfect names that require no aliasing.

    This is actually the one place that I generally don't follow the IDEF1X standards which require all column names to be unique, which does lead to names like ProductName, ProductDescription, ProductCode, ProductAdNauseum.  I will say that I am don't "hate" your standard completely, as one of my previous attempts at coming up with a standard would have suggested that the classword of "name" be a default. But in the end having names that had less ambiguity seemed better. I would personally have guessed by name alone that your Product.Product column would be a code type column.

    This was really the point of my long reply, that names need to be understandable, and follow some standard.  If a developer was adding to the database without knowledge of the standard they certainly might add a Name column because it isn't obvious and the Product column may have some "purpose" that isn't readily obvious (and like most architects they sneak stuff past you and try to get you to agree to the changes after all of the programming has been done)
    Louis

    Monday, March 15, 2010 4:16 AM
  • CamelCase is one such standard that Microsoft likes to use.  http://en.wikipedia.org/wiki/CamelCase

    For writing queries, I prefer left-aligned like so: http://www.ssistalk.com/2009/03/12/whats-your-sql-formatting-style/


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, February 2, 2010 4:14 PM
  • Just curious - Do most people use their table names in each field name? I hate making columns like "Person.PersonNameFirst" and "Person.PersonNameLast" but it does help a bit when referencing them.

    I would not do that.

    Exception, when it is common English usage like ProductName & ProductLine in the Product table. Other attributes I would not prefix: Price, Cost, Color, Size, etc.

    AdventureWorks table & column naming is very good.

    With good naming, you increase your own and fellow database developers productivity.  Your goal is to make your design easy to "read" by a competent database programmer.



    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016









    Wednesday, February 3, 2010 9:19 PM
  • Let's simplify the things a little bit: Open AdventureWork database spend 1-2 hour looking and analyzing what is there and you'll find a good standard - naming convention.


    Dan Vilnoiu
    Sunday, March 14, 2010 9:30 AM
  • Following the naming styles of AdventureWorks sample database is definitely a good way to go.

    One thing I would have done different is not use "Name" for entity's name if there is commonly accepted English word for it like Product, Subcategory & Category. In the following demo, the result returned, though not acceptable for reporting, and it would even fail a SELECT INTO.

    -- RDBMS Database Design Naming Convention 
    USE AdventureWorks2008; 
    
    SELECT   c.Name, 
             s.Name, 
             p.Name, 
             ListPrice 
    FROM     Production.Product p 
             INNER JOIN Production.ProductSubcategory s 
               ON p.ProductSubcategoryID = s.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory c 
               ON s.ProductCategoryID = c.ProductCategoryID 
    WHERE    ListPrice > 0 
             AND p.ProductSubcategoryID IS NOT NULL 
    ORDER BY c.Name, 
             s.Name, 
             p.Name 
    /*
    Name	Name	Name	ListPrice
    Accessories	Bike Racks	Hitch Rack - 4-Bike	120.00
    Accessories	Bike Stands	All-Purpose Bike Stand	159.00
    Accessories	Bottles and Cages	Mountain Bottle Cage	9.99
    Accessories	Bottles and Cages	Road Bottle Cage	8.99
    Accessories	Bottles and Cages	Water Bottle - 30 oz.	4.99
    ...

    The naming choices forces you to use column aliases:

    -- T-SQL using column aliases 
    USE AdventureWorks2008; 
    
    SELECT   Category		=c.Name, 
             Subcategory	=s.Name, 
             Product		=p.Name, 
             ListPrice 
    FROM     Production.Product p 
             INNER JOIN Production.ProductSubcategory s 
               ON p.ProductSubcategoryID = s.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory c 
               ON s.ProductCategoryID = c.ProductCategoryID 
    WHERE    ListPrice > 0 
             AND p.ProductSubcategoryID IS NOT NULL 
    ORDER BY Category, 
             Subcategory, 
             Product 
    /*
    Category	Subcategory	Product	ListPrice
    Accessories	Bike Racks	Hitch Rack - 4-Bike	120.00
    Accessories	Bike Stands	All-Purpose Bike Stand	159.00
    Accessories	Bottles and Cages	Mountain Bottle Cage	9.99
    Accessories	Bottles and Cages	Road Bottle Cage	8.99
    ....
    */

    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016







    Sunday, March 14, 2010 7:19 PM
  • The problem is, if you use Product instead of Name it doesn't make any sense out of contact.  Product.Product? 

    The point is though that tables are typically used aliased if human writes the code so it is:

    p.Name vs p.ProductName

    s.Name vs. s.Subcategory

    c.Name vs. c.Category

    These are not "accidental" JOINs, in Data Warehousing/OLAP called dimensional hierarchy, in fact an OLAP cube hierarchy element: Category--> Subcategory --> Product.

    The triple "Name" usage forces Query Designer to assign column aliases: Expr1 & Expr2, thus requiring additonal search/replace correction for usefulness and readability. The second round of fixing would not be required if the naming was ProductName, Subcategory and Category respectively.

    -- Code from query designer - beautified
    SELECT   Production.ProductCategory.Name, 
             Production.ProductSubcategory.Name AS Expr1, 
             Production.Product.Name            AS Expr2 
    FROM     Production.Product 
             INNER JOIN Production.ProductSubcategory 
               ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory 
               ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID 
    WHERE    (Production.Product.ProductSubcategoryID IS NOT NULL) 
             AND (Production.Product.ListPrice > 0) 
    ORDER BY Production.ProductCategory.Name,
             Expr1, 
             Expr2

    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016






    Monday, March 15, 2010 4:50 AM

All replies

  • T-SQL identifier naming conventions : http://msdn.microsoft.com/en-us/library/ms175874.aspx 
    Ranjith | My Blog
    Friday, January 29, 2010 1:30 PM
  • Thanks Ranjith but this isn't what I was looking for. I was looking for rules on capitalization of table names and fields. I was also looking for a standardized way of writing queries.

    Tuesday, February 2, 2010 4:06 PM
  • CamelCase is one such standard that Microsoft likes to use.  http://en.wikipedia.org/wiki/CamelCase

    For writing queries, I prefer left-aligned like so: http://www.ssistalk.com/2009/03/12/whats-your-sql-formatting-style/


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, February 2, 2010 4:14 PM
  • Yes, I really like it.
    Thanks Phil Brammer...

     

    Thanks,

    Sandeep

     

    Wednesday, February 3, 2010 7:43 AM
  • Just curious - Do most people use their table names in each field name? I hate making columns like "Person.PersonNameFirst" and "Person.PersonNameLast" but it does help a bit when referencing them.
    • Proposed as answer by DCova Tuesday, March 9, 2010 9:10 PM
    • Unproposed as answer by Louis DavidsonMVP Saturday, March 13, 2010 10:29 AM
    Wednesday, February 3, 2010 8:01 PM
  • Just curious - Do most people use their table names in each field name? I hate making columns like "Person.PersonNameFirst" and "Person.PersonNameLast" but it does help a bit when referencing them.

    I would not do that.

    Exception, when it is common English usage like ProductName & ProductLine in the Product table. Other attributes I would not prefix: Price, Cost, Color, Size, etc.

    AdventureWorks table & column naming is very good.

    With good naming, you increase your own and fellow database developers productivity.  Your goal is to make your design easy to "read" by a competent database programmer.



    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016









    Wednesday, February 3, 2010 9:19 PM
  • I tend to go both ways.  There is no reason not to be verbose, and naming a column PersonFirstName creates readability when supporting existing code.

    So for me it depends on the situation and corporate standards.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Wednesday, February 3, 2010 9:46 PM
  • Just curious - Do most people use their table names in each field name? I hate making columns like "Person.PersonNameFirst" and "Person.PersonNameLast" but it does help a bit when referencing them.

    If you have same column in more than 1 table, you have to use the full schema name to avoid ambiguos name errors
    Chase Excellence - Success Will Follow!
    Friday, February 5, 2010 11:08 AM
  • I prefer this explanation of casing http://msdn.microsoft.com/en-us/library/ms229043.aspx to the one from Wikipedia. I like that it identifies the difference between Pascal and Camel casing right at the beginning.

    I like your left-aligned query format. The only change I would make would be the use of square brackets as you never know if one of your field names may become a reserved word in the next SQL release.

    It seems many people have their own ways. It would be nice if there was an official standard, maybe there is and I just can't find it.

    James
    Friday, February 5, 2010 12:51 PM
  • I try not to prefix but sometimes it just happens without thinking. The main one that catches me is the Id or the Key. There is a good chance you will have more than one key in a table. Let's say the Individual table links to the Establishment table by the Establishment ID. In the Establishment table the field would just be "Id" but in the Individual table the field would be called "EstablishmentId". I think the link is clearer if they are both called "EstablishmentId". If that makes sense, what would you use?

    James
    Friday, February 5, 2010 1:03 PM
  •  I think the link is clearer if they are both called "EstablishmentId". If that makes sense, what would you use?

    James

    I have seen lot of database structures so far where the column name remains same in all the tables related to each other to avoid confusions and to mainain consistency, it also avoids confusion  during joins.
    Make it a practice, not to make id as "optional" but make EstablishmentId (TableNameID)"compulsary" 
    Chase Excellence - Success Will Follow!
    Saturday, February 6, 2010 9:54 AM

  • I have seen lot of database structures so far where the column name remains same in all the tables related to each other to avoid confusions and to mainain consistency, it also avoids confusion  during joins.
    Make it a practice, not to make id as "optional" but make EstablishmentId (TableNameID)"compulsary" 
    Chase Excellence - Success Will Follow!

    Yes, that is certainly desirable, but not always possible. Example follows with double FK to the same PK.

    BOL 2008:

    Sales(RequestedDate, OrderedProductID, ReplacementProductID, Qty)

    Product(ProductID, ProductName,Category)

     

    Relation: Sales.OrderedProductID -> Product.ProductID

    Relation: Sales.ReplacementProductID -> Product.ProductID



    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016





    Saturday, February 6, 2010 12:03 PM
  • Yes, that is certainly desirable, but not always possible.
    There is always an exception to every rule. Thanks Kalman, I hadn't thought of that situation.


    Thanks for all the responses to my original post. He is another scenario which has been bugging me and you maybe able to give me the "correct" procedure.

    Product(ProductId, Type, Wheels, EngineSize)

    If I now want to add another field called Doors, which only applies to one Type of product should I add the field to the same table with a default value of Null or should I create a new table linked by ProductId and Type? The new table could also have additional fields in the future.

    Thanks,

    James
    Tuesday, February 9, 2010 12:59 PM
  • In rare cases do I include the table name as part of the field name. The times I do include them is when I create a field name such as id.  Then, I may create the field name something like personId or carId or houseId.  If I desire that the SQL code be descriptive and verbose to avoid ambiguity, I can always prefix the field name with the table name. There is nothing to stop me from doing that.

    Tuesday, March 9, 2010 9:11 PM
  • No, there isn't a naming convention like that published by Microsoft, and if it was very few people would like it.  They make good tools, but their utilization of the tools can be... interesting.

    Naming is a highly charged topic, and there are several standards out there.  I use a naming standard based on the Idef1x modeling style. Table names represent a single instance of what the table is modeling, so it would be SalesOrder, not SalesOrders, and not SalesOrderHistory (unless it only contained old sales orders) and certainly not TableSalesOrder or Fred39.

    I use PascalCasing for OLTP databases (not camel casing, which too many people don't like because it starts with a lowercase :), and underscore seperated names for my BI databases.

    For columns, I try to limit prefixing columns with the table name unless they will be referenced in another table, or it feels natural. Like the account table will have an accountNumber, not just a number.  Like I wouldn't name all of the surrogate keys ID, nor would I have Table Person, PersonFirstName, PersonLastName, PersonThis, PersonThat

    The name should be as descriptive as possible. Using very few abbreviations in names unless it is a very well known abbreviation (like Id instead of Identifier).

    Columns are ended with a "class" word that tells the general purpose. Like %name says it is some form of identifier for the row, %code says it is an abbreviation/semi-cryptic value that users use; Time is a point in time like 2010/1/1 12:00 AM.  Date is just a day in time, so the 24 hour period where  you are, rather than a specific 24 hour period regardless of location.

    Mostly NO specific  typing NameVarchar32, or ModifyDateTimeOffset16.  And no ColumnName, ColumnDescription, TablePerson. If the person can't discriminate what the name is from the metadata and context, they probably shouldn't have and software using the database (especially not SSMS) installed on their machine

    Just my additional $ .02.  Most important thing is to be consistent and FOLLOW WHATEVER STANDARD is in force.  You never want to have to figure out what the names will be:

    create table Person
    (
          PerId int
          First_name varchar(20),
          LastName  varchar(20),
          CustomerIdentifierInt int
    )

    and so on.  This is really frustrating, but  as new "architects" add to the building, they often try to apply their own "style", but in the end, all you have is a freaking mess.

    I hope this helps a bit, or at least you maybe get a little bit of and idea to add to your conventions.
    Louis

    Saturday, March 13, 2010 10:47 AM
  • Let's simplify the things a little bit: Open AdventureWork database spend 1-2 hour looking and analyzing what is there and you'll find a good standard - naming convention.


    Dan Vilnoiu
    Sunday, March 14, 2010 9:30 AM
  • I would agree that AdventureWorks is a decent example of names...data structures not so much, but the names are pretty good examples of how to name things (as well as using schemas for providing logical separation).


    Louis

    Sunday, March 14, 2010 7:10 PM
  • Following the naming styles of AdventureWorks sample database is definitely a good way to go.

    One thing I would have done different is not use "Name" for entity's name if there is commonly accepted English word for it like Product, Subcategory & Category. In the following demo, the result returned, though not acceptable for reporting, and it would even fail a SELECT INTO.

    -- RDBMS Database Design Naming Convention 
    USE AdventureWorks2008; 
    
    SELECT   c.Name, 
             s.Name, 
             p.Name, 
             ListPrice 
    FROM     Production.Product p 
             INNER JOIN Production.ProductSubcategory s 
               ON p.ProductSubcategoryID = s.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory c 
               ON s.ProductCategoryID = c.ProductCategoryID 
    WHERE    ListPrice > 0 
             AND p.ProductSubcategoryID IS NOT NULL 
    ORDER BY c.Name, 
             s.Name, 
             p.Name 
    /*
    Name	Name	Name	ListPrice
    Accessories	Bike Racks	Hitch Rack - 4-Bike	120.00
    Accessories	Bike Stands	All-Purpose Bike Stand	159.00
    Accessories	Bottles and Cages	Mountain Bottle Cage	9.99
    Accessories	Bottles and Cages	Road Bottle Cage	8.99
    Accessories	Bottles and Cages	Water Bottle - 30 oz.	4.99
    ...

    The naming choices forces you to use column aliases:

    -- T-SQL using column aliases 
    USE AdventureWorks2008; 
    
    SELECT   Category		=c.Name, 
             Subcategory	=s.Name, 
             Product		=p.Name, 
             ListPrice 
    FROM     Production.Product p 
             INNER JOIN Production.ProductSubcategory s 
               ON p.ProductSubcategoryID = s.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory c 
               ON s.ProductCategoryID = c.ProductCategoryID 
    WHERE    ListPrice > 0 
             AND p.ProductSubcategoryID IS NOT NULL 
    ORDER BY Category, 
             Subcategory, 
             Product 
    /*
    Category	Subcategory	Product	ListPrice
    Accessories	Bike Racks	Hitch Rack - 4-Bike	120.00
    Accessories	Bike Stands	All-Purpose Bike Stand	159.00
    Accessories	Bottles and Cages	Mountain Bottle Cage	9.99
    Accessories	Bottles and Cages	Road Bottle Cage	8.99
    ....
    */

    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016







    Sunday, March 14, 2010 7:19 PM
  • See this link (and links from it) for Celko naming conventions:

    http://metadata-standards.org/11179/#11179-5

    (from ISO-11179 Naming Conventions )
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, March 15, 2010 12:47 AM
    Answerer
  • The problem is, if you use Product instead of Name it doesn't make any sense out of contact.  Product.Product?  I would definitely consider using ProductName as the column name if you really want to avoid aliasing, but I generally prefer aliasing in the query rather than trying to come up with perfect names that require no aliasing.

    This is actually the one place that I generally don't follow the IDEF1X standards which require all column names to be unique, which does lead to names like ProductName, ProductDescription, ProductCode, ProductAdNauseum.  I will say that I am don't "hate" your standard completely, as one of my previous attempts at coming up with a standard would have suggested that the classword of "name" be a default. But in the end having names that had less ambiguity seemed better. I would personally have guessed by name alone that your Product.Product column would be a code type column.

    This was really the point of my long reply, that names need to be understandable, and follow some standard.  If a developer was adding to the database without knowledge of the standard they certainly might add a Name column because it isn't obvious and the Product column may have some "purpose" that isn't readily obvious (and like most architects they sneak stuff past you and try to get you to agree to the changes after all of the programming has been done)
    Louis

    Monday, March 15, 2010 4:16 AM
  • The problem is, if you use Product instead of Name it doesn't make any sense out of contact.  Product.Product? 

    The point is though that tables are typically used aliased if human writes the code so it is:

    p.Name vs p.ProductName

    s.Name vs. s.Subcategory

    c.Name vs. c.Category

    These are not "accidental" JOINs, in Data Warehousing/OLAP called dimensional hierarchy, in fact an OLAP cube hierarchy element: Category--> Subcategory --> Product.

    The triple "Name" usage forces Query Designer to assign column aliases: Expr1 & Expr2, thus requiring additonal search/replace correction for usefulness and readability. The second round of fixing would not be required if the naming was ProductName, Subcategory and Category respectively.

    -- Code from query designer - beautified
    SELECT   Production.ProductCategory.Name, 
             Production.ProductSubcategory.Name AS Expr1, 
             Production.Product.Name            AS Expr2 
    FROM     Production.Product 
             INNER JOIN Production.ProductSubcategory 
               ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID 
             INNER JOIN Production.ProductCategory 
               ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID 
    WHERE    (Production.Product.ProductSubcategoryID IS NOT NULL) 
             AND (Production.Product.ListPrice > 0) 
    ORDER BY Production.ProductCategory.Name,
             Expr1, 
             Expr2

    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016






    Monday, March 15, 2010 4:50 AM