Database Design

Step 1: Create Base Tables

We’ll start by creating the fundamental tables without any relationships. These tables are:

• Permissions
• Roles
• UserGroups
• Users

1.1 Create permissions Table

-- Create the 'permissions' table
CREATE TABLE `permissions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    `model` VARCHAR(255) NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Explanation of Columns:

• id: Auto-incremented primary key.
• name: Name of the permission (e.g., Manage Users, Edit Posts). It’s set as UNIQUE to ensure no duplicate permission names.
• model: The model or entity this permission applies to (e.g., User, Post).
• action: The specific action allowed for this permission (e.g., create, edit, delete).
• createdAt: Timestamp of when the permission was created.
• updatedAt: Timestamp of when the permission was last updated.

1.2 Create roles Table

-- Create the 'roles' table
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255) NULL
);

Explanation:

• id: Unique identifier for each role.
• name: A unique name for the role (e.g., “Admin”, “Editor”).
• description: Optional detailed description of the role’s responsibilities.

1.3 Create usergroups Table

-- Create the 'usergroups' table
CREATE TABLE usergroups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255) NULL
);

Explanation:

• id: Unique identifier for each user group.
• name: A unique name for the user group (e.g., “Marketing Team”, “Developers”).
• description: Optional detailed description of the user group.

1.4 Create users Table

-- Create the 'users' table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT NULL,
    usergroup_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL,
    FOREIGN KEY (usergroup_id) REFERENCES usergroups(id) ON DELETE SET NULL
);

Explanation:

• id: Unique identifier for each user.
• username: Unique username for login purposes.
• email: Unique email address.
• password: User’s password (should be stored hashed).
• role_id: References the user’s role; allows each user to have one role.
• usergroup_id: References the user’s group; allows each user to belong to one group.
• created_at & updated_at: Timestamps for record creation and updates.
• Foreign Keys:
  • role_id: Links to roles.id; ON DELETE SET NULL ensures that if a role is deleted, the role_id in users becomes NULL.
  • usergroup_id: Links to usergroups.id; behaves similarly to role_id.

Note: Assigning role_id and usergroup_id directly in the users table establishes a Many-to-One relationship:

• User to Role: Many users can have the same role.
• User to UserGroup: Many users can belong to the same user group.

Step 2: Establish Relationships

Now that we have our base tables, we’ll establish the necessary Many-to-Many relationships using join tables.

2.1 Assigning Permissions to Roles

Relationship: A role can have multiple permissions, and a permission can be assigned to multiple roles.

Implementation: Create a join table role_permissions.

2.1.1 Create role_permissions Join Table

-- Create the 'role_permissions' join table
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Explanation:

• role_id: References a role in the roles table.
• permission_id: References a permission in the permissions table.
• Composite Primary Key: Combines role_id and permission_id to ensure each pair is unique.
• ON DELETE CASCADE: If a role or permission is deleted, related entries in this table are also deleted automatically.

Usage Example:

-- Assign 'CREATE_USER' permission to 'Admin' role
INSERT INTO role_permissions (role_id, permission_id)
VALUES (1, 2);

2.2 Assigning Permissions to UserGroups

Relationship: A user group can have multiple permissions, and a permission can be assigned to multiple user groups.

Implementation: Create a join table usergroup_permissions.

2.2.1 Create usergroup_permissions Join Table

-- Create the 'usergroup_permissions' join table
CREATE TABLE usergroup_permissions (
    usergroup_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (usergroup_id, permission_id),
    FOREIGN KEY (usergroup_id) REFERENCES usergroups(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Explanation:

• usergroup_id: References a user group in the usergroups table.
• permission_id: References a permission in the permissions table.
• Composite Primary Key: Ensures each user group and permission pair is unique.
• ON DELETE CASCADE: Maintains referential integrity by deleting related entries upon deletion of a user group or permission.

Usage Example:

-- Assign 'EDIT_POST' permission to 'Editors' user group
INSERT INTO usergroup_permissions (usergroup_id, permission_id)
VALUES (3, 4);

2.3 Assigning Permissions Directly to Users

Relationship: A user can have multiple permissions directly assigned, and a permission can be assigned to multiple users.

Implementation: Create a join table user_permissions.

2.3.1 Create user_permissions Join Table

-- Create the 'user_permissions' join table
CREATE TABLE user_permissions (
    user_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (user_id, permission_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Explanation:

• user_id: References a user in the users table.
• permission_id: References a permission in the permissions table.
• Composite Primary Key: Ensures each user and permission pair is unique.
• ON DELETE CASCADE: Deletes related entries if a user or permission is removed.

Usage Example:

-- Assign 'DELETE_POST' permission directly to user with id 5
INSERT INTO user_permissions (user_id, permission_id)
VALUES (5, 6);

Step 3: Summary and Diagram

3.1 Summary of Relationships

• Users
  • Belongs to one Role (role_id in users table).
  • Belongs to one UserGroup (usergroup_id in users table).
  • Has many Permissions through user_permissions.
• Roles
  • Has many Permissions through role_permissions.
  • Has many Users.
• UserGroups
  • Has many Permissions through usergroup_permissions.
  • Has many Users.
• Permissions
  • Assigned to many Roles, UserGroups, and Users.

3.2 Visual Diagram

Here’s a simplified representation of the relationships:

[USERS]
   |        \
   |         \
[ROLES]    [USERGROUPS]
   |             |
   |             |
[ROLE_PERMISSIONS]   [USERGROUP_PERMISSIONS]
          \             /
           \           /
            [PERMISSIONS]
                |
                |
          [USER_PERMISSIONS]
                |
              [USERS]

Explanation:

• Users are connected directly to Roles and UserGroups via foreign keys.
• Permissions are connected to Roles, UserGroups, and Users via respective join tables.
• This setup allows for flexible and granular access control:
  • Role-based permissions: Assign common permissions to roles and then assign roles to users.
  • Group-based permissions: Assign permissions to user groups and assign users to these groups.
  • Direct user permissions: Assign specific permissions directly to users when needed.

Complete SQL Script

For convenience, here’s the complete SQL script combining all steps:

-- Create 'permissions' table
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255) NULL
);

-- Create 'roles' table
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255) NULL
);

-- Create 'usergroups' table
CREATE TABLE usergroups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255) NULL
);

-- Create 'users' table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT NULL,
    usergroup_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL,
    FOREIGN KEY (usergroup_id) REFERENCES usergroups(id) ON DELETE SET NULL
);

-- Create 'role_permissions' join table
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- Create 'usergroup_permissions' join table
CREATE TABLE usergroup_permissions (
    usergroup_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (usergroup_id, permission_id),
    FOREIGN KEY (usergroup_id) REFERENCES usergroups(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- Create 'user_permissions' join table
CREATE TABLE user_permissions (
    user_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (user_id, permission_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Inserting Sample Data

1. Adding Permissions

-- Insert sample data into permissions table
INSERT INTO `permissions` (`name`, `model`, `action`)
VALUES
    ('Manage Users', 'User', 'create'),
    ('Manage Users', 'User', 'edit'),
    ('Manage Users', 'User', 'delete'),
    ('Edit Posts', 'Post', 'edit'),
    ('Create Posts', 'Post', 'create'),
    ('Delete Posts', 'Post', 'delete'),
    ('View Reports', 'Report', 'view'),
    ('Edit Settings', 'Settings', 'edit');

Resulting Sample Data

Description of the Sample Data:

• For managing users (User model), there are permissions for creating, editing, and deleting.
• For managing posts (Post model), there are permissions for creating, editing, and deleting posts.
• There’s a permission to view reports (Report model) and edit settings (Settings model).

How to Retrieve Permissions by Role

Once you have the Role and Permission entities correctly linked, you can query the permissions assigned to a specific role by running a query like:

SELECT p.name, p.model, p.action
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permissionId
WHERE rp.roleId = <ROLE_ID>;

2. Adding Roles

INSERT INTO roles (name, description) VALUES
('Admin', 'Administrator with full access'),
('Editor', 'Can edit content'),
('Viewer', 'Can view content');

3. Adding UserGroups

INSERT INTO usergroups (name, description) VALUES
('Content Team', 'Team responsible for content creation'),
('QA Team', 'Quality assurance team');

4. Adding Users

INSERT INTO users (username, email, password, role_id, usergroup_id) VALUES
('john_doe', 'john@example.com', 'hashed_password_1', 1, 1),
('jane_smith', 'jane@example.com', 'hashed_password_2', 2, 2);

5. Assigning Permissions to Roles

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1, 1), -- Admin can create posts
(1, 2), -- Admin can edit posts
(1, 3), -- Admin can delete posts
(2, 2); -- Editor can edit posts

6. Assigning Permissions to UserGroups

INSERT INTO usergroup_permissions (usergroup_id, permission_id) VALUES
(1, 1), -- Content Team can create posts
(2, 2); -- QA Team can edit posts

7. Assigning Permissions Directly to Users

INSERT INTO user_permissions (user_id, permission_id) VALUES
(2, 3); -- Jane Smith can delete posts directly

Conclusion

This structured approach provides:

• Flexibility: Permissions can be managed at multiple levels (role, group, user).
• Scalability: Easy to add new roles, groups, permissions, and users.
• Maintainability: Clear separation of concerns and straightforward relationships.

Advantages:

• Reusability: Assigning permissions to roles and groups allows for easy reuse across multiple users.
• Granularity: Direct user permissions enable fine-grained access control when necessary.
• Integrity: Foreign keys and cascading rules maintain data integrity automatically.

Considerations:

• Ensure that permission checks in your application logic account for all levels (role, group, user).
• Implement proper indexing on foreign key columns for performance optimization.
• Use transactions when performing multiple related insert/update operations to maintain consistency.

I hope this comprehensive, step-by-step guide helps you understand and implement your database schema effectively. Let me know if you need any further assistance or clarification!