Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004

    Two entities connected by two relations

    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.

    Any feedback is greatly appreciated.


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    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.


  3. #3
    Join Date
    Dec 2004
    I think, in that case the model would look like this:

    first relation:
    employees >----- managementgroups
    many employees belong to one management-group

    second relation:
    employees >------< managementgroups
    many employees are managed by many management-groups

    where the second relation is a violation to 3NF, hence a new entity is required. The second relation would become something like this:

    employees ----< e/m >---- managementgroups

    Another thought:
    If there is only one management group, the second relation would look like this:
    employees >---- managementgroup
    many employees are managed by one managementgroup

    this relation is redundant, because its the same as the first one.

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    okay, here's a great example

    employee-project is many-to-many (an employee can be assigned to multiple projects, a project can have multiple employees assigned to it)

    employee-project is one-to-many (an employee can be the project manager of multiple projects, but a project can have only one project manager)

    in implementation, the first relationship always requires a third, intersection or junction table, holding two foreign keys, which themselves comprise a composite primary key

    the second relationship is implemented by placing the foreign key of the project manager into the project entity | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    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:

    employee >--- are managed --- managementgroup
    employee >--- manages --- managementgroup

    but this is very conceptual, just illustrating how the two entities can be related. Something like this would be redundant in a real world application.


  6. #6
    Join Date
    Sep 2002
    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:


    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"!)

  7. #7
    Join Date
    Dec 2004
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts