RE: Joins versus Grouping/Indexing: Normalization Excessive?
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:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 12:15 AM > To: mysql@lists.mysql.com > 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 > [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins versus Grouping/Indexing: Normalization Excessive?
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). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
