1

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

  • This is a normalization v/s denormalization question; and you can find 100s of questions answering the same and most of them suggesting that normalization should be done. So, yes you need multiple tables. – Madhur Bhaiya Sep 24 '19 at 18:26
  • @MadhurBhaiya Assuming i am going with 13 tables, what would be the best query to merge all tables? Kindly focus on this then. – Mercurial Sep 24 '19 at 18:27
  • You have only one option afaik..JOIN them with their respective relationships. You dont need to worry about performance, if your tables are indexed properly. – Madhur Bhaiya Sep 24 '19 at 18:29
1

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.

  • 1
    1. Collecting multiple attributes together (eg, AUTHOR) : I plan to have multiple attributes in the future for LOCATION, TIMEFRAME, TIME, SCHOOL, TYPE too so keeping them in seperate table makes sense. 2. However, i will only have one to many relation with ART and others, so i can drop all of linking tables i suppose and insert all the respective IDs in ART table. Makes sense? – Mercurial Oct 6 '19 at 9:28
  • @Mercurial - Sounds good. – Rick James Oct 6 '19 at 18:22
1

If you need to have multiple values (like one ART having several AUTHORs), you need this kind of linking. If that is not needed, just have all the ids in ART table.

I'd recommend you move to ART all attribute ids where a single value is enough and leave only the multivalued ones using the more complex kind of linking via external link table.

  • I only have one to many relation with ATTRIBUTES to ART, i.e. a single author can have many arts but a single art will have only single AUTHOR, FORM, TYPE, SCHOOL etc. I have updated the database and created a view using above query, as since all the ID's are indexed, i hope its ok so far? – Mercurial Sep 25 '19 at 13:57
  • @Mercurial - Since it is 1:many, the table Art_author is not needed. Simply have Author_id in ArtData. Faster, simpler, less barbaric. – Rick James Oct 6 '19 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.