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
