This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Relationship Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Relationship Question" Watch "Relationship Question" New topic
Author

Relationship Question

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I have the following psuedo tables

employee_table
emp_id
firstname
lastname
department

user_table
user_id (fk, user_id == employee_table.emp_id)
password
role

issue_table
issue_id
created_by
accepted_by
closed_by
...

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?

I hope that made sense. Thanks.


GenRocket - Experts at Building Test Data
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Gregg,

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!

Jules
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

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?
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Relationship Question
 
Similar Threads
hibernate hello world
Hibernate primary Key/User defined Generator class
querry involving multiple tables.
Question on Sets of Objects
recursive query problem