Allow One Column to Relate to Multiple Rows in Another Table-CodePudding

For an assignment, we are supposed to be "reverse-engineering" a website and try to recreate the database structure. I have all but one column done, and I am not sure how to make it work.

For this assignment, I chose to "reverse-engineer" the Rate My Professors website. I have 4 tables: users, reviews, professors, and schools. In the website, you can save multiple professors, but that is the problem I am having for this... I don't know what datatype to set the "saved_professors" column of the "User" table. I want it to have the ids of each professor in an array, but as far as I know, you can't have the data type as an array (SET and ENUM seem close, but I am pretty sure that those won't work the way I need it to...)

Is there a way to do this? Or should I just set it to a VARCHAR or TEXT (which would just hold a list of the ids in a string)?

CodePudding user response：

You need another table.

What you're describing is a many-to-many relationship. A student can save many professors, and likewise a given professor may be saved by many students.

Every many-to-many relationship should be stored as a set of rows in a new table:

CREATE TABLE saved_professors (
  user_id INT NOT NULL,
  professor_id INT NOT NULL,
  PRIMARY KEY (user_id, professor_id)
);

Store just one pair on each row in this table. This means that for each student, there may be many rows in this table.