associative table or a directly foreign key,what is better?

[avihaimar]

Hey,

I am working with hibernate and i want to map my Objects to tables structre.

There is two ways to map relation:
1. with associative table
2. directly foreign key (i mean without associative table)

Can you please explain what are the advantages for each way?


Thank you

 

[rac2]

I think this may depend on the cardinality of the relationship.

If table A is one to one with table B. Then you can have a foreign key column in either table. I dont know that it matters which table gets the foreign key column.

SELECT tblA.*
FROM tblA 
JOIN tblB ON tblB.b_id = tblA.b_id

SELECT tblA.*
FROM tblA 
JOIN tblB ON tblB.a_id = tblA.a_id

If table A may be one to zero with table B, then a foreign key column in table A might be more efficient. Because you could check in table A for a NULL value to determine whether there is a corresponding row in table B. Of course, if you only look at the tables together in a JOIN, that would be
irrelevant.

SELECT tblA.*
FROM tblA 
LEFT JOIN tblB ON tblB.b_id = tblA.b_id
/*Sometimes tblA.b_id will be NULL*/

SELECT tblA.*
FROM tblA 
LEFT JOIN tblB ON tblB.a_id = tblA.a_id
/*Sometimes there will not be a row with tblB.a_id = tblA.a_id*/

/*To check existence of a row in tblB with a_id */
/*If the foreign key is in tblA*/
SELECT *
FROM tblA
WHERE tblA.b_id IS NOT NULL

/*If the foreign key is in tblB*/
SELECT *
FROM tblA
WHERE EXISTS (SELECT * FROM tblB WHERE tblB.a_id = tblA.a_id)

/* or */
SELECT *
FROM tblA
WHERE a_id IN (SELECT a_id FROM tblB)

If table A is one to many with table B, then the foreign key must be in table B.

SELECT tblA.*
FROM tblA
JOIN tblB ON tblB.a_id = tblA.a_id
/*may yield many rows*/

If table A is many to many with table B, then there must be an association table to represent the relationship.

SELECT tblA.*, tblB.*
FROM tblAssociateAandB ab
JOIN tblA ON tblA.a_id = ab.a_id
JOIN tblB ON tblB.b_id = ab.b_id

At least that is how it looks to me. Maybe others will have other thoughts.