Database design theory advice

[Elitachi]

Hi Guys

My first post, so here goes.

I am designing a cms, but before I do that I,m concentrating on the ums - user management, now, I have designed what I think to be a good table structure but I thought I would post here for everyone to look at, and hopefully give advice.

Here it is

http://www.syl.eclipse.co.uk/images/database.jpg

Now, what do you think?

I,ll explain it a little, firstly there is the module table, this will list every unique aspect of the site, i.e contact, news, tutorials etc etc.

Then a permissions table, i.e view, edit, delete etc, I thought this would be good, otherwise I would have to create a sub module table, and create a sub module for each permission, now seeing as most modules will be using the same basic set of permissions, its easier this way.

Does the module_has_permissions table look ok to everyone? you,ll notice there is a PK called module-has_permission_id and two FK's which have also been incorporated into a unique index. Is this the correct to achieve what I want, which is this...

Modules can have any number of permissions, but a group can only be given a permission if it already exists.

If anyone can offer advice, alternate way of doing the same or thinks what I have is good, then please fell to comment.

Thanks

Elitachi

[raf]

it's impossible to advice without knowing the more functional side of your application.
I think it's to normalised --> i'd replace the module_has_permissions and the groups_has_module_has_permissions table by one table (that contains foreign keys to the groups, modules and permissions table.).
Personally, i wouldn't use the group-level because it's restricting and from experience, i know that after a while, you get a lott of 'special groups' that contain only one member that needs to have a specific permissionsprofile.
If you want to use groups effectively, then you'll probably always have a hierarchical system and then there are better db-designs.
But in any case; using the grouplevel makes the db and your application more complicated.

I would create a table with the foreign keys of the members/modules/permissions tables and query that one inside your app.
When you add a new member, you can assign him to a group and fill the table using the standard-profile for that group. I would then allow the admins to edit the clients pofile.
Makes creating the new member a nanosecond less fast (but then again, this is only when you create a new member which is not really so frequent and performance-critical as looking up the permissions for each pagerequest) but it will speed up retrieving the permissions without adding any restrictions to a clientspecific permissionprofile --> these two requirements (performance and flexability for the permisisonretrievel) should be what your db-design needs to be build around.
You can still use groups and links permissions for each module to a group, but these tables should only be used when creating a new member, to select a sort of permission-template you then apply for the new member.

taking it yet another step further away from the textbook normalisations, i'd drop the permissionstable.
The possible permissions will be very limited and it's not very likely that you'll frequently add or remove possible permissions. it's a very static dimension.
So i would just turn them into columns in the members/modules table and then set a 0 (=default) or 1 as the values for each permission-column (column of type enum). Take a look at the user table in yout mysql database, and you'll see that this approach is
If you ever add another permissiontype, then adding a column to the members/modules-table will not take long and then you'll probably have a lott more work implementing that new permissiontype in your application.

So, not realy textbook desig, but in my opinion, probably the most performant, simple model with the maximum of flexability to assign permissions to a member and that is still very maintainable.
Keeping it simple is a great time and moneysaver if other people also need to work on your db in the future...