Naming convention of foreign keys

Question

Hello there

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.

**project_authors**
questionId
userId

and

**project_bidders**
questionId
userId

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

project_authors
questionId
authorId

and

project_bidders
questionId
bidderID

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?

Answer 1

I would say:

project_users
-------------
questionId
userId
roleId

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.

Answer 2

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.

Answer 3

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?

Answer 4

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.

Answer 5

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.

tblUser

• UserID (pk)

tblProjectAuthor

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

tblProjectBidder

• 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:

tblEmployee

• EmployeeID (pk)
• ManagerEmployeeID (fk to tblEmployee)

Answer 6

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

project_authors
questionId
authoredByUserId

and

project_bidders
questionId
bidByUserId

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

myProjectAuthorEntity.authoredByUserId = someUserId;
myProjectBidderEntity.bidByUserId = someOtherUserId;