http://www.freeimagehosting.net/uploads/8fe0c141cb.gif

Hi. The url above shows a diagram I'm working on in order to design a basic membership and sitemap diagram. Basically what I define as Membership is a User (John, Mary, etc) who belongs to one Department (Administrative, Finance, etc) that can have many Groups (admin, manager, etc) and a Group can have many Roles (create, view, delete, etc)

I'm differentiating Groups from Roles, considering roles as something a person can execute (an action)

I have 2 join-tables: Membership and Navigation

Membership: joins 3 tables (Role - Department - Group) because i'm considering that an admin from dept finance is different from an admin from another dept because they can have different roles (eventually they can have the same roles, but it's a coincidence)

Navigation: this join-table sets who can access a particular link (relative url path in a website)

I would like to see some opinions about that diagram. :-)

opinions? okay, i like your use of colour



okay, seriously, it's hard to give an opinion on a proposed solution when we are still in the dark as to the requirements

well, colors are great, but regarding requirements, let me try to explain:

1. the main reason i'm trying to design this membership - sitemap diagram is because independent of what kind of web app i usually develop, normally all of them requires managing users, assigning them into groups and then, according to which group he/she belongs to, control access to web pages and eventually what kind of operations he/she can execute (eg, view a report, create, etc) based on the roles a particular group have

2. sometimes in a more complex app i also need to differentiate a user or group by which sector/department he works, but normally the department is not really important, so the Department table/model could be eliminated or we could create a 'general' department and assign all users to this one.

3. the Navigation join-table could be de-normalized and instead i could add an extra column in the Links table (eg, members) and fill this field with coma separated membership_id values, so i know who can access that url, but i'm not sure doing that is a good idea?

I think by using that diagram, although i'll need to make use of several joins to get the needed data, it can solve most of the situations i generally face when developing wep apps ... i'm just a bit insecure about the diagram because i'm not used to designing db diagrams.

There is no user_id in Membership. I don't understand how the diagram matches your definition.

I believe the department_id in User is enough to tell me which users I have in a particular Department or even Membership, is that right?

Only if the fact of a department_id appearing in Membership means that ALL users in that department always have a membership. That wasn't what I inferred from your description though.

It's virtually impossible to do effective design in an online forum. These threads often turn into a long list of questions and answers about the business domain that don't add up to anything very instructive.