Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to find the best way for a basic security system for a website. I know I want users and groups.

I thought I'd have:

user_table
user_id
user_name
...

group_type
group_id
group_name
parent_id
...

group_table
id
user_id
group_id

First one is the user, second one is the group, and the third one is the intermediary table that connects the two. One user has many groups.

Does this sound ok?

share|improve this question
    
Other than the names. Your GROUP_TYPE table should be named GROUP or something similar, and your GROUP_TABLE should reference both Users and Groups, as that's what it's linking. –  Adam Musch Feb 1 '12 at 14:49
    
@adam doesn't group_table do that with user_id and group_id? –  johnny Feb 1 '12 at 14:55
4  
@AdamMusch It should not be named GROUP, as that is a reserved word. Tables and columns should never be named after reserved words –  Phil Feb 1 '12 at 15:02
1  
@Phil agreed. You may as well name a table SELECT and a field FROM so you can have a query like SELECT [FROM] FROM [SELECT] –  JNK Feb 1 '12 at 15:04
1  
Try calling the basic entities something like 'app_user' and 'app_role' –  ConcernedOfTunbridgeWells Feb 1 '12 at 15:06

1 Answer 1

up vote 7 down vote accepted

The traditional way to model this is using a pattern called Role-Based Security.

The idea is not just to have groups of users, but also groups of permissions. Here is how the pattern looks:

Role Based Security ERD

Note that you want to avoid reserved words for table names, so don't name your tables exactly as shown in the diagram.

The way it works is that your groups or Roles have not only a list of users assigned to them but also a list of permissions assigned to them. This allows you to table-drive both who can do what, but also what it is they can do, if you follow me.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.