Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Resolving multiple one-to-many relationship?

    There's someimes need to have multiple one-to-many relationship between one (primary key) side and multiple (foreign key) sides in another table!

    For example:

    You have "A medical procedure table"!

    Every medical procedure is performed by a doctor (foreign key in procedures tables and primary key in doctors table).
    Suppose you have residents and specialists, residents don't have the right to sign the release papers, but you want to keep information about the fact that they performed a procedure.
    So you would have signedby_DoctorNO and performedby_DoctorNO fields in procedures tables that both link to primary key in doctors table.

    However Access doesn't seem to handle that well, because when creating a form viewed by procedures you never get the chance to have Doctors data displayed twice (for signedby and performedby doctor).


    Possible solutions:

    Maybe you could create many-to-many relationship where every procedure is performed by many doctors (in this case two - one signing it and one performing it) and documenting the function they have in a particular procedure but it simply seems contra-intuitive.

    Or, you could argue that doctors who have signing rights and those who don't are subclasses of doctors and should be separated in two tables.

    Or you could have two exact copies of doctors tables named Doctors and Doctors1 and creating two one-to-many relationships to procedures!

    THIS IS A THEORETICAL QUESTION ON MY PART, I CAN HANDLE THE SOLUTION IN THIS PARTICULAR CASE. I WOULD JUST LIKE TO KNOW WHAT THE RIGHT SOLUTION IS (advantages, disadvantages of solutions mentioned above, plus propositions of some that I wasn't able to think of).


    P.S.

    But I still have one real life frequently encountered problem where you need 2 one-to-many relationships between two tables.

    Clubs and SportsEvents

    Each Sport Event takes at least (and in most cases exactly) 2 clubs, home and away!

    Lakers vs. Celtics
    ManU vs. Liverpool

    And it would be hard to argue that ManU and Liverpool don't deserve to be put in a single Clubs table. They are both clubs, only playing away or at home in a particular game!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Add the table to the relationships diagram more than once (You'll get TEAM and TEAM_1) and then you can create multiple 1:M relationships to the EVENT table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2007
    Posts
    108
    True, but you can't use tables Team and Team_1 when creating forms, it's only a minor irritation but still...

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, but you can add the table twice to the form's underlying query, which achieves the same result. Even better in queries is that you can give each a different alias (name), so, for example, Team_1 could become TEAM_A and TEAM could become TEAM_B.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    most often, the aliases used are home and away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, that would be reasonable for most games
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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