Take the 2-minute tour ×

When making relations between tables (in mysql), I have encountered a naming dilemma.

For example, if I was creating a site where a project could be created by multiple users and also read by multiple users, to link a questions and user tables, I would potentially need two tables.




The problem here is that the two tables look identical excluding the table name. Probably a more useful representation would be




The problem here now is that authorId and readerId are actually just userIds, and the name does not reflect that, and could possibly misleadingly indicate that authorId and bidderId's are unique and different in their own right.

I am sure my example will have many holes in it, but I have been encountering this problem alot recently, so my question is what method do you use?

share|improve this question
Please don't say "relations between tables". The relations in a relational database are the tables themselves, not the connections between them with foreign keys. Some people are confused about this, so don't confuse them further! –  Thomas Padron-McCarthy Sep 1 '09 at 14:48
Constraint names can be severely hampered by restrictions imposed by the database, e.g. oracle's moronic 30 character identifier limit. –  skaffman Sep 1 '09 at 15:27

6 Answers 6

up vote 4 down vote accepted

What's wrong with author_userID and bidder_userID?

You have people playing roles, which is always a difficult design. You need to reflect the role as well as the underlying object playing that role.

share|improve this answer
In my opinion what's wrong with them is that they are long and cumbersome. I don't call a variable loop_array_index_integer, even if it happens to be an integer that is used in a loop to index an array! –  Thomas Padron-McCarthy Sep 1 '09 at 17:11
You don't say "loop_array_index_integer" because you don't have two roles for one foreign key. "loop_array_index_integer" can be spelled "i" without ambiguity because the type (integer) is obvious. With a database definition, the type (userID) and the qualifying role (author or bidder) is not obvious. –  S.Lott Sep 1 '09 at 18:48

I like to be really descriptive in my names, because more often then not they find their way into code as property names on some object. So in your case I would have




Then in code it makes a lot more sense when accessing the properties, like

myProjectAuthorEntity.authoredByUserId = someUserId;
myProjectBidderEntity.bidByUserId = someOtherUserId;
share|improve this answer

I prefer to keep the foreign key name identical to the primary key name when possible. This helps you quickly determine whether or not a column is a foreign key to another table and there is no ambiguity as to which table it references.


  • UserID (pk)


  • ProjectAuthorID (pk)
  • UserID (fk to tblUser)


  • ProjectBidderID (pk)
  • UserID (fk to tblUser)

In your queries, you can use prefixes to distinguish between which table's UserID you are referencing.

select author.UserID 
from tblProjectAuthor author 
left join tblUser user on user.UserID = author.UserID

The only problem we've experienced with this naming scheme is when you reference a foreign key multiple times in the same table. A good example would be a self-joining table. In cases like these, my only suggestion is to prefix with a meaningful word or phrase that helps distinguish while allowing you to recognize that the column is a foreign key.

For example:


  • EmployeeID (pk)
  • ManagerEmployeeID (fk to tblEmployee)
share|improve this answer

I would say:


where roleId links to a table that differentiates between author, bidder, etc. Positive effect - you can control with the choice of the composite primary key whether a user can be only one (author or bidder) or both. The former would mean a key over questionId, userId, the latter a key over all three fields.

Side note: Personally, I prefer staying in one naming scheme. Either I use everyhing_with_underscores, or I use camelCase/PascalCase, but not project_users and userId within the same database.

share|improve this answer
ok good call and thanks for the advice. The naming problem persists however. For example if every project has only a single author and so I include it in the project table. e.g. projects -------- projectId description userId is userId really an appropriate name here as it does not indicate inherently that the userId is the author of this project. –  zenna Sep 1 '09 at 14:45
I would go with AuthorUserId (I'm more with SQL Server, where PascalCase is stronger, FWIW). In any case consistency is key. Sortablity is nice, so UserIdAuthor would also be an option, depending on your proirities. In any case, a semantically useful name always wins over a radically strict name for me. –  Tomalak Sep 1 '09 at 14:50

When I can I use the exact name of the PK field I am linking to. However, occassionally I might need two references to the same id in the same table, then I would do:

Users UserID

Orders Customer_UserID SalesRep_UserID

That way you know the specific use of the ID as well as the actual ID name.

share|improve this answer

If you are asking just about the naming, I would use whatever naming scheme provides the most documentation inherently. I personally think that would be the first option. However, as long as you make sure whatever you decide is consistent and documented somehow, I think either will work fine.

However, have you thought about making more tables? Perhaps have a users table, which stores IDs and other user information, a projects table which stores projects, a bidders table which maps users that bid to projects, and an authors table which maps users to authored projects?

share|improve this answer
sorry perhaps I didn't make that clear, I do have a users and a projects table. The tables I wrote about are just to perform the many to many relationships, or the bidders and authors table as you suggested. –  zenna Sep 1 '09 at 14:40

Your Answer


