Foreign key naming convention
When creating relationships between tables (in mysql), I ran into 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 questions and custom tables, I would potentially need two tables.
**project_authors**
questionId
userId
and
**project_bidders**
questionId
userId
The problem is that the two tables look the same except for the table name. Probably a more useful view would be
project_authors
questionId
authorId
and
project_bidders
questionId
bidderID
Now the problem is that authorId and readerId are actually just userIds, and that name doesn't reflect that and can be misleading to indicate that authorId and consumerId are unique and different on their own.
I'm sure my example will have a lot of holes in it, but I ran into this problem a lot recently, so my question is which method are you using?
source to share
I would say:
project_users ------------- questionId userId roleId
where roleId
links to a table that distinguishes between author, bidder, etc. Positive effect - you can control with the choice of a composite primary key whether there can be only one user (author or bidder) or both. The first will mean the key above questionId, userId
, the last - the key in all three fields.
Side note: Personally, I prefer to stay in the same naming scheme. Either I use everyhing_with_underscores
or use camelCase
/ PascalCase
, but did not project_users
, and userId
within the same database.
source to share
When can I use the exact name of the PK field that I am binding to. However, sometimes I may need two references to the same ID in the same table, then I would do:
Users UserID
Orders Customer_UserID SalesRep_UserID
This way you know the specific usage of the identifier as well as the actual name of the identifier.
source to share
If you're only asking about naming, I would use any naming scheme that mostly provides the documentation itself. I personally believe that this will be the first option. However, as long as you make sure that whatever you decide is consistent and documented in some way, I think it will work fine.
However, have you considered creating more tables? Perhaps there is a table users
that stores IDs and other user information, a table projects
that stores projects, a table bidders
that displays users participating in projects, and a table authors
that maps users to authoring projects
source to share
I prefer that the foreign key name is identical to the primary key name whenever possible. This will help you quickly determine if a column is a foreign key to another table, and there is no ambiguity as to which table it belongs to.
tblUser
- UserID (pk)
tblProjectAuthor
- ProjectAuthorID (pk)
- UserID (fk to tblUser)
tblProjectBidder
- ProjectBidderID (pk)
- UserID (fk to tblUser)
You can use prefixes in your queries to distinguish which UserID table you are referring to.
select author.UserID
from tblProjectAuthor author
left join tblUser user on user.UserID = author.UserID
The only problem we encountered with this naming scheme is when you refer to a foreign key multiple times in the same table. A self-join table is a good example. In such cases, my only suggestion is a meaningful word or phrase prefix, which helps differentiate by allowing you to recognize that the column is a foreign key.
For example:
tblEmployee
- EmployeeID (pk)
- ManagerEmployeeID (fk to tblEmployee)
source to share
I like to be actually descriptive in my names, because more often than not they don't end up in the code as property names on some object. So in your case I would
project_authors
questionId
authoredByUserId
and
project_bidders
questionId
bidByUserId
Then in code it makes much more sense when accessing properties like
myProjectAuthorEntity.authoredByUserId = someUserId;
myProjectBidderEntity.bidByUserId = someOtherUserId;
source to share