导师、学生、课程数据库设计及用户登录功能实现技术咨询

Got it, let's work through this database design step by step based on your requirements, fixing gaps and adding necessary components to support your business logic.

1. 修正现有表的字段问题 #

First off, I noticed your users table has duplicate password fields—let's clean that up to avoid redundancy. Also, your roles table only lists Student and Tutor as valid roles, but users.role_id is supposed to support Admin too, so we'll update that.

优化后的roles表 #

CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name ENUM('Student', 'Tutor', 'Admin') NOT NULL UNIQUE, -- 新增Admin角色选项
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

优化后的users表 #

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- 移除重复的password字段
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    role_id INT NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
);

2. 完善courses表结构 #

Your original note says courses stores tutor-course info, but we should separate core course data from tutor associations (since one course can be taught by multiple tutors). Let's make courses hold only course basics:

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) NOT NULL UNIQUE, -- 比如CS101、MATH202这类唯一标识
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    credit_hours INT,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3. 添加多对多关联表 #

Since your requirements involve many-to-many relationships (tutors ↔ courses, students ↔ courses), we need intermediate tables to link these entities properly.

导师-课程关联表（tutor_courses） #

Handles the "one tutor teaches multiple courses, one course has multiple tutors" relationship:

CREATE TABLE tutor_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tutor_id INT NOT NULL, -- 关联users表中角色为Tutor的用户
    course_id INT NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_tutor_course (tutor_id, course_id), -- 避免同一导师重复关联同一课程
    FOREIGN KEY (tutor_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

学生-课程关联表（student_courses） #

Handles the "one student takes multiple courses, one course has multiple students" relationship:

CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL, -- 关联users表中角色为Student的用户
    course_id INT NOT NULL,
    enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    grade VARCHAR(5), -- 可选字段，用于记录学生课程成绩
    UNIQUE KEY unique_student_course (student_id, course_id), -- 避免同一学生重复选同一课程
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

4. 核心业务逻辑实现要点 #

• 登录权限控制: When a user logs in, fetch their role_id from the users table, join with roles to get the role name, then restrict system actions based on that role (e.g., tutors can manage their assigned courses, students can view their enrolled courses, admins can manage all users/courses).
• 常用查询示例:
  • Get all courses taught by a specific tutor:

    SELECT c.course_code, c.course_name, c.description
    FROM users u
    JOIN tutor_courses tc ON u.id = tc.tutor_id
    JOIN courses c ON tc.course_id = c.id
    WHERE u.username = 'john_doe' AND u.role_id = (SELECT id FROM roles WHERE name = 'Tutor');
    

  • Get all courses a student is enrolled in, plus their tutors:

    SELECT c.course_code, c.course_name, CONCAT(u.first_name, ' ', u.last_name) AS tutor_name
    FROM users s
    JOIN student_courses sc ON s.id = sc.student_id
    JOIN courses c ON sc.course_id = c.id
    JOIN tutor_courses tc ON c.id = tc.course_id
    JOIN users u ON tc.tutor_id = u.id
    WHERE s.username = 'jane_smith' AND s.role_id = (SELECT id FROM roles WHERE name = 'Student');
    

5. 额外优化建议 #

• 密码安全: Never store plaintext passwords. Use a strong hashing algorithm like bcrypt to hash passwords before saving them to the users table, and verify hashes during login.
• 索引优化: Add indexes on frequently queried fields like users.username, users.email, tutor_courses.tutor_id, and student_courses.student_id to speed up database queries.
• 软删除: If you need to retain historical data, add an is_deleted BOOLEAN field (default false) to users, courses, etc. Mark records as deleted instead of physically removing them.

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