The other difference is that it works (BTW, a huge benefit) Adolfo
> -----Original Message----- > From: Beauford.2003 [mailto:[EMAIL PROTECTED]] > Sent: Monday, December 02, 2002 11:24 AM > To: DL Neil; [EMAIL PROTECTED] > Subject: Re: Table setup question > > > 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 > > > > --------------------------------------------------------------------- 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
