Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    ER Question involving many-to-many-to-many relationship

    Hi all:

    I was hoping you could help me with an ER question. I have three tables: Users, Regions, and Roles. The relationship between them is as follows:

    User can be a member of multiple regions
    Regions can house many Users

    User can have multiple roles
    Roles can be assigned to multiple users

    I want to be able to capture the following requirement: User can be part of multiple regions, and users can have different roles for each region. I am not sure how to model this correctly. I was hoping that someone here could help. My attempts to model this have not been successful.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    This sounds like a classroom assignement. I'll be glad to help, but I won't do the work for you.

    The key to solving this problem is to break down the entities that you need to track. The physical entities are easy: Users, Regions, and Roles. The hard part is to model the two non-physical entities in your problem, which are the relationships of user to region and user to role. Once you include those relationships, then the answer becomes simple.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    Hmm.

    Well. I am not sure that was quite as helpful as I would have hoped.

    I suspect your moral lesson also calls for associative tables, but since User can be part of many regions (and vice versa) and Users can have multiple roles in different regions, I fail to see how an associate entity would be 4nf 'lossless'.

    In any case: I was hoping for a sensible response on how to neatly model this ternary problem with some account of the entity sets and and the residual leaf table. No luck.

    P.S. If someone silly enough wants to cheat on their homework, which is not the case here, these forums do not seem to be the place to guard against it. It's really a waste of everyone's time.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by radioshack View Post
    ... I fail to see how an associate entity would be 4nf 'lossless'.
    where did this come from? certainly not pat

    Quote Originally Posted by radioshack View Post
    In any case: I was hoping for a sensible response ...
    you ~did~ get a very sensible response

    if you cannot utilize pat's answer, that's your problem, innit

    Quote Originally Posted by radioshack View Post
    It's really a waste of everyone's time.
    you got that right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by radioshack View Post
    Well. I am not sure that was quite as helpful as I would have hoped.
    The level of help you get is generally proportional to the amount of effort you yourself put in and the amount of respect with which you treat the help being offered. A vague "I can't figure this out" gets you a vaguely helpful suggestion like Pat's. Showing some of your unsuccessful modeling attempts gets you more specific advice on how to fix them. A sense of entitlement and an expectation of the answer being served on a silver platter gets you nowhere. All's not lost though: you're always entitled to a full refund if you find the help wanting.

    I suspect your moral lesson also calls for associative tables ...
    You suspect correctly. That alone should be a clue to reconsider your solution in those terms, not simply reject it again.

    If you're still interested in assistance, you can help yourself out by showing us how you'd resolve the relationship between Users and Regions. That is the easier of the two relationships to figure out.

Posting Permissions

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