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

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

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.

where did this come from? certainly not pat

you ~did~ get a very sensible response

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

you got that right

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.

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.