MySQL Optimization – Join Multiple Tables for Aggregate or Create Single Table

database-designmariadbMySQLoptimization

I have the following ER diagram.

enter image description here

Now i have option of going with a single table ARTDATA as mentioned,
or,
I can go with 13 different tables linked with each other with relations.

Problem is for each art i am gonna have to display all the attributes, i.e. For each ART i'll have to fetch TYPE, AUTHOR, TIMEFRAME, FORM, LOCATION AND SCHOOL. And for that i'll have to join all these tables together and that might take time.
However i can always go with the alternative of single table ARTDATA and get everything in one place.

Given my data set is large (50000+ entries) and will grow in future, what would be best solution to go with?

Assuming i am going with 13 tables, what would be the best query to merge all tables? I'll put my pathetic attempt below.

select 
t1.ID,
t1.TITLE,
t1.DATE,
t1.TECHNIQUE,
t1.URL,
t3.AUTHOR,
t3.BORN_DIED,
t5.FORM,
t7.LOCATION,
t9.SCHOOL,
t11.TIMEFRAME,
t13.TYPE

from
ART t1 inner join 

ART_AUTHOR t2 on t1.id=t2.art_id inner join 
AUTHOR t3 on t3.id=t2.author_id inner join

ART_FORM t4 on t1.id=t4.art_id inner join 
FORM t5 on t4.form_id=t5.id inner join 

ART_LOCATION t6 on t1.id=t6.art_id inner join 
LOCATION t7 on t6.location_id=t7.id inner join 

ART_SCHOOL t8 on t1.id=t8.art_id inner join 
SCHOOL t9 on t8.school_id=t9.id inner join 

ART_TIMEFRAME t10 on t1.id=t10.art_id inner join 
TIMEFRAME t11 on t10.timeframe_id=t11.id inner join 

ART_TYPE t12 on t1.id=t12.art_id inner join 
TYPE t13 on t12.type_id=t13.id;

You get the gist, that is barbaric.

UPDATE:

I have updated the tables and created a view called ARTDATA using above query. Now here is the update ER.

UPDATED ER

Best Answer

You have 6 many-to-many tables linking one table to 6 other tables??

If any of them is just one-to-many, toss the linking table.

Normalization is good; over-normalization is bad.

Why have a table LOCATION if the only thing it contains is a column LOCATION?

Many-to-many tables are somewhat inefficient. (See this for a partial solution: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table ).

On the other side, 50K rows is a "small" table. It might be 50MB in size, which should easily fit on your server.

Normalization is useful for:

  • Isolating names so they can be changed in a single location (is this needed?), or
  • For saving space (see below), or
  • Collecting multiple attributes together (eg, AUTHOR)

I think I have argued against the need for saving space.

In the case of AUTHOR, there are multiple columns, so having an AUTHOR table makes sense because of that.

Related Question