Using Hibernate with a link table
Hi.
I have a pretty complex problem and i'm not sure how to map it.
I have a standard tomcat User & UserRoles tables setup.
I also have what's going to be an event/class scheduler with the tables Event & EventInstructor
the schema looks like this:
---------- -------------------
| User | | User_Role |
---------- -------------------
| ID (int) | | ID (int) |
| Name | | user_id (int) |
| ... | | role (varchar) |
---------- -------------------
---------- -----------------------
| Event | | Event_Instructor |
---------- -----------------------
| ID (int) | | instructor_id (int) |
| Name | | event_id (int) |
| ... | -----------------------
----------
Problem #1:
How do i map the Event & User using the Event_Instrucotr table?
I've looked at some posts about many-to-many, but my problem is that and event may or may not have an instructor, but if it does it can only have 1.
Problem #2:
An instructor is a User who has the role "instructor" in the User_Role table.
Not sure how to get only these Users to map to the Event_Instructor table.
Again, and event may or may not have up to 1 instructor, but and instructor can have many events.
Can anyone help me map this?
Thanks!
- Jonathan
I have a pretty complex problem and i'm not sure how to map it.
I have a standard tomcat User & UserRoles tables setup.
I also have what's going to be an event/class scheduler with the tables Event & EventInstructor
the schema looks like this:
---------- -------------------
| User | | User_Role |
---------- -------------------
| ID (int) | | ID (int) |
| Name | | user_id (int) |
| ... | | role (varchar) |
---------- -------------------
---------- -----------------------
| Event | | Event_Instructor |
---------- -----------------------
| ID (int) | | instructor_id (int) |
| Name | | event_id (int) |
| ... | -----------------------
----------
Problem #1:
How do i map the Event & User using the Event_Instrucotr table?
I've looked at some posts about many-to-many, but my problem is that and event may or may not have an instructor, but if it does it can only have 1.
Problem #2:
An instructor is a User who has the role "instructor" in the User_Role table.
Not sure how to get only these Users to map to the Event_Instructor table.
Again, and event may or may not have up to 1 instructor, but and instructor can have many events.
Can anyone help me map this?
Thanks!
- Jonathan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How do i map the Event & User using the Event_Instrucotr table?
I've looked at some posts about many-to-many, but my problem is that and event may or may not have an instructor, but if it does it can only have 1.
I assume that INSTRUCTORID should have the value of USER id, which shall be implemented by defining INSTRUCTORID being a foreign key to userid (in table user). The value can be null, which indicates no instructor.
Problem #2:
An instructor is a User who has the role "instructor" in the User_Role table.
Not sure how to get only these Users to map to the Event_Instructor table.
IN RDBMS like SQL Server and Oracle, I would use contraints or triggers to validate this requirements. In MySQL, there are no triggers unless you use MaxDB by MySQL...
So unless you have a DBMS Version that allows triggers, you need to solve this on application level.
CHeers