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
>> 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:
>> 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:
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 Daly <conor.daly at oceanfree.net>
Domestic Sysadmin :-)
8:06pm up 208 days, 12:15, 0 users, load average: 0.00, 0.01, 0.16
7:55pm up 56 days, 23:22, 1 user, load average: 0.01, 0.04, 0.00
Maintained by the ILUG website team. The aim of Linux.ie is to
support and help commercial and private users of Linux in Ireland. You can
display ILUG news in your own webpages, read backend
information to find out how. Networking services kindly provided by HEAnet, server kindly donated by
Dell. Linux is a trademark of Linus Torvalds,
used with permission. No penguins were harmed in the production or maintenance
of this highly praised website. Looking for the
Indian Linux Users' Group? Try here. If you've read all this and aren't a lawyer: you should be!