posted 19 years ago
Hi Georg,
Okay. I think I have gone through the same confusion you are facing and here is what I have confirmed,
1. when we have CMR then there are two possibilities in the relation we have.
- 1-to-N
- M-to-N as we know already.
Now, in 1-to-N relationship we don't usually have "separate relationship table". e.g.
Person-Address relationship where one person can have many addresses. Here in the Address table we would store FK to Person table right?
Thus Address table itself holds the "relationship field".
In M-to-N we must have a separate relationship table.
e.g. Team-Player where A player can play for many teams and one team has many players. We would have,
TeamPlayer relationship table where we store TeamID,PlayerID which both are FK to Team and Player tables PK correspondingly..
Now, lets see what happens when we use CMR methods in each of the above case s,
1. IN Person-Address relation,
------------------------------
Logical schema for these entities,
Person(SSN,FName,LName, PK(SSN));
Address(SSN, AddrId, Street, State, Zip, PK(AddrId), FK(SSN) Person(SSN));
(Observe that in the Address->SSN we have not specified it as NOT NULL)
Bean code
----------
Person per = personHome.findByPrimaryKey("000-00-0000");
Address addr = addressHome.create("id1","street","state","zip"); // Line 1
per.getAddresses().add(addr); // Line 2
At Line-1 an Address entity is created which means there is a row in the database table for Address and it looks like,
('',id1,street,state,zip);
Observe that there still the SSN column which is FK in the Address table is empty yet. Of course this would be allowed "only if we have FK allowing NULL in db schema". This means the address just added to the table doesn't really know where (to which person) it belongs.
At Line-2 we say that I want to add the created address to the person with given SSN. This will essentially update the SSN FK in the Address table to be "000-00-0000".
One very important point
------------------------
Here, as you have observed that as we have SSN FK allowing NULL in Address it all works. If you have overlapping PK/FK meaning-- Address KEY = (SSN+AddrId) then the above example won't work. Why?? Because, to create Address we MUST provide SSN and hence the Line-2 call of per.getAddresses().add(addr) fail as it won't be able to update the PK in Address. Actually, we won't need this LIne-2 anymore in this case as we already created Address with "all required" fields with "relationship fields".
2. In Team-Player relation
---------------------------
Consider the following schema,
- Team(teamId,teamName, PK(teamId));
- Player(playerId,playerName, PK(playerId));
- TeamPlayer (teamId,playerId, FK(playerId) Player(playerId), FK(teamId) Team(teamId));
Here we have separate relationship table TeamPlayer.
Now, consider the following code,
Team t = teamHome.create("team1","team1");
Player p = playerHome.create("player1","player1");
So far it inserts two rows in two tables- Team and Player correspondingly BUT still at this point we have not established relationship between the team and the player created. So the third table is empty so far.
When we write,
t.getPlayers().add(p);
then it inserts row in the third table putting TeamId,PlayerId from team and player bean objects.
I guess this should help a little.
Feel free to ask more questions if you got confused by this explanation.
Regards
Maulin