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
