Alex Rivera | Logout

Primary key/foreign Key naming convention

Asked 2009-09-02T19:13:33.480
105

In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group:

1: 

Primary Table (Employee)   
Primary Key is called ID

Foreign table (Event)  
Foreign key is called EmployeeID

or

2: 

Primary Table (Employee)  
Primary Key is called EmployeeID

Foreign table (Event)  
Foreign key is called EmployeeID

I prefer not to duplicate the name of the table in any of the columns (So I prefer option 1 above). Conceptually, it is consistent with a lot of the recommended practices in other languages, where you don't use the name of the object in its property names. I think that naming the foreign key EmployeeID (or Employee_ID might be better) tells the reader that it is the ID column of the Employee Table.

Some others prefer option 2 where you name the primary key prefixed with the table name so that the column name is the same throughout the database. I see that point, but you now can not visually distinguish a primary key from a foreign key.

Also, I think it's redundant to have the table name in the column name, because if you think of the table as an entity and a column as a property or attribute of that entity, you think of it as the ID attribute of the Employee, not the EmployeeID attribute of an employee. I don't go an ask my coworker what his PersonAge or PersonGender is. I ask him what his Age is.

So like I said, it's a raging debate and we go on and on and on about it. I'm interested to get some new perspectives.

sql database-design naming-conventions
Edit
Report

2 Answers

1

I use convention #2. I'm working with a legacy data model now where I don't know what stands for in a given table. Where's the harm in being verbose?

answered 2009-09-02T19:28:45.010
1

"Where in "employee INNER JOIN order ON order.employee_id = employee.id" is there a need for additional qualification?".

There is no need for additional qualification because the qualification I talked of is already there.

"the reason that a business user refers to Order ID or Employee ID is to provide context, but at a dabase level you already have context because you are refereing to the table".

Pray, tell me, if the column is named 'ID', then how is that "refereing [sic] to the table" done exactly, unless by qualifying this reference to the ID column exactly in the way I talked of ?

answered 2009-09-03T21:07:33.130

Your Answer