[thelist] Looking for dynamic menus for ASP pages

Ken Schaefer ken.schaefer at gmail.com
Wed Jul 28 18:58:57 CDT 2004


On Wed, 28 Jul 2004 08:46:37 -0500, Rob Smith <rob.smith at thermon.com> wrote:
> > Then develop a way of keeping track of
> > users, and which groups the users belong to. (and which groups groups
> > belong to, ala group nesting). Then assign groups permissions to
> > objects.
> 
> ....That's...what I've done. I even built an administration interface to help
> me maintain the data and all the groups, permissions shmirissions, yadda
> yadda yadda.


Others have pointed out the "correct" way to represent this in your
RDBMS. You should do an ER (Entity-Relationship) model before creating
your DB. Your entities (users, permissions) become tables, and
attributes become fields in the table. And where you have an M:N
relationship between two entities you create a separate table that
holds combinations of the primary key values from both those two
tables


> >  You end up with increasingly nasty code up-above splitting strings
> 
> With long sharp pointy teeth. I disagree. I haven't changed the code in
> nearly two years. Very seldom I have to change the menu system. When a new
> user comes in, my only questions are, "Where is this person and what do you
> want them to see?" I enter their information and enter my comma delimited
> list (1,2,4,9) and that's it, where each number represents an access level.


Right - now suppose you want to develop an interface where someone can
get a list of all possible permissions? How do you do that? Do you
extract every individual's comma seperated list, parse it for values,
work out a set of unique values, and present that? That won't scale at
all.

Or do you just hard code the master list into every page that needs to
do this information? That becomes a maintainability nightmare.

The scalable way to do this would be to have a table that holds all
permissions. Each record is a permission. To get a list of all
possible permissions:

SELECT 
   PermissionID,
   PermissionName,
   PermissionsDescription
FROM
   Permissions
WHERE
   Active = 1
   

To get the list of permissions for the given user:

SELECT
   a.PermissionID
FROM
   Permissions a
INNER JOIN
   UsersPermissions b
ON
   a.PermissionID = b.PermissionID
WHERE
   b.UserID = 1


> > You'd need to roll your own integrity system
> > because you can't rely on the database enforcing any sort of
> > referential integrity.
> 
> You cannot enforce referential integrity with comma delimited fields. Why?
> In a way, it violates RDBMS theory. Each cell is supposed to contain one and
> only one piece of information, not a comma delimited list. But then again,
> it does contain one and only one piece of information, a comma delimited
> list for user group definitions.

Uh - each field is supposed to reflect an attribute value, not some
arbitrary information you've put in there.

Hope that helps

Cheers
Ken


More information about the thelist mailing list