Data Modeling Quandary

[grubber]

Hello All

I'm struggling with a Data Modeling issue, hoping someone can point me in the right direction before I take the leap.

I built a solution for a record label a number of years ago (in the days before mp3s)

The core relationship (that I'm struggling with) was.

Product (eg, CD) > Tracklist < Tracks

Today in the digital age the tracks are both attributes of the physical product (tracklisting on a CD) and a digital product in own right. For example on itunes you can now buy a single track of an album as well as the full album.

My initial thought is move to a single Product table (many to many self relationshio - with Albums as bundles and tracks as products), a bit like a bill of materials solution. However where does that leave me with the physical product? as there are no sales of individual tracks just CDs (i.e my old model).

To make matters more comlicated the 'Tracks' table has relationships to writers, artists etc. And the Product has many different relationships (suggesting they are very different entities)

Anyone thoughts great oracle?

[mr_vodka]

I would probably go with Tracks as your product and album as a discount price on the tracks. IOW, if one were to buy the whole album (as in your old model) they would get all the tracks at a discounted price of $.45 cents are oppossed to $.99 for single but your product would still be those individual tracks.

BTW you may want to take a look at this as well.

http://www.jonathanstark.com/recursive_data_structures.php

[grubber]

I would probably go with Tracks as your product and album as a discount price on the tracks. IOW, if one were to buy the whole album (as in your old model) they would get all the tracks at a discounted price of $.45 cents are oppossed to $.99 for single but your product would still be those individual tracks.

BTW you may want to take a look at this as well.

http://www.jonathanstark.com/recursive_data_structures.php

Thanks John. My gut tells me thats the right approach but its uncharted territory for me so I'll have to run some scenarios to get my head round it. Its esp difficult to grasp where reporting and royalties are concerned on sales of CD's/Vinyl (tangible bundles or albums) and Digital product (non tangible bundles or albums AND individual tracks)

Ps. Already knee deep in Johnathan's examples. Nothing short of brilliant.

Olly