What is a basic model for making a database with users and groups?

Question

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?

Answer 1

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:

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.