CODEWITHSUNDEEP
mysqldatabase-designlookup-tables
Tom Senner
Tom Senner

Reputation: 548

Disadvantage of "combined" lookup table in mySQL vs individual lookup tables

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

Upvotes: 0

Views: 106

Answers (2)

Akina
Akina

Reputation: 42611

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.

Upvotes: 2

user9706
user9706

Reputation:

Lookup tables are usually static so there shouldn't be much maintenance overhead. If you update the lookup data, however, now have to manage the life cycle of a subset of rows of your single lookup table which may get tricky opposed to just truncating a table when new data becomes available. Where I would be careful if your lookup table have different schemas with columns have to be null as they apply to a given "type" of row. You may not be able to implement the right foreign keys. If you happen to use the wrong id, you would get a nonsensical value. Those help you keep your data consistent (in production systems). If this is school project, especially a database class, you will be dinged for not using textbook normalization.

Upvotes: 0

Related Questions