User access DB design

suaveant has asked for the wisdom of the Perl Monks concerning the following question:

This isn't purely perl I suppose, it is bit of perl/MySQL...

We are in the process of creating a new user system where I work. The way we work now is we call out to another department's system to grab a list of Access Priviledges (accprivs) for the user for each page.

We are currently working up our own local version of an accpriv system, and are having a design issue. My co-worker, who is pretty much building it, was going to use a large bitmap stored in the user table to handle the accprivs, select it out, and do all the dirty work in perl. This doesn't sound quite right to me.

My thought was to have a table of accprivs with accpriv ids, and a mapping table to map accpriv ids to user ids. Then to grab all the accprivs for a user is a simple JOIN query. However, the only arguments I can really offer for this is that it would be easier to write an accpriv editing page with this system, and easier to move things around.

Now, I could be wrong, does anyone have any good arguments/suggestions on how to might be best to handle this? The system is looking at around 1000 users and 300 accprivs, and will be accessed from a fastcgi connected to the database. Any comments are welcome - thanks

- Ant
- Some of my http://www.perlmonks.org/index.pl?node_id=56739#Best - (1 2 3)

Comment on User access DB design

Replies are listed 'Best First'.
Re: User access DB design by VSarkiss (Monsignor) on Nov 01, 2001 at 02:19 UTC
A big consideration is whether you'll be maintaining the privileges in this database (updating, deleting, etc.) or only reading them. If you're only reading them, it makes sense to make it fast to retrieve and easy to update en masse when you get a refresh from the other department. On the other hand, if you'll be maintaing the values in your own database, then you want to make it fast and easy to update a single user's priviledges. It's hard to tell without knowing more about the particular data involved, but if it's essentially read-only with bulk refresh, a big bucket-o-bits may be the way to go. If you're maintaining the data yourself, then a normalized data model will make coding much simpler. Of course, these are generalizations; a normalized data base retrieval may be faster even in the first case, depending on the amount of data, cachability, the programmers' skill, and so on. If you have the time and the resources, the only way to tell for sure is to build and measure. Just from a gut feel, though, I agree with you: let the database do the lookup work. HTH	[reply]
Re: User access DB design by tstock (Curate) on Nov 01, 2001 at 02:27 UTC
I don't feel strongly about either way without the details of how this is to be used, but I do have a few pointers you might use to push either case: . performance: if your user table contains variable length fields (varchar, blob, text), it could make sense to move accprivs to another table without them. enum fields or fixed length fields only allows MySQL to use a faster table format. . If the "bitmap" is one database field, you fail the 1st normal form of database design. . If any priviledge depends on another priviledge, the database would not be in the 3rd normal form untill you split privileges into more tables tiago	[reply]
Re: User access DB design by growlf (Pilgrim) on Nov 01, 2001 at 07:19 UTC
Hmm.. why not use the built in user access of MySQL? It has several advantages - fast, reliable, builtin already, already documented.. etc. Also allows for access restriction per site location, table, column.. etc. All you would need to do for administration is make a standard user rights manager (one already comes with MySQL). Additionally it is network friendly for backups - (it resides in the database after all)	[reply]
Re: Re: User access DB design by suaveant (Parson) on Nov 01, 2001 at 19:23 UTC
Umm... no, this isn't for access to the DB, this is for various access priviledges on our web site (financial data) - Ant - Some of my best work - (1 2 3)	[reply]
Re (tilly) 1: User access DB design by tilly (Archbishop) on Nov 01, 2001 at 19:17 UTC
I prefer the join table approach. Having built one in the last few months, it gave me several good advantages, including an auditing sytem, the ability to set up expiration policies for permissions, the ability to develop a authorization groups, and great flexibility in adding new types of permissions for things. The bitmap has a speed and space advantage. But the join approach is vastly more flexible, and that has worked out very well for me.	[reply]
Re: User access DB design by mpeppler (Vicar) on Nov 01, 2001 at 23:37 UTC
I'm no MySQl specialist, but on general principles I'd recommend the joined table method. This may be a tad slower for the raw data fetch, but will be much easier to maintain in the long run, and will be extensible. In general my feeling in these cases is that if you have a SQL database, then you should design a fully normalized database, and possibly denormalize afterwards if you realize that the number of joins become too unwieldy (which doesn't seem to be an issue here.) Michael	[reply]