Naming convention of foreign keys
database, database-design, naming-conventions, sql
Solution
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.
Problem
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?