List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:April 3 2007 1:19pm
Subject:RE: Joins versus Grouping/Indexing: Normalization Excessive?
View as plain text  
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
data.

Regards,

Jerry Schwartz
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
> 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).
>
> --
> Thanks and Gig 'Em!
> Daniel Cousineau
> http://www.terminalfuture.com/
> http://www.linkedin.com/in/dcousineau
> dcousineau@stripped
>



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