How to link one table with many different tables?

Question

I have a list of tables, that is, student, teacher, staff, department. etc., and each of these tables contains its own comments. Now, one record in a table can contain one or more comments that show the relationship from one to many from any table in the comment table. I do not know how best to associate a comment table with each of them. If I put a foreign key from the comments table into each of these tables, it will look like 40-50 fields depending on none. tables. Secondly, if I add a foreign key from each of these tables to the remark table, will it be like repeating the whole row for only the second foreign key of the foreign note? Similarly, if I use only one field in each table as comments, I will actually store the rows in only one text field.Any suggestions for an effective solution?

Answer 1

Suppose your tables (student, teacher, staff, department) have an internal primary key called Id.

In the comment table, you can create a table.

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

The line in the comments might look like this:

1,'Student',347,'text'

Answer 2

You can use many-to-many staging tables. Each base table (student, professor, etc.) would have an alternative ego storing one foreign key in the base table (for example, student_id) and one foreign key in the commments table. You practically double the number of tables, but you do not need to modify existing tables, and you get full flexibility.

Answer 3

" ", .

, :

, :

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

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

:

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

, . , , :

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

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

. !

(, , )

, , , , , , :

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

(, , ... )

Answer 4

, , , -childs.

, , .

comment_id ? _ _ id approriate.

Answer 5

:

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

, , , , .

, .

Answer 6

.

, ? .. ..

Answer 7

My 50 cents: the Zoredache solution is certainly good, but I discourage the use of enumerations; they are not very smart in mysql: if you specify an unknown value, the error appears as an empty string - even if some default is given. Also, it's insanely long for ALTER if you want to change or add a new type. unsigned tinyint should be enough for most of your needs ...