Post reply

What is the column naming convention when creating columns in a table?

  • June 3, 2009 at 6:09 pm

    #130928

    Hi,

    This may be a dummy question for DBA fundass...

    What are the common standards that we need to follow while naming a column in a table or naming a Table itself. ( atleast we should not use special chars that I'm aware of )

    In detail...

    COLUMN Name

    UPDT_TYPE_CD

  • June 3, 2009 at 6:22 pm

    #1004363

    everyone is different, and you should use what is best for you. but here's some of the rules in out shop:

    1. the primary key of each table is TableName+"ID". (one of our systems uses TableName + "TblKey", though)

    2. if the column is a foreign key, it is the same name as the column in the parent table it references. So a table named Customers has CustomerID as it's PK, and the table Invoices, which has a relation to Customers, would have InvoiceId as it's PK, and CustomerID as an FK to Customers.

    3. column names should be less than 30 characters if possible, but the name must describe it's purpose. Other programmers after you might not know what the column means otherwise.

    4. no underscores in the names, we prefer CamelCase.

    5. minimize abbreviations, except for DT or AMT, for date or Amount, "InsertedDT" and "UpdatedDT" are a good column names but "insdt" or "uptdt" is not descriptive enough for a new programmer to infer the columns purpose. at our shop we want columns that deal with currency to end in "Amt", and dates to end in DT;

    6. that makes it easy to review since every PK or FK would end in ID, dates in DT and Decimals for Currency in Amt.

    7. No column names that are reserved words, or have spaces in them which would force us to bracket them with [Column Name]; when translating column names from other resources, remove the spaces and CameCase them instead.

    Table Names follow the same rules above, with emphasis on names that Describe the purpose; views always start with "VW_" in our shop though, so it's obvious to the programmer that

    select * from Customers

    and

    Select * from VW_Customers

    are different.

    That's all I can think of, others may have more suggestions.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2009 at 8:14 pm

    #1004385

    Lowell (6/3/2009)

    2. if the column is a foreign key, it is the same name as the column in the parent table it references. So a table named Customers has CustomerID as it's PK, and the table Invoices, which has a relation to Customers, would have InvoiceId as it's PK, and CustomerID as an FK to Customers.

    One thing we do differently here...

    IF (and only IF) the table has multiple FKs to the same table, then we use descriptive names for each field in the child table.

    IE: a Customer table has a CustomerID column

    A Subscription table has a ShippingCustomerID column and a BillingCustomerID column (though still keeping the original column name as the suffix).

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2009 at 8:39 pm

    #1004391

    good point Wayne; we do the same...when we get multiple FK's in a table as well.

    One of the things to point out is how standards like this can help you cross check your data design.

    it makes it very easy to do stuff like the following:

    1.for every column like '%ID', make sure it is either a PK or FK by joining to sysforeignkeys; for any column that ends in "ID", you can make sure you are not missing a FK from it's table definition. I've got a canned script called "missing and implied foreign keys" just for that purpose.

    2. every table has the identity as the first column in the table...easy to search and make sure the isIdentity is set in the schema, or that the PK includes that column.(the PK is not necessarily the identity, but often is.)

    3. search syscolumns and confirm that every column that ends in "DT" is of type datetime.

    4. search syscolumns and confirm that every column that ends in "AMT" is decimal(24,4) or whatever your shop uses to make sure you don't have too small of values.

    5. similar to #1 above, you can make sure that indexes exist on the FK columns to improve performance in joins.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 4, 2009 at 7:20 am

    #1004609

    Except when dealing with Oracle structures & data, we actually try to stay away from odd ball abbreviations. It's so much easier to understand what 'DeductibleRate' is versus 'DdltblRt.' Our company went through a period where everything was supposed to have an "approved" abbreviation. It lead to things like Ddltbl for Deductible. Notice the abbreviation inserted an extra L. It made me insane. I finally wore them down. There's nothing wrong with using standard, known, in the dictionary, type of abbreviations, Org for Organization, ID for Identifier. You just need to be consistent in their application.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply