活动管理系统：俱乐部与员工MySQL数据库结构及权限设计问询

Great question! Let's break this down step by step to design a MySQL schema that fits your activity management system perfectly, keeping scalability and role-based access control (RBAC) needs front and center.

We'll structure the schema around core entities: users (all start as regular users), clubs (organisations), staff role assignments, and optional granular permission controls.

1. Core Users Table #

This table stores every registered user—whether they're just a regular user, a club owner, or staff member.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• user_id: Unique identifier for each user (primary key)
• password_hash: Always store hashed passwords, never plain text—use bcrypt or Argon2 for hashing
• created_at/updated_at: Track account creation and modification timestamps for auditing

2. Organisations (Clubs) Table #

Stores all clubs created by users. The creator_user_id links back to the founding user, who will automatically get the clubOwner role.

CREATE TABLE organisations (
    org_id INT AUTO_INCREMENT PRIMARY KEY,
    org_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    creator_user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

• org_id: Unique club identifier
• creator_user_id: Establishes ownership; ON DELETE CASCADE ensures if the creator's account is deleted, the club is removed too (adjust this if you want clubs to persist without their founder)

3. Roles Table #

Stores your predefined roles to keep the schema flexible. If you need to add new roles later (like a "volunteer" role), you just insert a row here instead of rewriting code.

CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    role_description TEXT
);

-- Insert your initial roles
INSERT INTO roles (role_name, role_description) VALUES
('clubOwner', 'Full access to all club and event operations'),
('eventManager', 'Manage event settings, registration, and related tasks'),
('treasurer', 'Handle accounting, refunds, and financial tasks');

4. Organisation Staff Assignment Table #

This junction table links users to clubs and assigns them a role. A single user can be staff in multiple clubs with different roles (e.g., someone could be an event manager at one club and a treasurer at another).

CREATE TABLE organisation_staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    org_id INT NOT NULL,
    role_id INT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (org_id) REFERENCES organisations(org_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    UNIQUE KEY unique_staff_assignment (user_id, org_id) -- Prevent duplicate assignments for the same user+club
);

• unique_staff_assignment: Ensures a user can't be added multiple times to the same club (remove this if you want to allow multiple roles per user in one club)
• is_active: Lets you disable staff access without deleting historical records (useful for former employees)

5. Optional: Granular Permissions Tables #

If you want precise control over exactly what each role can do (instead of grouping permissions by role), add these tables. This makes adjusting permissions trivial without changing application code.

Permissions Table #

CREATE TABLE permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'event_settings_edit', 'refund_process'
    permission_description TEXT
);

-- Insert your initial permissions
INSERT INTO permissions (permission_key, permission_description) VALUES
('org_manage', 'Edit club settings and details'),
('event_create', 'Create new events for the club'),
('event_settings_edit', 'Modify event rules and registration settings'),
('registration_manage', 'View and update event registrations'),
('accounting_view', 'Access club financial records'),
('accounting_edit', 'Update club financial data'),
('refund_process', 'Initiate and manage registration refunds');

Role-Permissions Junction 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(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

-- Assign permissions to your roles
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.role_id, p.permission_id FROM roles r, permissions p
WHERE r.role_name = 'clubOwner'; -- Club owners get all permissions

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.role_id, p.permission_id FROM roles r, permissions p
WHERE r.role_name = 'eventManager' AND p.permission_key IN ('event_create', 'event_settings_edit', 'registration_manage');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.role_id, p.permission_id FROM roles r, permissions p
WHERE r.role_name = 'treasurer' AND p.permission_key IN ('accounting_view', 'accounting_edit', 'refund_process');

How This Fits Your Workflow #

1. A regular user signs up, creating a row in the users table.
2. When they create a club, a row is added to organisations with their user_id as creator_user_id.
3. Automatically insert a row into organisation_staff linking their user ID to the new club, assigning the clubOwner role.
4. Club owners can add other users as staff by inserting rows into organisation_staff with the appropriate role_id.
5. When staff log in, check their organisation_staff records (and associated permissions, if using the optional tables) to restrict access to sensitive features.

Key Implementation Tips #

• Indexing: Add indexes on foreign keys (like user_id and org_id in organisation_staff) to speed up join queries.
• Security: Always validate user roles/permissions on the backend before allowing access to operations like refunds or event settings—never rely solely on frontend checks.
• Cascade Behavior: Adjust ON DELETE CASCADE if you need to retain historical data (e.g., keep staff records even if a club is deleted).

内容的提问来源于stack exchange，提问作者Mike Ross