ansaurus

Question

Modeling a Join Table

Answer 1

A:

Just have an attribute of a user object called "roles", which contains a list of roles.

You can also have 2 attributes, one for a list of active roles one for inactive ones, in case you need to manage the active flag using that object.

Also, you can have a role object's attribute listing the users in that role (either instead of or in additional to the role attribute in user object) - again possibly with "inactive" copy.

DVK 2010-02-03 20:31:31

YOu are saying that my Roles class should be made of a Role reference and IsActive property?

tucaz 2010-02-03 20:38:52

Nope, it should have "users" property and optionally another "Incactive users" property. From your data table, isActive is not a property of individual role

DVK 2010-02-03 20:45:23

IsActive represents the state of the relationship between a User and a Role. To make the example more complete, let´s say that a User has-many Roles and some of them can be temporarly inactive (IsActive = false). You will say that I should remove it if it´s inactive, but is a business requirement to be able to inactive this relationship for a while.Or, I could have some other properties in the relationship like the name of the one who authorized this user to have a role so I would add AuthorizerUserId to UserRole table.

tucaz 2010-02-03 20:49:58

Then what I proposed in my answer is a correct answer. You model the "isActive" by a user's membership in role's active or inactive user list (and/or by role's membership in user's active/inactive role lists).

DVK 2010-02-03 21:38:28

You could theoretically have a list of "role, active_flag" pairs instead of having an active roles list and inactive roles list, doesn't matter TOO much which of the 2 approaches to choose. (and/or a list of "user, active_flag" pairs inside a role)

DVK 2010-02-03 21:39:53

Answer 2

A:

Create a view that joins UserRole and Role:

[VUserRole]
UserId
RoleId
RoleDescription
IsActive

and model it with a UserRole class. Then User has a collection of UserRole and IsActive is an attribute of UserRole.

Note that this solution won't play well with most (any?) persistence framework, and the "right" way to do it is to map the join table.

Jamie Ide 2010-02-03 20:58:48

I´m looking for a more OO solution without creating the N to N table in my code. Any other thoughts?

tucaz 2010-02-03 21:07:09

Modeling the link table is the OO solution because it's a many-to-many with additional attributes. My solution doesn't create the link table in code, it uses a class that combines UserRole and Role. If you're willing to write all the data access code by hand you can model it any way you want.

Jamie Ide 2010-02-03 21:57:12

Actually, I´m planning to map it with NHibernate. Any Additional thoughts?

tucaz 2010-02-03 22:03:58

ansaurus

tags:

views:

answers:

Modeling a Join Table

related questions