DL, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table.
Thanks again, Beauford ----- Original Message ----- From: "DL Neil" <[EMAIL PROTECTED]> To: "Beauford.2003" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question > Beauford, > [please reply to the list - there are always others 'lurking' who will learn > from the discussion (and use of the archives is a wonderful dream...)] > > You are correct, there will be duplication in the AlbumSong table - but not > within one field, only within one column. Album will consist of a unique ID > (IDA) and each Title will, one assumes be unique (unless two groups use the > same name - I guess that must be possible). Similarly Songlist will also > consist of a unique ID (IDS) and once again, one assumes that any repetition > of title will be two different songs that share the same name. These two ID > 'uniqueness' rules must apply for the next to work, ie you will most likely > define them as PRIMARY KEY columns. > > The 'translation' table, breaks the 'unique' 'rule', "So if song Q apears on > albums A, C, and E" as you ask, then we will see: > > AlbumSong > > IDA IDS > 1 1 > 2 1 > 3 1 > > Note that while "1" repeats in AlbumSong, it does not repeat (a) in > Songlist, nor in (b) one row/field of AlbumSong, eg > > IDA IDS > 1,2,3 1 > THE ABOVE IS TOTALLY WRONG!!! > > In the case of AlbumSong the table will not be able to have a PRIMARY KEY > column(s), but each of the individual columns should probably be INDEXed for > read-speed. > > Be aware that AlbumSong contains no real 'data' from an end-user > perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to > the other two tables. The Album and Songlist tables do all the 'data' work, > AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, > and/or vice-versa. > > Apologies if this was not clear, first time round, > =dn > > > > > DL, > > > > OK, that helps a lot, but I am still confused. I am still struggling with > > how you can not have duplication in at least one of the tables. A diagram > > here may help. So if song Q apears on albums A, C, and E - I don't quite > > understand how AlbumSong is going to be able to know which songs are > > duplicated without duplication of ID's. (There are 500 songs and only 36 > > albums). > > > > Thanks > > > > Album Songlist AlbumSong > > > > IDA TITLE IDS Song IDA IDS > > > > 1 A 1 Q ? ? > > 2 B 2 R ? ? > > 3 C 3 S ? ? > > 4 D 4 T ? ? > > 5 E 5 V ? ? > > ? > > ? > > ? > > ? > > ? > > ? > > > > > > > > ----- Original Message ----- > > From: "DL Neil" <[EMAIL PROTECTED]> > > To: "Beauford.2003" <[EMAIL PROTECTED]>; <> > > Sent: Monday, December 02, 2002 4:32 AM > > Subject: Re: Table setup question > > > > > > > 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 > > > > > > > > > > > > > > > --------------------------------------------------------------------- > 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
