When designing a schema for employees based on roles, the goal is to establish a flexible structure that dynamically manages permissions. Here’s how we achieve this:
Create a Roles Table
The Roles table defines different roles within the system (e.g., Admin, Manager, Employee).
Columns:
role_id (Primary Key): Unique identifier for each role.
role_name: Name of the role (e.g., “Admin”).
description: Optional description of the role.
2. Create a Permissions Table
This table lists all possible permissions that can be assigned.
Columns:
permission_id (Primary Key): Unique identifier for each permission.
permission_name: Name of the permission (e.g., “Edit Employee Data”).
module_name: The specific module or feature the permission applies to.
3. Create a Role_Permissions Table
This table establishes a many-to-many relationship between roles and permissions, enabling dynamic assignment of permissions to roles.
Columns:
role_permission_id (Primary Key): Unique identifier for each entry.
role_id (Foreign Key): References the Roles table.
permission_id (Foreign Key): References the Permissions table.
4. Create an Employees Table
The Employees table stores employee-specific details and assigns them a role.
Columns:
employee_id (Primary Key): Unique identifier for each employee.
name: Name of the employee.
email: Contact email for the employee.
role_id (Foreign Key): References the Roles table, assigning a role to the employee.
Example Schema
— Roles Table
CREATE TABLE Roles (
role_id SERIAL PRIMARY KEY,
role_name VARCHAR(50) NOT NULL,
description TEXT
);
— Permissions Table
CREATE TABLE Permissions (
permission_id SERIAL PRIMARY KEY,
permission_name VARCHAR(100) NOT NULL,
module_name VARCHAR(50)
);
— Role_Permissions Table
CREATE TABLE Role_Permissions (
role_permission_id SERIAL PRIMARY KEY,
role_id INT REFERENCES Roles(role_id) ON DELETE CASCADE,
permission_id INT REFERENCES Permissions(permission_id) ON DELETE CASCADE
);
— Employees Table
CREATE TABLE Employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
role_id INT REFERENCES Roles(role_id) ON DELETE SET NULL
);