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

Improve this question
3
  • 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. Commented Sep 24, 2019 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. Commented Sep 24, 2019 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. Commented Sep 24, 2019 at 18:29

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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? Commented Oct 6, 2019 at 9:28
  • @Mercurial - Sounds good. Commented Oct 6, 2019 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.

Improve this answer
2
  • 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? Commented Sep 25, 2019 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. Commented Oct 6, 2019 at 2:03

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.