Do you really mean 1:1 relationships? A song might have more than one
artist, album, or genre. Could a song have no album at all, such as
something that was released in MP3 format? Also, you might have more than
one version of a song. You might need a separate table for recordings, with
each song having one or more recordings. That would be a good place to keep
the release information, rather than storing it with the song.
The biggest problem would be to figure out how to index the songs,
themselves. I don't have a good suggestion for that off the top of my head.
That all being said, there's no reason I can think of not to normalize the
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: Daniel Cousineau [mailto:dcousineau@stripped]
> Sent: Tuesday, April 03, 2007 12:15 AM
> To: mysql@stripped
> Subject: Joins versus Grouping/Indexing: Normalization Excessive?
> 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
> 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
> abstraction layer).
> Thanks and Gig 'Em!
> Daniel Cousineau