I'm designing a simple mp3(music) ecommerce site, customers can either buy a single Track or whole Album, an Album is made up of Tracks, so there is a one to many relationship between an Album and a Track as shown below. I thought it would be a good idea to have one Orders table and one Product table, the idea I had is to have a generic Product table to represent either a Track or an Album as a Product but I'm kinda struggling to design that on my ERD. I was thinking in a inheritance approach. all Tracks will have the same price but Albums will have an individual prices.
What is the best way to achieve this requirement? in the orders table I need to see if it was a Track or Album purchased and money spent.
I have searched for examples and samples without any luck.
An example will also help a lot.
Best Answer
There really is not a One True Answer, but there are plenty of samples available on the web. One site is: http://www.databaseanswers.org/data_models/
This is actually a site with many simple data models. I would not call these a complete solution, but it is a good resource for getting started.
For a CD Collection there is a sample model, that focuses on content, not on the commercial side of selling content.
http://www.databaseanswers.org/data_models/cd_collection/index.htm
In addition if you search for something like example database model music you will find other examples.