Hello,

I appreciate the responses regarding my last post. I'm still having problems though with the concept of a multi product ecommerce database. Again, I am interested in managing various products such as music, apparel and accessories. I began diagramming out the music related tables for the database which is located http://www.faultline-media.com/erd/index.html. I would appreciated any feedback on this diagram that any could provide.

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. 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.

Thanks in advance

-Jason

Sounds like you have a supertype/subtype structure. You would have a supertype called "products" with common attributes for products and the subtypes "music", "apparel", "accessories" with their unique attributes and related tables.