Naming foreign key columns 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?
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).