2

I have table users with columns id, username, email and password. And i have another table with relations to table users.

Some developers use prefix way (column id_user), some developers use postfix way (column user_id).

Which way is correct and why.

yAnTar
  • 4,269
  • 9
  • 47
  • 73
  • http://stackoverflow.com/questions/7662/database-table-and-column-naming-conventions – Alex Jun 27 '12 at 15:01
  • @Xander disagree. As the level of complexity of queries increases, you need your query to be readable so that you're not going back and trying to figure out what's being joined on what and to what. But I also don't view it as something so important that needs to be turned into a large, philosophical battle. – swasheck Jun 27 '12 at 15:11
  • @swasheck using proper aliases resolves the issue and yes i too am not into large philosophical battles ... – Alex Jun 27 '12 at 15:13
  • This is not an answerable question. There is no correct answer, it's all down to preference. The most common that *I* have seen is {tablename}_id. It's far from universal though. – Cylindric Jun 27 '12 at 15:36

3 Answers3

4

There is no correct way, it's just a matter of taste. I'd use the prefix over the postfix because it puts the more meaningful part of the name first, making it slightly faster to read (or so I imagine).

djc
  • 11,603
  • 5
  • 41
  • 54
4

Using ID is a SQL antipattern and should not be used. It is more likely to cause problems than using the tablename ID approach because people will use natural joins in dbs that allow them and join to the wrong id or in complex sql for reporting it is very easy to join to the wrong id (from a different table than you are joining to) where you would get a syntax error if you were to use tablename id. Plus, it makes more sense for PKs and FKs to have the same name. It makes is simpler to know exactly what you are to join on.

Using either IDTablename or tablenameID is a matter of choice. Of all the databases (thousands in my career) I have had access to, most seem to prefer tablenameId. Whatever the choice, be consistent through the database. It is very annoying to work with a databse where some tables use Id and some use tablenameId and some use tablename_id and some use Id_tablename and some use IDtablename. Pattern consistency is more important than any other factor in naming objects.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Thank you! I hate seeing "id" as a column in tables, where the proper column name should be more like _id or something like that. For clarity, a foreign key name should be the same as the primary key on the original table. – Gordon Linoff Jun 27 '12 at 18:10
0

For the record:

Im collecting information about naming convention here

https://medium.com/southprojects/database-naming-convention-the-definitive-guide-90424d6c2d36

And i found so far that user_id (or UserId if the database allows columns with case) is more used than id_user (IdUser). However, both are valid.

Also, Oracle used to say that the correct way is user_id_pk but i think that this ideology is obsolete.

magallanes
  • 6,583
  • 4
  • 54
  • 55
  • this medium.com link is down, the autor remove his article. i think this one is a good ref too : https://www.codecademy.com/learn/fscp-designing-relational-databases/modules/fscp-designing-a-database/cheatsheet – devseo Oct 05 '22 at 14:17