Many to many with implied and explicit relationships

0
votes

I have a standard many-to-many relationship in my database between Person and Widget. A Person in an administrative role has access to ALL Widgets. In my application, I want to see which Widgets a Person has access to.

I have two high level options:

  1. Explicitly manage the relationships. When a Person becomes an administrator, relate that Person to all existing Widgets. When a Widget is created, relate that Widget to all existing administrators.

  2. At run-time, if the Person is an administrator, assume they have access to ALL widgets and bypass the relationship table when loading Widgets.

Is one option better than the other? Is there a name for this scenario?

I have been trying to apply option 2 using NHibernate and I can't seem to figure out how to get it to bypass the relationship table when loading all Widgets for an entity (and even if I could, this would unnecessarily load alot of information unless I load Widgets separately from the Person entity and apply paging).

2
nhibernatemany-to-manyentity-relationship

2 Answers

1
votes

I would map this by means of Roles.

Roles : Person = 1 : Many

So when you create a person, you also create a new Role, unless they are an Administrator in which case they use the existing Admin Role.

Then the problem is easy: You need a WidgetRole table.

When a new Widget is created, and entry is automatically added to the WidgetRole table for NewWidget, AdminRole

When a Person changes to an Admin Role, simply change their current Role.

imo this setup is logically simpler, than having a special Admin case.

0
votes

I had to share the final solution - should help anyone trying to force NH to load up a relationship that is not explicit in the DB.

I was already creating sub classes of person and NHibernate is smart enough to recognize Administrator : Person and instantiate that Person as an Administrator (where Administrator has a table with a PK/FK PersonId)

I just added a new mapping override for Administrator...

mapping.HasManyToMany(x => x.Widgets)
  .Table("AdministratorWidgetAccess")
  .Cascade.None();

And I added a view called AdministratorWidgetAccess...

SELECT a.PersonId as [AdministratorId], w.WidgetId as 
FROM dbo.Administrator AS a LEFT OUTER JOIN
  dbo.Widget AS w ON 1 = 1

When running, if the Person is an Administrator it loads up all Widgets based on the relationship in the view, otherwise it loads up Widgets based on the join table.