4 ms·

It really should just be "id" You don't need to prefix your table column names with the name of the table. users.user_id should just be users.id Etc.

heleninboodler 4y ago
I believe GP is talking about how you refer to this column in another table, where "id" is not an option. In the "widgets" table, you need to refer to either "the id for the user" or "the id to use in the users table" so both user_id and users_id are perfectly reasonable choices if the other table is called "users".
- bluefirebrand 4y ago
  That's fair. I honestly did not think about foreign key column names when I was typing, so thanks for pointing it out. I'm always a fan of making foreign key column names more descriptive than just the table name. Widgets may have a user_id but it probably makes more sense to have an owner_id.
  - henriquez 4y ago
    It’s still a good practice to refer explicitly to the foreign reference in the column name, so in your example owner_user_id would be preferable to owner_id if the foreign key refers to a record in the user table.
chromatin 4y ago
I believe the commenter is talking about a FK that exists elsewhere, like orders.user_id
- bluefirebrand 4y ago
  Why not something like orders.users_fk? Then you know exactly what that column is there for. It was intended to be a foreign key for the users table. I think "orders.user_id" is fine too of course, I'm not going to argue against that. But isn't it just the tiniest bit more ambiguous? Edit: There's probably also always better names than just the table name with id, too. For example orders might have more than one fk to the users table, depending on the type of site. Something like ebay might have both buyer_id and seller_id on a single order.
seandoe 4y ago
I disagree. Table_id is more descriptive and easier to use when joining -- no need to alias if you're selecting multiple table keys, can use USING for joins, etc
- yobbo 4y ago
  Yes. Large queries, many tables, many FKs will make it easier to see why these conventions exist. Patterns like "users JOIN things USING (user_id) JOIN stuff USING (user_id, thing_id) ...".
ShadowBanThis01 4y ago
Now let's start a debate over the use of "id" when it should be "ID"! An id is a different thing from an ID. Look up "id, ego, superego."
- ShadowBanThis01 4y ago
  Oh noes, someone let his id run wild and down-modded a fact!
euroderf 4y ago
If (for example) the USER table has a field USER_ID, it makes all those JOINs less verbose.

heleninboodler 4y ago

I believe GP is talking about how you refer to this column in another table, where "id" is not an option. In the "widgets" table, you need to refer to either "the id for the user" or "the id to use in the users table" so both user_id and users_id are perfectly reasonable choices if the other table is called "users".

bluefirebrand 4y ago
That's fair. I honestly did not think about foreign key column names when I was typing, so thanks for pointing it out. I'm always a fan of making foreign key column names more descriptive than just the table name. Widgets may have a user_id but it probably makes more sense to have an owner_id.
- henriquez 4y ago
  It’s still a good practice to refer explicitly to the foreign reference in the column name, so in your example owner_user_id would be preferable to owner_id if the foreign key refers to a record in the user table.

chromatin 4y ago

I believe the commenter is talking about a FK that exists elsewhere, like orders.user_id

bluefirebrand 4y ago
Why not something like orders.users_fk? Then you know exactly what that column is there for. It was intended to be a foreign key for the users table. I think "orders.user_id" is fine too of course, I'm not going to argue against that. But isn't it just the tiniest bit more ambiguous? Edit: There's probably also always better names than just the table name with id, too. For example orders might have more than one fk to the users table, depending on the type of site. Something like ebay might have both buyer_id and seller_id on a single order.

seandoe 4y ago

I disagree. Table_id is more descriptive and easier to use when joining -- no need to alias if you're selecting multiple table keys, can use USING for joins, etc

yobbo 4y ago
Yes. Large queries, many tables, many FKs will make it easier to see why these conventions exist. Patterns like "users JOIN things USING (user_id) JOIN stuff USING (user_id, thing_id) ...".

ShadowBanThis01 4y ago

Now let's start a debate over the use of "id" when it should be "ID"! An id is a different thing from an ID. Look up "id, ego, superego."

ShadowBanThis01 4y ago
Oh noes, someone let his id run wild and down-modded a fact!

euroderf 4y ago

If (for example) the USER table has a field USER_ID, it makes all those JOINs less verbose.