5 ms·
For me one of the most practical benefits of singular table names is: not having to think about the primary/foreign key names, because you can always just assum
by TehShrike 3y ago
For me one of the most practical benefits of singular table names is: not having to think about the primary/foreign key names, because you can always just assume that it's "[table name]_id".
If you have a "users" table, you either have to name the column "users_id", which is incorrect (because per row it's the id of a single user), or you have to use mental cycles to de-pluralize the name (or even worse, try to remember whether you called it "users_id" or "user_id" on a case by case basis).
- bluefirebrand 3y agoIt 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 3y agoI 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 3y agoThat'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 3y agoIt’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 3y agoI believe the commenter is talking about a FK that exists elsewhere, like orders.user_id
- bluefirebrand 3y agoWhy 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 3y agoI 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 3y agoYes. 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 3y agoNow 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 3y agoOh noes, someone let his id run wild and down-modded a fact!
- euroderf 3y agoIf (for example) the USER table has a field USER_ID, it makes all those JOINs less verbose.
- ShadowBanThis01 3y agoNot really. A table presumably holds multiple of whatever the "thing" is, so one row (and its column names) can be assumed to be singular.
- replwoacause 3y agoMaybe I’m weird or thinking about this wrong, but it makes sense to me to pluralize the table “users”, because it is a collection of users, and when I’m naming the key it is the “user ID” because it is the ID that corresponds to a single user. I don’t have to do any mental gymnastics for this to make sense, it just seems natural to me. But again, maybe I’m thinking about this incorrectly and just don’t realize it.
- nemosaltat 3y agoalso, in mySQL at least, user is a keyword that messes with hinting. edit: a word