I have ten tables (Product_A, Product_B, Product_C, etc.), each of them having a primary key pointing to a row in a the parent table Product.
Basically, I have applied the recommendations coming from the SQL antipattern book from Bill Karwin (this antipattern solution described here: https://fr.slideshare.net/billkarwin/practical-object-oriented-models-in-sql/34-Polymorphic_Assocations_Exclusive_Arcs_Referential )
In order to load a child product, I use something like this:
SELECT * FROM Product
LEFT JOIN Product_A USING (product_id)
LEFT JOIN Product_B USING (product_id)
LEFT JOIN Product_C USING (product_id)
LEFT JOIN Product_D USING (product_id)
WHERE product_id = 1337
etc.
I fear that the more types of child table products I get, the more JOIN clause I will have to add, causing the query to end up incredibly slow.
Is using LEFT JOIN in order to prevent polymorphic associations antipattern still a solution if we work on tens of sub child tables?
Should I start thinking of up using a query on parent table Product in order to grab a "product_type" and then execute another query on the appropriate child table depending on the value stored in the "product_type" column in the parent table?
Update: first replies on this topic state that this is bad design and that I should create a single table combining the colums from the child tables. But each product type has its own attributes. To say it otherwise: "A TV might have a pixel count, but that wouldn't make much sense for a blender." @TomH
Thank you
MySQL has a hard limit on the number of joins. The limit is 61 joins, and it's not configurable (I looked at the source code and it's really just hard-coded). So if you have more than 62 product types, this is not going to work in a single query.
If the data were stored in the structure you describe, I would run a separate query per product type, so you don't make too many joins.
Or do a query against the Product table first, and then additional queries to the product-type specific tables if you need details.
For example, when would you need to gather the product-specific details all at once? On some kind of search page? Do you think you could design your code to show only the attributes from your primary Product table on the search page?
Then only if a user clicks on a specific product, you'd go to a different page to display detailed information. Or if not a different page, maybe it'd be a dynamic HTML thing where you expand a "+" button to fetch detailed info, and each time you do that, run an AJAX request for the details.