I'm a database admin and developer in MySQL. I've been a few years dealing with MySQL. I lately adquire and focus O'Reilly High End MySQL second Edition to enhance my abilities on MySQL advanced features, high end and scalability, because I've frequently been frustated by the possible lack of advance understanding of MySQL I'd (as well as in a large part, I have).

Presently, I'm focusing on a ambicious web project. Within this project, we'll have quite content and customers in the begining. I'm the designer from the database which database should be extremely fast (some card inserts but mostly and much more important READS).

I would like here to go over about these needs:

  • You will see several type of products
  • The products possess some fields and relations in common
  • The products also provide some fields and relations special which make them differents one another
  • Individuals products must be listed all together purchased or strained by common fields or relations
  • The products must be also listed only by type (for examble item_specialA)

I've some fundamental design doubts, and I'd like you that helped me to decide and learn which design aproach would get better because of a higher performance MySQL database.

Classical aproach

The next diagram shows the clasical aproach the firstly you may consider using the mind thinking in database: Database diagram

Centralized aproach

But maybe we are able to improve it with a few or pseudo object oriented paradigm centralicing the most popular products and also the relations on a single common item table. It might be also helpful for listing all type of products: Database diagram

  • Pros and cons for each one of these?
  • Which aproach can you choose or which changes can you apply seeing the needs seen before?

Thanks all ahead of time!!

That which you have are two distinct data mapping methods. That you simply known as "classical" is "one table per concrete class" in other sources, which you known as "centralized" is "one table per class" (Mapping Objects to Relational Databases: O/R Mapping In Detail). Both of them get their pros and cons (go here above). The queries within the first strategy is going to be faster (you will have to join only 2 tables versus 3 within the second strategy).

I believe that you ought to explore classic supertype/subtype pattern. Listed here are some examples in the SO.

If you are searching mostly for speed, consider selective utilization of MyISAM tables, make use of a centralized "object" table, and merely one additional table with correct indexes about this form:

object_type object_id property_title property_value

user 1 photos true

city 2 photos true

user 5 single true

city 2 metro true

city 3 population 135000

and so forth... searches on primary secrets or indexed secrets (object_type, object_id, property_title) for instance is going to be blazing fast. Also, you reduce the necessity to finish with 457 tables as new qualities appear.

It is not exactly a properly-designed nor perfectly-stabilized database and, if you're searching for a lengthy-term large site, you should look at caching, or at best utilizing a denormalized paradigm, denormalized mysql tables like that one, redis, or possibly MongoDB.