Column naming convention

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Column naming convention

Column naming convention

Rate Topic

Display Mode

Topic Options

Author

Message

Tom Bakerman

Posted Tuesday, May 22, 2012 11:22 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:33 AM
Points: 76, Visits: 568

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?

Post #1304340

Lowell

Posted Tuesday, May 22, 2012 11:44 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 11,613, Visits: 27,666

in our shop, all the identity columns contain the tablename + Id, so repeating the tablename would be repetitively redundant Smile

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1304358

Sean Lange

Posted Tuesday, May 22, 2012 11:46 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 8,567, Visits: 8,218

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1304359

Steve Jones - SSC Editor

Posted Tuesday, May 22, 2012 11:59 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 2:54 PM
Points: 31,410, Visits: 13,726

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

Post #1304369

Tom Bakerman

Posted Tuesday, May 22, 2012 12:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:33 AM
Points: 76, Visits: 568

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.

Post #1304379

Grant Fritchey

Posted Wednesday, May 23, 2012 5:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,371, Visits: 25,151

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1304853

Dev

Posted Wednesday, May 23, 2012 11:07 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

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.

~Dev~

Post #1305181

Lokesh Vij

Posted Thursday, July 19, 2012 11:15 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 1,171, Visits: 1,246

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

Post #1332396

Dev

Posted Thursday, July 19, 2012 11:37 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

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.

~Dev~

Post #1332425

« Prev Topic | Next Topic »

Permissions