My question is the created_by, accepted_by, and closed_by are all user ID's but since the user_id is a fk of emp_id, should I associate them with emp_id, or user_id? Does it makes since to create a fk on another fk? Or should they just all point to the emp_id?
IMO, the crux of the matter is the relationship between Employees and Users. If the logical relationship is mandatory, i.e. every employee must be a user (and every user must be an employee as per the FK) then it doesn't matter which table you use as the reference. Having said that, if the fields are logically user IDs then you should reference user. That way if the relationship between employee and user changes in future it won't break the relationship with Issues.
If the logical relationship between Employees and Users is optional, i.e. if an employee does not have to be a user then the FK should clearly reference the User ID.
So the Users get my vote! Never thought I'd hear myself say that!
Well, in order to be a user of the system you have to be an employee. There are no exceptions to that and there never will be. The emp_id is a company issued number, not an incremented value in the DB that I made. So they are automatically unique.
I was thinking that if I used the user_id as the association I would be required to create an additional join in order to go ahead and pull the info I needed from the employee table. For example, if I need to display who accepted an issue, I would need information from the employee_table. The reason I used user_id as a FK to emp_id in the user_table was because I say no reason to duplicate firstname, lastname, email, etc. So if I were using user_id for the association in issue_table would I not have an additional lookup to get the information I needed?
Joined: Aug 02, 2004
If you have a foreign key where Users.user_id references emp_id and another where Issues.created_by references user_id then there is an implicit foreign key between emp_id and created_by, i.e. created by must be an emp_id.
Just because there is an FK relationship between tables doesn't mean that you have to include it in the query. You can join from Employees to Issues ignoring Users.
Having said all that I would suggest that, in practice, it's not worth putting foreign keys on the three user_id based columns on Issues due to the I/O overhead of checking the FK constraint each time you write a record.
An absence of foreign keys doesn't stop you joining on those columns either.