1

I have read that it is bad practice to use naming convention id on primary key. Is this the case or not. Some people online have mentioned it can lead to mistakes when creating joins. Other people have said as long as you are consistent either or is ok- so i could use id to denote id of a property or property_id as long as i am consistent.

Wondering you thoughts/ options people 1) either property_id or id ok for primary key as long as consistent across the board. 2) should use property_id to primary key to avoid making mistakes on joins 3) best to use id- can see which side is primary key on joins and prefix id uneccessary

Matthew Chambers
  • 869
  • 3
  • 20
  • 34
  • I'd like to know where you read it. It is a very common practice. Ruby on Rails, for example, relies heavily on it. It only causes problems when joining if you carelessly `SELECT *` all the time instead of being specific. – Michael Berkowski Aug 12 '12 at 13:29
  • http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables second to last comment – Matthew Chambers Aug 12 '12 at 13:31
  • That answer has 0 upvotes. Just sayin... – Michael Berkowski Aug 12 '12 at 13:32
  • All the problems that using `id` as PK can cause are solved by being explicit in your `SELECT` lists, and by assigning column aliases when name conflicts would arise. You should be in habit of doing those things as good practice anyway. – Michael Berkowski Aug 12 '12 at 13:36
  • @Michael: It's not an answer, it's the second to last comment to the accepted answer. – ypercubeᵀᴹ Aug 12 '12 at 13:37
  • @ypercube Sorry - the last answer says the same thing and that's what I found... – Michael Berkowski Aug 12 '12 at 13:39
  • @Matthew: There are different opinions on this: See the asnwer by PerformanceDBA here: [Relational table naming convention](http://stackoverflow.com/questions/4702728/relational-table-naming-convention) and a short blog-post by Aaron Bertrand: [What's in a name?](https://sqlblog.org/2010/08/22/whats-in-a-name) Some frameworks, like Rails, have limitations. But what all agree is to be consistent. – ypercubeᵀᴹ Aug 12 '12 at 13:47
  • possible duplicate of [Primary key/foreign Key naming convention](http://stackoverflow.com/questions/1369593/primary-key-foreign-key-naming-convention) – ypercubeᵀᴹ Aug 12 '12 at 14:01

1 Answers1

1

I think id is ok. The only legitimate argument against it is in situations like this:

select * from foo, bar where id = 'blah'  # id is ambiguous

In general, when you select from multiple tables, you should qualify every column anyway:

select * from foo, bar where foo.id = 'blah' # no ambiguity

In addition, I think it's better than using model_id for the primary key, since it's customary to use that syntax for foreign keys.

Tanzeeb Khalili
  • 7,324
  • 2
  • 23
  • 27