How do I relate one table to many different tables?

Question

I have a list of tables i.e. student, teacher, staff, dept. and so on and each of these tables have comments specific to them. Now one record in a table can have one or many comments that shows it's a one to many relation from any table to comments table. I don't know what the best way is to relate comments table to each of these. If I put foreign key from comments table to each of these tables, it will be like 40-50 fields depending on no. of tables. Secondly if I add foreign key from each of these tables to remarks table, it will be like repeating whole row just for the second remarks foreign key? Similarly if I use just one field in each table as comments, I will be actually storing rows in just one text field. Any suggestions on how to achieve efficient solution?

Answer 1

Lets assume that your tables (student, teacher, staff, dept) all have a int primary key named Id.

For your comments table you could create a table.

Id int
CommentType enum (student, teacher, staff, dept)
LinkId int
Comment

A row in Comments might look like this

1,'Student',347,'text'

Answer 2

is a student or teacher or staff not just a type of person..

so you have a person and a person can have many comments? so you have a personscomments table with a relation to that person and why have a remarks table..is a remark not just a type of comment..

its hard to see without a more in-depth schema

Answer 3

You could use intermediate "many-to-many" tables. Each base table (student, professor, etc.) would have an alter ego storing one foreign key to the base table (e.g. student_id) and one foreign key to the commments table. You practically double your number of tables but you don't need to modify existing tables and you get full flexibility.

Answer 4

If you want to keep the foreign-key constraint, you need to have a table that handles the mapping for each and every table that will have comment-childs.

Meaning, Comment will have a primary key, with a foreign key constraint to each and every table that handles the mapping.

then, in the mapping-table, you have comment_id and ????_id with a foreign key constraint to the approriate table.

Answer 5

Your comments table could look as follows:

CommentID (int) - Primary Key
TableName (varchar(250)) - Table the comment is related to
RecordID (int) - the ID of the record in the table referred to
Comment (text) - the actual comment

You could of course add optional fields like a timestamp, which would let you select comments in the order they were entered.

Now you can use this table to store comments for any table, and you can select them by filtering on table name and record ID.

Answer 6

As this is a many-to-many relation, you migth might want to have a look at using an associative table.

Using your example, it might look something like this:

Your tables that can have comments:

+----------+------------+
| student  | student_id |
+----------+------------+
| Steve    |         12 |
| Larry    |         57 |
| Sunshine |         88 |
+----------+------------+

+--------+---------+
| dept   | dept_id |
+--------+---------+
| Math   |       2 |
| P.E.   |       5 |
| Drama  |      12 |
+--------+---------+

Then you need to keep track of the actual comments:

+-----------------------+------------+
| comment               | comment_id |
+-----------------------+------------+
| I love Math!          |          3 |
| Larry is my hero...   |          5 |
| Sunshine <3 Me!       |          6 |
+-----------------------+------------+

Now, you need an association between these tables, this is where your associative table comes into play. You now associate what student or dept has what comments, like this:

+------------+------------+
| student_id | comment_id |
+------------+------------+
|         57 |          5 |
|         57 |          6 |
+------------+------------+

+---------+------------+
| dept_id | comment_id |
+---------+------------+
|       2 |          3 |
+---------+------------+

This is both effective and elegant. Give it a shot!

(And to save you another question perhaps)

You could of course use just one association table if you are concerned about having so many association tables, but I would advice against it since it is not as neat and removes some possibilities for referential integrity checks that you can have with the first solution:

+-----------+------------+---------+
| entity_id | comment_id | entity  |
+-----------+------------+---------+
|        57 |          5 | student |
|        57 |          6 | student |
|         2 |          3 | dept    |
+-----------+------------+---------+

(Which in turn should prompt you to add a lookup table for those entities... but let's not go there)

Answer 7

My 50 cents: Zoredache solution is definitely good, but I discourage usage of enums; they are not very smart in mysql: if you specify an unknown value, the error is represented with an empty string - even if some default is specified. Also, it's crazily long to ALTER if you want to modify or add a new type. unsigned tinyint should be enough for most of your needs ...