I'm having problems with the concept of a multiple product type ecommerce database. I am interested in managing various products such as music, apparel and accessories.
At it's most basic level, a product has attributes such as a SKU, a description, a price and a quantity on hand. The problem is that I want to be able to search for a product in multiple ways. For instance, retrieve ALL albums by a particular artist or retrieve ALL tracks on a particular album or retrieve ALL albums in a particular genre etc. To do this, I obviously need much more information than just the simple product data. How do I incorporate the different types of products into the general structure of the product tables?
I have included a product type table which I imagine I could use to perform the correct application logic depending on the value. This way I know that if the product is of the type "Music", then I can perform the correct SQL queries to retrieve the data.
Add to this, the fact that I also want to include apparel and accessories which have their own unique attributes as well. This is where I have become a bit confused. If I'm making any sense here then great. If anyone has worked on this type of database before I would greatly appreciate some pointers or examples.