[ILUG] Comments from DBA's requested.

[Conor Daly]

On Tue, May 18, 2004 at 05:49:00PM +0100 or so it is rumoured hereabouts, 
John Gay thought:
> I've been trying to get my head around data normalization. The references I 
> have are:
> 
> MySQL Tutorial by Welling and Thomson
> Managing & Using MySQL 2nd Edition from O'Reilly
> 
> Of the two, the O'Reilly book is slightly more helpful in explaining 
> normalization. 
> 
> I've followed the CD Catalog example but added my own requirements.
> 
> I want to track my CD Collection and store info about CD titles, song titles 
> and artists. 
> 
> Since each CD contains many songs, I split SONG into a seperate table from CD.
> 
> Since artists release many songs and CD's, I split ARTIST into a seperate 
> table. Now I've this relationship:
> 
> ARTIST---<SONG>----<CD
> 
> Each song has one and only one artist.
> Each artist has one or more songs.
> Each song is on one or more CD's.
> Each CD contains many songs.
> 
> To resolve the many-to-many relationship between SONG and CD I created a 
> TRACK table to go between SONG and CD. This also serves to store track numbers 
> that I had overlooked. Now my tables look like this:
> 
> ARTIST---<SONG---<TRACK>---CD

Um, why not have the lot in a single table?  Something like:

ID | artist | song | cd | track |

You just have to generate multiple indexes if the table gets too big and
you can do pretty much any query on that table.  Note ID which is unique
to each row.

Conor

-- 
Conor Daly <conor.daly at oceanfree.net>

Domestic Sysadmin :-)
---------------------
Faenor.cod.ie
  8:06pm  up 208 days, 12:15,  0 users,  load average: 0.00, 0.01, 0.16
Hobbiton.cod.ie
  7:55pm  up 56 days, 23:22,  1 user,  load average: 0.01, 0.04, 0.00