Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column naming convention Expand / Collapse
Author
Message
Posted Tuesday, May 22, 2012 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 115, Visits: 803
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
Posted Tuesday, May 22, 2012 11:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--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
Posted Tuesday, May 22, 2012 11:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1304359
Posted Tuesday, May 22, 2012 11:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Posted Tuesday, May 22, 2012 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 115, Visits: 803
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
Posted Wednesday, May 23, 2012 5:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
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
Posted Wednesday, May 23, 2012 11:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1305181
Posted Thursday, July 19, 2012 11:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 1,371, Visits: 1,562
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
Posted Thursday, July 19, 2012 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1332425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse