mysql column naming conventions for foreign keys

Question

Lately I've been thinking about what's a better name for foreign keys, either user_id, or id_user style.

Personally I think id_user style is better for handling within database (db queries) where it's clear from the first three characters that the column is containing ID.

On the other hand I find user_id style more suitable e.g. with use of HTML autocomplete fields where there are two input fields; one name="user" for text, the other name="user_id" for his ID; which makes it easier to recognize that they belong to each other because they both start their name by defining what the content will be about.

What do you think?

• S.Visser: I should use user.
• Class: I would have to say this question is off topic. See FAQ
• silentboy: Different cms use different convention. Use what suits your best
• user1822: @S.Visser: user is a reserved word and should not be used as column (or table) name.

Answer 1

I prefer user_id but I think it doesn't really matter as long as you stay consistent and not mix them up so you keep your overview structured and readable.

Answer 2

As @randomizer correctly suggested, don't mix it, choose one - that is the stone rule.

I would suggest:

If you will name all columns with prefix e.g. user_columnName , do it with id too.

If I would use id_user I would avoid postfixing and named other self-columns without post/prefix.

I personally like using ID everywhere, where is some primary-ID and id_* elsewhere where it's referenced, because that way it is clear that ID is the primary id of the current table and id_*s are foreign keys to IDs in *s .