Column naming convention

  • Hi All,

    Looking for opinions on something. I've never seen this before, and I'm of mixed opinion on how I feel about it:

    When naming a column that is a foreign key to another table, prefix the related table name to the column name. Example:

    Table1

    Table1Id INT

    more columns

    Table2

    Table2Id INT

    Table1_Table1Id INT

    more columns

    What are your thoughts?

  • in our shop, all the identity columns contain the tablename + Id, so repeating the tablename would be repetitively redundant πŸ™‚

    so for example TBAddress would have an identity TBAddressID ;

    so all FK's that point to is are the exact same name :

    TBContact has a FK column named TBAddressID ;

    on the situations where we need multiple foreign keys, we make all our keys END WITH the actual column name,

    so MailAddressID,PersonalAddressId, and BusinessAddressID might co exist in the same table if we needed mulitple FK's.

    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!

  • I am a firm believer that any column should have a name that identifies it clearly and should not change between tables. Column names changing table to table is complete PITA to work with. The main reason I see for changing names like you are describing is due to poor naming of the original column.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I tend to be with Sean. I'd do this:

    Table1

    ID INT

    more columns

    Table2

    ID INT

    FKID INT

    where I'd name the FKID something that makes sense.

    Or more formally:

    Create table Customers (

    CustomerID int

    , CustomerName Varchar(20)

    )

    ;

    Create table Orders (

    OrderID int

    , CustomerID int

    , OrderDate datetime

    )

    ;

  • Ok, I'm going to blame the jet lag (35 hours to get from Christchurch, NZ to Boston this weekend) on not seeing the obvious. Yes, prefixing the table name onto the foreign key column is redundant if that column already is called <Object>Id (where <object> is Customer or Order or ...).

    This database was created while I was on vacation, from a model that the development team had been discussing before I left. Strangely, it appears to be the default naming convention that comes out of VisualStudio edmx. Yuck.

  • Piling on at this point, but that's one ugly naming convention so...

    No, I've never seen one like that and wouldn't recommend it. In fact, I'd fight against it, kicking, biting & gouging. A column should have a meaningful & clear name and it shouldn't change from table to table unless there's an overriding reason.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • In fact, I'd fight against it, kicking, biting & gouging.

    +1

    We do follow β€˜FK_<Child_Table>_<Parent_Table>_<Parent_Column(s)>’. It’s very much self-explanatory with a drawback of its length. We abbreviate the table & column names if the length is 30 or more.

  • Why to extend names of foreign keys. We can keep domain for both the tables same. Like this:

    Department

    =========

    Dept_ID (PK)

    DName

    Employee

    =======

    ID (PK)

    Name

    Dept_ID (FK)

    Here Dept_ID in Employee table is a foreign key to Dept_ID in Department table.

    Suggestions/Comments most welcomed.

    - Lokesh

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • I guess I was not clear on my part. β€˜FK_<Child_Table>_<Parent_Table>_<Parent_Column(s)>’ is Foreign Key (Constraint) Name. The FK columns borrow the same name as they have in parent tables.

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

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