DL, Yep, I see what your saying. In my application it may work fine, but in larger applications where things may change, it would be easier to do it with the 3 tables.
Thanks ----- Original Message ----- From: "DL Neil" <[EMAIL PROTECTED]> To: "Beauford.2003" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, December 03, 2002 7:56 AM Subject: Re: Table setup question > Beauford, > > > OK, I get it now. I thought there would need to be duplication in the > > AlbumSonglist DB. > > =indeed, the process of 'normalisation' (as mentioned earlier) helps to > ascertain when and where 'duplication' is allowed/beneficial/should not > happen. > > 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. > > =try wording a query to get from album to song title, and then another query > starting from song title and working to album. That's always a good test of > two-way relationship situations. > > =if you have a pointer repeated then (usually) it will take up less storage > space than a repeated song title. OTOH getting rid of a table and the join > in each query will speed up processing time/throughput (by a v.small > amount)... > > =if some data ever changes (admittedly I can't see that it would in your > application - but you're the subject matter expert), eg someone remixes a > song, so to be more precise you want to modify "Leila" to have "Leila - > original" and "Leila - remix" as two different entries in the db; then how > easy would it be to go through the song title data and make that change? In > the m-m model the change would be needed only once (the Song Title tbl - all > else unaffected). In your model, you would need to be sure that you changed > every case - and without knowing how many changes is 'enough'! (now apply > this to customer records and people changing addresses and you'll see > why/which is the 'industry standard' approach). > > =however the ultimate answer to your question is: if it works for you/your > users, then it is 'right'! > > > Thanks again, Beauford > > =hope it helps you along your way to MySQL expertise, > =dn > > > > ----- 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
