Can anybody give an example of the following case:
Entity A has a 1:n AND a n:1 relation to Entity B. An example of this scenario that makes sense is greatly appreciated.
(the cardinalities dont really matter, what's important is that there are two different relations between two entites. Of course, if the two relations have the same cardinalities, one is redundant.)
Personally I think a case like this seems to be a design error.
This is not a homework-question. I currently develop a tool which translates one model (ERD) to another. During the transformation process entities are merged, which could lead to such a scenario as described above. Now, i have to find a way to solve this. I have thought of "relation-precedences". To be exact: m:n binds stronger than n:1, n:1 binds stronger than 1:n, 1:n binds stronger than 1:1. I'm not quite sure of this. What do you think?
But before i just remove relations that bind weaker than others, I need to know if there might be examples that _require_ 2 relations between 2 entities.
A simple case is one where two (or more) employees are required to perform supervisory/disciplinary actions on other employees... One relationship between employees shows which employees form management groups, the other relationship shows which groups manage which employees.
I think there is one issue though.
Since the m:n relation is resolved with an intersection entity, there are not two entities connected with two relations. but rather one relation between employee and project, one relation between project and intersection entity, one relation between intersection entity and employee.
I think there are no such examples, if no named relations are introduced. that way, two entities, employees and managementgroups, could be related like this:
I'm not sure I follow your reasoning. Are you saying that an entity model is necessarily invalid if it contains 2 relationships "A --(1:n)-- B" and "A --(n:1)-- B"? I don't see why. It may be possible to model it differently perhaps, but that doesn't make it wrong. How about:
PERSON --- owns/owned by ----< HOUSE
PERSON >-- lives in/occupied by --- HOUSE
These are two distinct relationships between PERSON and HOUSE. Assuming both are true independently (i.e. there is no joint ownership, etc.), then why should they not be true when considered together?
Note that a PERSON don't have to own the HOUSE he lives in, or live in the HOUSE he owns! If either of those held true then the model might be different.
You could generalize the model like this:
PERSON --< PERSON_HOUSE_RELATIONSHIP >-- HOUSE
with an attribute Relationship Type in the intersection that can be 'OWNS' or 'LIVES IN'. But now you have to find a new way to enforce the rules that a PERSON can only live in one HOUSE etc.
(BTW, please don't refer to relationships as "relations" - it is both wrong and confusing, since "relation" is in fact the proper term for a "table"!)
this is indeed an example that makes sense and is exactly what i was thinking of in my initial scenario.
damn, i'm in trouble now. this whole transformation thing is more complex than i have expected it to be.
thanks a lot for all the feedback. especially yours andrewst.