Hi Beauford, You are on the right track. Yes you should remove the songs to a separate table. If you merely duplicate the first example (below) in two tables you have created a "one-to-many" relationship between the Album table and the Songs table - one album has many songs on it. Your query code applies.
However the Songs table still has duplication in it, eg 3.song appears thrice, and we can't have that! The problem is, if you cut down the Songs table entries so that 3.song appears only once, how do you link to multiple Albums? So now you are into a "many-to-many" relationship between the Album table and the Songs table - one album has many songs on it AND one song may appear on several albums. Problem! You should not have a field with 'repetition' within it, eg for each album hold two fields: ID, and Songs - where songs is a list of IDs/titles. This is not 'relational'. You can read up about a process known as "normalisation" which teaches a basic three step process (some go to more than three steps, but let's learn to walk first...) This normalisation process helps to organise data into 'sets', and as RDBMS/SQL work on sets of data/use set theory to drive events, this is the way to go... So that said, let's proceed. Yes you should set up the song table to have columns such as ID and Title. The ID should be unique and each Title should appear at most once within the table. (It may help to draw a diagram here, with boxes representing tables, split into columns and rows, and with arrows depicting relationships between tables and the individual data items sitting in col-row combinations) To handle the m-m relationship, create another table and place it 'in the middle' - to act as a 'translator' of the m-m relationship. This table will contain AlbumID and SongID columns, and the data fields contained will NOT (necessarily) be unique. (draw another diagram to compare with the above/initial format) Now you can start from Album and code a join to the 'translator' table to find the IDs of every song appearing on the album, and then join any 'hits' from that 'relationship' to the Songs table to realise the titles, etc, detail. Conversely, you can start at the Songs table and by joining to the 'translator' table work out which Album(s) a song appears on by AlbumID, and then join to the Album table to find out titles and other album details. Some people start from card-box/shoe-box databases (which are really ordered files), and others from 'DBMS' that only 'relate' two tables at a time. If having multiple joins looks a bit 'hairy' to you, do not fear, this is what relational databases are all about, and joins featuring five to ten tables are quite 'routine'. MySQL will have no trouble performing (the logic, and at speed)! Regards, =dn > I have a database of albums by a group and I want to be able to search on > this table to find out what songs are duplicated on what albums (there are > 36+ albums). The problem is how do I set up the database. > > Example: > > Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song > Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song > Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song > > So 3.song appears on all 3 albums. > > Currently I have it set up with two tables as shown below, but I am thinking > there has to be a better way to do this than to duplicate the name of the > song three, four, or five times in the table. > > Table AlbumName > > Album ID > ==== == > Album1 1 > Album2 2 > Album3 3 > > Table SongTitle > > Song ID > === == > 3.song 1 > 3.song 2 > 3.song 3 > 7.song 3 > etc. > etc. > > So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle > WHERE AlbumName.ID=SongTitle.ID; > > Given the setup above, is there a way that I can put in the SongTitle.ID > field that song appears on more than one album. Maybe something like: > > Song ID > === == > 3.song 1, 2, 3 > > But then what would my search be. > > Sorry for the length of this, but I am learning MySQL and trying to get a > handle on all of it. My way works, but I'm sure there has to be a better > way. > > Any thoughts are appreciated. > > TIA, Beauford > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
