Data Modeling Question

Question

Data Modeling Question

Eric Garza-215066

Newbie

Points: 2
More actions
September 28, 2006 at 3:20 pm

#169837

Hey all. My first post, so be gentle. I had a data modeling question and potential solution and wanted to get some other opinions.
We have an entity called (Model) There are two cases we are trying to support:
1) We have to determine a user's Model based on their department, facility, and Role. A model can only have one unique combination of facility, department, role, but a model can have many unique facility, department, and role combinations. So I was thinking we should have a type table for facility, department, and role with a PK of course. We have the Model entity of course. We would also have a cross reference table between facility, department, role and model. The model ID would be an attribute in the cross ref table, not unique. We would then have a composite key in the cross ref table (PK,FK1) for department, facility, and role. We would then be able to query the cross ref table to determine the Model ID for a user where facility = x, department = x, and role = x. Thoughts?
2) An alternate case gets trickier where instead of a unique combination of department, facility, and role for a Model, there could be unique combinations of department and facility for a particular role and a Model could have multiple Roles. Yikes. Thoughts?
Thanks for any help.
Eric Garza

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 1

Hi Eric,

Can you clarify something where I think you've contradicted yourself? You said: "A MODEL can only have one unique combination of facility, department, role, but a MODEL can have many unique facility, department, and role combinations." Did you mean to say user instead of model in one of those clauses?

Eric Garza-215066 Newbie Points: 2 More actions · Answer 2

I meant that a Model(Entity Name) can have one unique combination of facility, department, and role, but can have several combinations associated with it.

facility1, department1, role1, model1

facility2, department2, role2, model1

Does that make sense?

Eric

Judith Webber Ten Centuries Points: 1027 More actions · Answer 3

Hi Eric,

If I am reading your question correctly I believe your initial thought of using that cross reference table to join a model to a "user" entity (By user entity I mean the combination of Role, Facility and Department) is correct.

In both cases the unique fields are the Role Facility and Department which would give you a unique key on that table. Since you haven't included the Model in that unique key it gives you a one to many relationship....so for each Model several Roles in several Departments in several Facilities could apply.

Hope that helped.

Judie