php - 相对MySQL数据库

Question

我有一个 MySQL 数据库表，其构建如下。 类(class)下方的数字表示类(class)的“级别”，例如高、中或低，0 表示未参加该类(class)。

我需要构建另一个表来保存每个类(class)的家庭作业，但我有点不知道如何构建该表，特别是表示行的确切列必须是什么，因为每个类(class)没有特定的东西来表示它。

Answer 1

一个适当规范化的结构应该有一个单独的类(class)表，给每个类(class)一个 ID，另一个表通过包含学生 ID、类(class) ID 和级别将学生放入类(class)。

CREATE TABLE students (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL,
  password VARCHAR(64), 
  email VARCHAR(...)
  UNIQUE KEY (username)
);

CREATE TABLE courses (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

/* If a record doesn't exist for a course and student, 
   the student isn't enrolled in that course. Otherwise,
   the level is defined here */
CREATE TABLE enrollments (
  id INT NOT NULL PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  level INT NOT NULL,
  /* Each student may be enrolled only once per course */
  UNIQUE KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students (id),
  FOREIGN KEY (course_id) REFERENCES courses (id)
);

最后，您可以为每门类(class)分配的作业创建一个表:

CREATE TABLE assignments (
  id INT NOT NULL PRIMARY KEY,
  course_id INT NOT NULL,
  description TEXT
  /*... other columns related to the assignment as necessary*/
);

并在必要时让学生完成作业:

CREATE TABLE student_assignments (
  student_id INT NOT NULL,
  assignment_id INT NOT NULL,
  assignment_body TEXT, /* or whatever... */
  /* Or to track when completed */
  submitted_timestamp TIMESTAMP,
  PRIMARY KEY (student_id, assignment_id),
  FOREIGN KEY (assignment_id) REFERENCES assignments (id),
  FOREIGN KEY (student_id) REFERENCES students (id)
);