While listing a product in a catalog every product has a few general attributes like mrp, price, sku, product description etc etc, which is applicable to all.
But there are few attributes which are product specific, for example, T-shirts can have color and size, laptops have memory, resolution, processor speed, mobile phones can have memory, camera resolution, and operating system.
Each of the up above attributes in the example is product specific and important.
Taking a cue out of opencart ecommerce, I believe, I replicated their database with a few changes (to suit my needs) and here it is.
Products
The idea is to record product information/attribute (which is applicable to all as described in the first paragraph) to the product table in the product table.
Attributes
Then comes the attributes table where we store the attributeName eg, resolution, memory, color, size, processor speed etc etc
Product_Attribute
This is the table where we save the productId of the product table, attributeId of the attributes table and the value of the attribute to attributeValue eg . resolution = 15000pixels(attributeValue), memory = 500gb (attributeValue), color = blue (attributeValue).
Up till now we have all three tables with records which now need to displayed on a product detail page somewhat like this
Opencart, if not mistaken, uses a left join statement to combine its product, attribute and product_attribute to display the product on their product detail pages and this is what I need to do.
How do I use the left join statement to display product, attribute and product_attribute to display products and their attributes? Please note that there can be a few products which needs no extra attribute.
Unfortunately, going full recursion with a left join is one of the things SQL is not very good at. In fact, I'm pretty sure this is impossible, at least in standard SQL.
A decent alternative would be to use simple inheritance by having your main "products table" as the base class, and extending other different tables types (such as "cell phone" or "t-shirt"). Extension, in this case, can be easily performed by having different new tables and have all of them have a foreign key to the products table.
Then if you don't have a way to distinguish the type of a product in your product table, add a "kind" or "type" attribute and in your application perform a left join based on that attribute, where needed.