相对MySQL数据库

Question

I have a MySQL database table which is built as follows. The numbers below the classes denote a "level" for the class, such as high, medium, or low, with 0 being does not attend that class.

I need to build another table to save homework assignments for each class, but I'm a bit lost as to how I'd build that table, specifically denoting what the exact columns of the rows have to be, since each class does not have something specific that denotes it.

Answer 1

A properly normalized structure would have a separate table for courses, giving each an id, and another table placing students into courses by including a student id, course id, and level.

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)
);

Finally then, you can create a table for assignments assigned in each course:

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

And for students to complete assignments if 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)
);