Naming primary keys “id” vs “something_id” in SQL

Question

I'm wondering what the best practice is for naming primary/unique keys in a database with many tables. Should you always just call each table's primary key id or is ok to not have an id field in each table and just name each of them something1_id, something2_id, etc?

Answer 1

It is a matter of preference; however, there is an advantage to using (something)ID in that column names become less ambiguous when you are doing joins between tables.

Answer 2

It's personal preference. At the end of the day it makes absolutely no difference. I personally prefer using just Id as I find referring to the field (let's say Customer table and Id field) Customer.CustomerId to be cumbersome, where Customer.Id seems to make more sense.

Answer 3

Whatever you do, pick one or the other and stick to that standard. There are pros and cons for each.

I prefer SomethingID but other people prefer just ID. In the system I work with there are well over a thousand tables and having the PK and the FK have the exact same names makes things easier.

Answer 4

id is just a convenient convention - you can call the field anything as long as you declare it as a key field.

Prefixing the id with a relevant context can be helpful when it comes to reading and writing your code, and especially improves readability when you are joining data from multiple tables and foreign keys.

Answer 5

I'd suggest you use an abbreviation for long names and use (something)Id (like user_id) for regular tables. So for the table customer_company_relation_metadata use ccm_id. But its fine to use id as well