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).
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.
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".
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.
orders.user_id
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.
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) ...".
An id is a different thing from an ID. Look up "id, ego, superego."