0

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

Note: This is not about storing a string of ids, I already know how to do that, and I know it's not the best way, that's why I was asking this question specifically... please don't mark it as "duplicate" to Is storing a delimited list in a database column really that bad?... it is a good question/answer, but it doesn't answer my question here.

Jacob Hornbeck
  • 398
  • 2
  • 19

1 Answers1

2

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.

See also my answer to: Is storing a delimited list in a database column really that bad?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • What exactly is the fourth line doing? `PRIMARY KEY (user_id, professor_id)` looks like it is setting the primary key to both `user_id` and `professor_id`... can you do that? – Jacob Hornbeck Sep 25 '21 at 19:13
  • @JacobHornbeck Yes you can do that, it's called a compound key. Do not fall into the trap of making a separate key, just use the two join columns – Charlieface Sep 25 '21 at 20:11
  • @Charlieface Okay, thanks! I guess I was never taught that in the classes that went into SQL... – Jacob Hornbeck Sep 25 '21 at 21:45
  • 1
    It means you can have only one row for each unique _pair_ of values, but there can be many rows that store a given value in either column. E.g. many rows with the same student's user_id, as long they have different professor_id's, or vice-versa. – Bill Karwin Sep 25 '21 at 22:10