So I'm currently designing a database for a web site and intra net for my
campuses student radio. Since I'm not getting paid for this and I'm doing
this in my free time I kinda want to take my time and have the system setup
as "perfectly" as any one college student can.
I'm currently debating on how I want to store all the songs in the system.
Namely I'm not quite sure which way will eek the most performance out of
My plan so far is quite normalized, a songs table with 1:1 relationships
with an Albums, Artists, and Genres table.
The big benefits I see from this is when I'm building the intra net
application I'll want to pull just all of the artists or all of the albums,
etc. However I feel like I'm encountering issues with where to store the
"year" field of an mp3 (do I want it on the album, song, or both) along with
issues like printing everything out at once.
The only other way I can think of thats relatively efficient is to have the
singular songs table and have indexes on albums, artists, and genres.
My question, more out of curiosity than necessity, is which of these would
be more efficient (given that I'll be using the InnoDB storage engine)?
Other relevant facts include it'll be using the latest, stable release of
MySQL 5 and I'll be developing in PHP5 (through CakePHP's database
Thanks and Gig 'Em!