Re: Table setup question
On Thu, 2002-12-05 at 14:42, Beauford.2003 wrote: Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Try Select name from album join reference join songs where album.ida=reference.ida and reference.ids=songs.ids and songs.title='sometitle'; John Coder - 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
Re: Table setup question
Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, 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 AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 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: SongID === == 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
RE: Table setup question
Let's assume you want the entire albums list which the song Day and Night by Billie Holiday is in. SELECT t1.title,t1.artist,t3.name FROM sings t1 INNER JOIN reference t2 ON t1.ids=t2.ids INNER JOIN album t3 ON t2.ida=t3.ida WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' (or WHERE t1.ids=1 if you know the id of the song) Relationship is like: [songs] [album] \\// [reference] (this later one contains the list of songs of each album) This way you treat songs and album separately and link them through another table so you don't have any limit on the number of albums a song can belong to. If you know the id of a song all you have to do is Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 3:42 PM To: Adolfo Bello Cc: [EMAIL PROTECTED] Subject: Re: Table setup question Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the mysql song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the mysql album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, 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 AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3
Re: Table setup question
Thanks for the help, but I just can't grasp the concept (I'm not even sure I have my table layouts the way they should be to have this work). I'll have to look into this further, but for the time being the way I have it is working. Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 4:28 PM Subject: RE: Table setup question Let's assume you want the entire albums list which the song Day and Night by Billie Holiday is in. SELECT t1.title,t1.artist,t3.name FROM sings t1 INNER JOIN reference t2 ON t1.ids=t2.ids INNER JOIN album t3 ON t2.ida=t3.ida WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' (or WHERE t1.ids=1 if you know the id of the song) Relationship is like: [songs] [album] \\// [reference] (this later one contains the list of songs of each album) This way you treat songs and album separately and link them through another table so you don't have any limit on the number of albums a song can belong to. If you know the id of a song all you have to do is Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 3:42 PM To: Adolfo Bello Cc: [EMAIL PROTECTED] Subject: Re: Table setup question Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the mysql song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the mysql album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, 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
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 IDAIDS 11 21 31 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,31 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 AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; Sent: Monday, December 02
Re: Table setup question
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 IDAIDS 11 21 31 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,31 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 AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1
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 AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 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: SongID === == 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
RE: Table setup question
Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, 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 AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 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: SongID === == 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
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 IDAIDS 11 21 31 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,31 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 AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - 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
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 IDAIDS 11 21 31 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,31 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 AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - 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
RE: Table setup question
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 IDAIDS 11 21 31 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,31 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 AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - 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
Table setup question
Hi, 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 AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 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: SongID === == 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
A table setup question.
Subject: A table setup question. From: al butler [EMAIL PROTECTED] === I have the MySQL book and I am trying to come to terms on how to setup a column that is based on another one. Let me try and explain. Each row has a string (unique) and indexed. This string is catergorized by anothe column defined as a SET , let's call it 1,2,3,4,5,6. From my readings this seems to be effiecient than having it classified as a VARCHAR and is also readable. It's treated as an int and can be indexed faster and more effecient. But for item 6 in the SET, you can have up to 20+ categories. This sub-category I have defined as another SET and it's named something like sub6. Now the SET can have other subcategories, so right now I would add another column for each SET that needs a sub-category. Kind of ugly. Let's recap: col1 string (VARCHAR) 64 col2 category (SET) 1,2,3,4,5,6 col3 subcategory of col2 6)SET A,B,C,D,E,F,G,H A set can have 64 items. I don't forsee col2 to ever reach this. I can forsee col3 possibly having 64 sub-categories of col2. My question. Is this the best I can do for a sub-category? Any suggestions? Regards Al - 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
RE: A table setup question.
* al butler [EMAIL PROTECTED] I have the MySQL book and I am trying to come to terms on how to setup a column that is based on another one. Let me try and explain. Each row has a string (unique) and indexed. This string is catergorized by anothe column defined as a SET , let's call it 1,2,3,4,5,6. From my readings this seems to be effiecient than having it classified as a VARCHAR and is also readable. It's treated as an int and can be indexed faster and more effecient. Correct. But for item 6 in the SET, you can have up to 20+ categories. This sub-category I have defined as another SET and it's named something like sub6. Now the SET can have other subcategories, so right now I would add another column for each SET that needs a sub-category. Kind of ugly. Yes... :) Let's recap: col1 string (VARCHAR) 64 col2 category (SET) 1,2,3,4,5,6 col3 subcategory of col2 6)SET A,B,C,D,E,F,G,H A set can have 64 items. I don't forsee col2 to ever reach this. I can forsee col3 possibly having 64 sub-categories of col2. My question. Is this the best I can do for a sub-category? Any suggestions? One word: normalize Add an INT primary key, (keep the UNIQUE constraint on col1) and add a second table for col3 and potential future sub categories. This table should also have an INT primary key. Then add a third table to connect the two, with two INT fields, and a compound primary key, containing both INT fields. Also, add an index for the second field of the primary key[1]. These two fields are foreign keys, and 'points to' the keys of your main data table and the category table. This way, you will keep up the speed of your queries, but, just as important, you will have a clean design wich scales well, without the need for repeatedly doing changes to the db schema. [1] If the primary key is (data_id,category_id), it will be very slow to do a search for all occurrences of data items for one category without an extra index on category_id, and if it is (category_id,data_id) it will be slow to list all categories of an data item without an index on data_id. If you don't need one or the other, you can drop this index, just make sure you put the right id first in the primary key. -- Roger - 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
