SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Column naming convention


Column naming convention

Author
Message
Tom Bakerman
Tom Bakerman
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 930
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?
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29166 Visits: 39985
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26934 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65397 Visits: 19118
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
)
;



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Tom Bakerman
Tom Bakerman
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 930
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
Grant Fritchey
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42055 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dev
Dev
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3614 Visits: 1602
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
Lokesh Vij
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2110 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Dev
Dev
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3614 Visits: 1602
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search