Column naming convention

Question

Column naming convention

Tom Bakerman

Hall of Fame

Points: 3367
More actions
May 22, 2012 at 11:22 am

#261249

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?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

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

)

;

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 4

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.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

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.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 7

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

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.