I have the following ER diagram.
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.
I have updated the tables and created a view called ARTDATA using above query. Now here is the update ER.