List:General Discussion« Previous MessageNext Message »
From:Micah Stevens Date:April 3 2007 4:32am
Subject:Re: Joins versus Grouping/Indexing: Normalization Excessive?
View as plain text  
I think you're approaching this from the wrong angle. You'll want to put 
the data at the highest level at which it changes.

i.e. If every song on an album is always the same year, put it at the 
album level, however, if it changes from song to song on a particular 
album, then you want it at the song level.

Year wouldn't ever apply to artist I don't think, unless they're truly a 
one hit wonder. :)

-Micah

On 04/02/2007 09:14 PM, Daniel Cousineau wrote:
> 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
> MySQL.
>
> 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
> abstraction layer).
>
Thread
Joins versus Grouping/Indexing: Normalization Excessive?Daniel Cousineau3 Apr
  • Re: Joins versus Grouping/Indexing: Normalization Excessive?Micah Stevens3 Apr
  • RE: Joins versus Grouping/Indexing: Normalization Excessive?Jerry Schwartz3 Apr
    • LEFT/RIGHT Joins not workingmurthy gandikota3 Apr
      • Re: LEFT/RIGHT Joins not workingMichael Dykman3 Apr
        • Re: LEFT/RIGHT Joins not workingmurthy gandikota3 Apr
          • Re: LEFT/RIGHT Joins not workingMogens Melander4 Apr
            • Re: LEFT/RIGHT Joins not workingmurthy gandikota4 Apr