1

I'm currently designing a database model and I've come across an issue, where I'd like some more input on what's considered the proper way of doing things.

In the example below I have two tables, Persons and Roles. Now 1 person may be assigned 0 or more roles. This is pretty straight forward with a many-to-many relationship, but the tricky part comes when you want to allow the user to select which role he is currently "operating" as.

Say the user got a selection of 5 roles to choose from, and depending on which role he currently has chosen, the application will present him with different views / menus / buttons / etc.

I see two solutions here:

1: (See top part of image linked below)

In the persons table, you include a nullable FK to the many-to-many relationship (the association entity).

From Wikipedia on Associative entity

It seems ok for "other entities" to reference this relationship. I'm just not entirely sure if this includes the Roles and Persons entities.

What I like about this design is that it's pretty easy to get a list of all allowed roles for a person, and then you simply point to one of those allowed roles, saying "you're the current/active one". This also feels like a good way to make sure that the user can only have a role as current, if he has access to the role... Only, I also see potential disaster here: What if, the FK in the persons table references that relationship of another user. The DB design would allow this. It would only be code that prevents it.

2: (See bottom part of image linked below)

I keep the associative entity simple, and instead I have FK in persons table that points to a specific role in the Roles table.

The problem here is that I get even less help from the DB design with ensuring that the "current role" of a person, is a role that is actually "allowed".

Example image

Any thoughts on this would be greatly appreciated =)

UPDATE: I'll include another variation of the same issue.

Two entities: WorkOrders and Sites

  • 1 Site may have access to many Work orders
  • 1 WorkOrder may be accessed by many Sites

So we have a many-to-many relationship

The issue: How do we best store in database which Site is the Owner of a work order, when there can only be 1 Site that Owns a work order.

Do we have "OwnerSiteId" as a column in the WorkOrder table, or would it be ok to reference the many-to-many relationship instead, saying "that one" is the owner.

Trond Blomholm Kvamme
  • 189
  • 3
  • 7

2 Answers2

0

Store the current role together with other session data.

Oswald
  • 31,254
  • 3
  • 43
  • 68
  • Yes, for this problem here I can see that might be a solution. Only I see this issue arise in other variations as well. Example: We have two other entities, WorkOrders and Sites. A WorkOrder is owned by 1 site, and may be shared with several other sites. Here you get the issue over again. You have several Sites that should have access to the work order, but only 1 of them is the "owner" – Trond Blomholm Kvamme Oct 16 '12 at 13:18
  • @TrondBlomholmKvamme Yes, but all of those references are *transient* and should not be stored "formally" in the database: It's all session stuff. If it's necessary to store "current" data, you would simply store it as a column of the entity. Otherwise, consider following Oswald's advice. – Bohemian Oct 16 '12 at 13:28
0

If your requirement is that
One person can have many roles but only can have one active role
if yes than your both designs have following pros and cons

- Design 1. Having key of ROLE table in the PERSON table pros: it will reduce on join hence cost will be reduced on database operation. Cons: You can assign the user a role that is never been in his allowed list.

  • Design 2. Having key of Associate Entity table in the PERSON table pros:PERSON will be assigned only those ROLES which are allowed for him. Cons: One additional join cost than first approach.

Conclusion: if I were you I will go for design 1 and ensure the role assignment from application.

zaffargachal
  • 802
  • 7
  • 21