Which of these MySQL database designs (attached) is best for mostly read high performance?

Question

I am a data base admin and developer in MySQL. I have been a couple of years working with MySQL. I recently adquire and study O'Reilly High Performance MySQL 2nd Edition to improve my skills on MySQL advanced features, high performance and scalability, because I have often been frustated by the lack of advance knowledge of MySQL I had (and in a big part, I still have).

Currently, I am working on a ambicious web project. In this project, we will have quite content and users from the begining. I am the designer of the data base and this data base must be very fast (some inserts but mostly and more important READS).

I want here to discuss about these requirements:

• There will be several kind of items
• The items have some fields and relations in common
• The items also have some fields and relations special that make them differents each other
• Those items will have to be listed all together ordered or filtered by common fields or relations
• The items will have to be also listed only by type (for examble item_specialA)

I have some basic design doubts, and I would like you to help me decide and learn which design aproach would be better for a high performance MySQL data base.

Classical aproach

The following diagram shows the clasical aproach which is the first you may think about with the mind thinking in database: Database diagram

Centralized aproach

But maybe we can improve it with some or pseudo object oriented paradigm centralicing the common items and the relations on one common item table. It would also be useful for listing all kind of items: Database diagram

---

• Advantages and disadvantages of each one?
• Which aproach would you choose or which changes would you apply seeing the requirements seen before?

Thanks all in advance!!

Answer 1

What you have are two distinct data mapping strategies. That you called "classical" is "one table per concrete class" in other sources, and that you called "centralized" is "one table per class" (Mapping Objects to Relational Databases: O/R Mapping In Detail). They both have their advantages and disadvantages (follow the link above). The queries in the first strategy will be faster (you will need to join only 2 tables vs 3 in the second strategy).