1

I use user IDs a lot in tables, and I would like to find out when naming these columns, what's the best way to go about naming them. Should you be specific to what the user ID is, or should you keep using the same column name(s) to keep it consistent?

So for example:

Column names for subscriptions

user_subscriber_id = the user ID that made the subscription.

user_subscribed_to_id = the user ID that was subscribed to.

Column names for comments

sender_id = the user ID that made the comment.

reply_to_id = the user ID of the comment replied to.

Or should I always use user_id, and in the case where there needs to be 2 user IDs in a table (like above), use user_id and secondary_user_id, where the user_id should always be the user that does an action, and secondary_user_id should always be the user ID of the receiver, so:

Column names for subscriptions

user_id = the user ID that made the subscription.

secondary_user_id = the user ID that was subscribed to.

Column names for comments

user_id = the user ID that made the comment.

secondary_user_id = the user ID of the comment replied to.

2

Community wiki answer:

I think naming the column as fieldtype_fielddescription, i.e. user_id_additional_info, is more safe.

For example, not user_subscribed_to_id = the user ID that was subscribed to, but user_id_subscribed_to.

It allows, for example, to select the fields packet from information_schema if needed, using a simple LIKE 'user_id_*' pattern.

  • That's a good point because I was thinking about using the LIKE 'user_id_*' pattern if I ever needed it in future, which is why I thought keeping a consistent user_id and secondary_user_id. user_id_additional_info looks right to me, or the other way around additional_info_user_id. – Andrew S Jun 7 '18 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.