Re: Need to return fewer rows than UNION (JOINs the answer?)

definitely normalised data! I have a table here that started with just three repeated columns, it's now at nine and it again needs to be increased, I'm now having to convert and rewrite to a normalised schema. A normalised schema is much more future proof. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . mr_tricky@hotmail.com wrote in message <8h37no$9l6$1@nnrp1.deja.com>... >In article <20000531065757.18975.00000933@ng-cs1.aol.com>, > gmurray88@aol.com (Glenn) wrote: >> Subject: RE: Need to return fewer rows than UNION (JOINs the answer?) >> From: gmurray88@aol.com (Glenn) >> Date: 5/31/00 3:30 AM Pacific Daylight Time >> Message-id: <20000531063024.26453.00001328@ng-fm1.aol.com> >> >> Thanks for the input on this issue. It's been a few years since I've >done >> database design and your input is helping me get back on track. >> The example I used of Products and Parts doesn't exactly reflect >the design >> issues we're facing. If it were a case of secondary data (Parts) being >a >> component of a product, then I would go with Obnoxio's redesign for >his aptly >> stated reasons. >> The actual situation is that we're creating a database consisting >of music >> CD's and CD tracks, searchable by Artist, Song, Songwriter, and more. >> The purpose for now is selling CDs online, also looking toward >digital >> music online (e.g. MP3). >> The specific issue we're looking at is that each CD title can have >several >> artists. There will be a maximum of about 4 before the artist becomes >"Various" >> Also, each CD track can be composed of more than one song and have >more >> than one artist. >> We could have a table for CD titles with one row per title (e.g) >> >> CD_ID TITLE Artist1_ID Artist2_ID etc... >> 1 Murmur 45 null >> 2 Three Tenors 56 47 >> >> Using Rice's suggested outer joins, a query would return one row per >title >> (e.g.) >> >> CD_ID TITLE Artist1_Name Artist2_Name >1 >> Murmur REM null >> 2 Three Tenors Pavarotti Domingo >> >> This could be easily ordered by Artist1_Name. >> >> Using Obnoxio's suggested re-design into two tables (e.g.) >> CD_Titles table >> >> CD_ID TITLE >> 1 Murmur >> 2 Three Tenors >> >> CD_Artist table >> >> CD_ID Artist_ID >> 1 45 >> 2 56 >> 2 47 >> ... >> >> A query would return: >> CD_ID TITLE Artist_Name >> 1 Murmur REM >> 2 Three Tenors Pavarotti >> 2 Three Tenors Domingo >> ... >> >> This could not as easily be primarily sorted by artist because the >primary sort >> field would have to be CD_ID. I know there are ways around this by >processing >> the output more. But is it worth it? >> Maintenance, indexing, and performance are obviously big factors in >the >> database design. Obnoxio's suggested re-design sounds more "pure" to >me as >> ideal database design goes. >> Should the "pure" (normalized, relational, etc) design be strived for >and the >> output tweaked later? >> Or is the more "klugy" design actually a better idea in this case. >Any more >> ideas, esteemed colleagues? >> >> Thanks, >> Glenn >> > >I'm with the Clown on this one. Having done something similar with >football (soccer to our Stateside chums) teams I much favour the >normalised approach. To illustrate the point: > >Depending on the matches played, squads can have from 14 to 22 players, >so do you have 22 columns just in case, with 1/3 empty for most of the >time, or do you normalise it so you can have as many as you need? (And >cope without having to alter the table when they up it to 25?!) > >In your case, are you sure that you'll always have a max of 4 artists? >Will it become 5 (extra column required, another outer join, almost all >sql needing to be rewritten) but if its normalised just add another row. > >Also should you want to group by artist, it's far easier in the >normalised form (ORDER BY artist) than if you have to search 4 columns >for any mention (by whatever method) > >I find the ease of maintainence and simpler sql (one join to artist >instead of 4 outers for example) makes it worth the extra juggling at >report time. > >Just my two pence worth. > > >Sent via Deja.com http://www.deja.com/ >Before you buy.