Are there big disadvantages (maybe in query speed etc.) of using only ONE combined lookup table (mySQL database) to store "links" between tables over having individual lookup tables? I am asking because in my project scenario I would end up with over a hundred individual lookup tables, which I assume will be a lot of work to setup and maintain. But to make an easier example here is a simplified scenario between only 4 tables:
Table: teacher
| teacherID | name |
|---|---|
| 1 | Mr. X |
| 2 | Mrs. Y |
Table: student
| studentID | name |
|---|---|
| 4 | Tom |
| 5 | Chris |
Table: class
| classID | name |
|---|---|
| 7 | Class A |
| 8 | Class B |
Table: languageSpoken
| languageSpokenID | name |
|---|---|
| 10 | English |
| 11 | German |
======================= INDIVIDUAL LOOKUP TABLES ==========================
Table: student_teacher
| studentID | teacherID |
|---|---|
| 4 | 1 |
| 5 | 1 |
Table: student_class
| studentID | classID |
|---|---|
| 4 | 7 |
| 5 | 8 |
Table: student_languageSpoken
| studentID | languageSpokenID |
|---|---|
| 4 | 10 |
| 4 | 11 |
====== VS ONE COMBINED LOOKUP TABLE (with one helper table) =====
helper table: allTables
| tableID | name |
|---|---|
| 1 | teacher |
| 2 | student |
| 3 | class |
| 4 | languageSpoken |
table: lookupTable
| table_A | ID_A | table_B | ID_B |
|---|---|---|---|
| 1 | 1 | 2 | 4 |
| 1 | 1 | 2 | 5 |
| 3 | 7 | 2 | 4 |
| 3 | 8 | 2 | 5 |
Your 2nd lookup schema is absolutely unuseful.
You refer to a table by its name/index. But you cannot use this relation directly (tablename cannot be parametrized), you need to build conditional joining expression or use dynamic SQL. This is slower.
Your lookup table is reversable, i.e. the same reference may be written by 2 ways. Of course, you may add CHECK constraint like CHECK table_A < table_B (additionally it avoids self-references), but this again degrades the performance.
Your lookup does not prevent non-existent relations (for example, class and language are not related but nothing prevents to create a row for such relation). Again, additional constraint and decreased performance.
There are more disadvantages... but I'm too lazy to list them all.
Another very important point: Foreign key constraints assuring referential integrity cannot be used in the "combined lookup" approach. They needed to be simulated by complex and error prone triggers. Overall the "combined lookup" approach is just a horrible idea. – sticky bit
There is a rule - non-relational relations must be separated.
In the 1st scheme - does a student may study in more than one class at the same time? If not then you do not need in student_class lookup table, and class_id is an attribute in student table.