Naming foreign key columns

Question

Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a foreign key that references the users table id column would be:

creatorUserId

Now the thing is that right now I am building my application and for the sake of development speed, I am going to be referencing everything by a surrogate foreign key (INT AUTO_INCREMENT). I really don't want to get into the whole which is faster than what in which case this early in development, I rather leave that stuff to the end (to try to prevent premature optimizations and I am more interested in working on the front-end of this application then the backend).

With that being said, a concern I have is if I in-fact want to change some of the foreign keys from the surrogate key to a natural key (all relavent tables will have both) and I use column names like

creatorUserId 

I am going to have to go through all the backend code in order to make the change, a process that would not be very pleasant.

I was thinking about doing something that just use the function and table name for foreign keys column like:

creatorUser

In the case, if I change what column I am using for the foreign key, the backend code changes are a lot simpler and there would be a lot less of them (if you are using an ORM which I am). Also, since surrogate keys are generally numbers and natural keys are generally alpha characters, it should still be relative easy to know which one is being used just by looking at the value.

Would this be considered bad practice?

Answer 1

The name "creatorUser" fails when there is a multi-column foreign key relationship.

As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.

Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".

My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).

Answer 2

Name the columns so they're understood in the context that they appear in, which is the table they reside in.

If you do that for each table, then the relationships will be obvious, as each column will reference a foreign column with a similar name.

If the columns referencing each other don't have a similar name, then ensure that you name the FK appropriately to make it immediately obvious what the relationship is between the columns.

It all ends up looking more readable when writing SQL (eg: table1.foo = table2.foo, rather than table1.foo = table2.table1foo).

Answer 3

Another take: if your table has a primary key simply called id, then a foreign key column referencing the primary key on the other table can use <tablename>_id. This advice is circulated in some articles for example https://www.navicat.com/en/company/aboutus/blog/2225-a-quick-guide-to-naming-conventions-in-sql-part-2