Jason, First, as to the structure of the database. I agree with Sammy, you probably don't need 3 tables linking data together. A more generic structure would have only 2 relationship tables connecting as such: albums <=> tracks <=> artists. In this fashion, you can determine who the artist is for each album by looking at who the artist is for the tracks on the album. This also allows for albums that are compilations or ones that you burn yourself. There is one more level of abstraction that could be done, but that is a different topic.
The structure looks good other wise. You might want to look at indexing both those columns as well. That will help some speed issues at some point. Second, it is poor practice to rely on/reference internal IDs with an external source. Since the IDs are AUTOINCREMENT you never do know the exact number it will get. Yes, you can try to control this via sorting your data before you load it, but if you change the data before you load it, your IDs will all change. Think of these numbers as data that only your database need know. Other than using them to tie tables together, you should ignore them. I.E. If you have the artist list '3 Doors Down, etc.' and you add '10,000 Maniacs' to the begining of your data file to load, then '3 Doors Down' goes from ID 1 to ID 2. This will mangle all of your data sets for any reference artist, and possibly others. This leaves the question of how to do this. Loading the data tables (albums, tracks, artists) is straight forward. You have that. Describing the relationships is a bit different. My suggestion would be to write some code someplace that takes a text file of the relationships and inserts the data into the relationship table based on the data in the database. I.E. Build files that look like: Artist Track 3 Doors Down Superman and Album Track The code will look up the Artist, and the Track and insert the IDs for both into Artists_Tracks. Honestly, with a bit more work, you really don't need to load the data tables directly, but indirectly as a result of loading the relationships. If you get a Null value while searching for an artist, then you need to add the artist, and then get the ID again. Now you can populate a database of 5 tables with 2 files. Good luck! On 22 May 2002 at 21:27, Jason Soza wrote: > I apologize for not including that in my original post. Here's my create > table statement: > For albums, artists, tracks: > CREATE TABLE artists (artists VARCHAR(50), id MEDIUMINT NOT NULL > AUTO_INCREMENT PRIMARY KEY) > > For albums_artists, artists_tracks, tracks_albums: > CREATE TABLE albums_artists (album_id MEDIUMINT NOT NULL, artist_id > MEDIUMINT NOT NULL) > > Insert statement: > LOAD DATA LOCAL INFILE "albumsartists.txt" INTO TABLE albums_artists > > For tracks, albums, and songs, their .txt files are simply a one column > list. The other column is auto_increment. So a sample from tracks: > 11am > 1979 > 3 Libras > 32 Flavors > 4 Degrees > 46 & 2 > 99 Red Balloons > A Place For My Head > A Place In The Sun > Abracadabra > > >From albums: > 311 > 14:59 > 8 > (unknown) <---generic, unknown album. really a record. > 13 Ways To Bleed Onstage > 24/7 > 40oz To Freedom > A Boy Named Goo > A Few Small Repairs > A Little South Of Sanity > > >From artists: > 311 > 10,000 Maniacs > 3 Doors Down > 311 > A Perfect Circle > AC/DC > Adema > Aerosmith > Afghan Whigs > AK1200 > > For albumsartists.txt, tracksalbums.txt, and artiststracks.txt, they're a > two-column, tab delimited text file. Examples from albumsartists.txt: > 1 1 > 2 258 > 3 35 > 4 14 > 4 76 > 4 167 > 4 230 > 5 53 > 6 223 > 7 256 > > >From tracksalbums.txt: > 1 237 > 2 221 > 3 222 > 4 41 > 5 354 > 6 16 > 7 315 > 8 176 > 9 12 > 10 2 > > >From artiststracks.txt: > 1 15 > 1 19 > 1 250 > 2 40 > 3 38 > 3 138 > 3 281 > 3 303 > 3 363 > 5 3 > 5 62 > > I thought 2 links would be enough as well, but I tried this query using just > the tracks_albums and albums_artists table and couldn't get it working. For > example, using this: > SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, > artists, tracks_albums, albums_artists > WHERE tracks.id = tracks_albums.track_id > AND albums.id = tracks_albums.album_id > AND albums.id = albums_artists.album_id > AND artists.id = albums_artists.artist_id > > I get 718 returns instead of 614. If more than one artist is linked to one > album, only the song from the first artist shows in the tracks column and it > shows for all artists (no other songs by the different artists appear). Am I > just missing something in this query? > > Thanks for your help, > Jason > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sammy Lau > Sent: Wednesday, May 22, 2002 8:18 PM > To: Jason Soza > Cc: [EMAIL PROTECTED] > Subject: Re: Query Problem > > > to make it easier for others to help you. you should have posted the > create table statement, insert statement for a small set of data so that > anyone of us could easier reproduce your problem. > > btw, are you sure you need 3 links between those 3 tables. please > correct me if i'm wrong. i think 2 links should be enough. ie. artist > <-> album <-> tracks. > > > Jason Soza wrote: > > > > I'm trying to compile 75 playlists of 25+ songs each into a database > > sortable by date the show was produced, track, album, and artist. I got > > advice from someone as to how to layout the table structure, and basically > > it was said that I should have a basic structure of 6 tables, one for > > artists, one for albums, and one for tracks. These tables should contain > > whatever info I want, i.e. song title, albums, etc., and a unique id for > > each. I should have 3 more tables, each linking the unique id's of each > > other table together. > > > > So, I have 3 tables as was suggested, one for songs, one for albums, one > for > > artists. Each has two columns, one being whatever information I'm > entering, > > and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column. > > > > I have 3 other tables containing two MEDIUMINT columns, these link each of > > the 3 others. I have: > > artists_tracks > > tracks_albums > > albums_artists > > > > Each is setup so that the records represent 'links' - for example, > > artists_tracks has artist_id and track_id columns. Each record is made up > of > > an artist_id and a matching track_id. > > > > My problem is, when querying some records are lost. But only on a certain > > query. For instance: > > SELECT tracks.tracks, artists.artists > > FROM tracks, artists, artists_tracks > > WHERE tracks.id = artists_tracks.track_id AND artists.id = > > artist_tracks.tracks_id > > > > This works. It returns 614 records, each track mated with it's relative > > artist. This same query works using albums and the tracks_albums table, > > returning 614 records as well. Matching up albums to artists returns 413 > > records, which is the correct number. > > > > However, the following query returns 451 instead of the expected 614 and I > > can find no distinguishable pattern as to the records dropped: > > SELECT tracks.tracks, albums.albums, artists.artists > > FROM tracks, albums, artists, tracks_albums, albums_artists, > artists_tracks > > WHERE tracks.id = tracks_albums.track_id AND albums.id = > > tracks_albums.album_id > > AND albums.id = albums_artists.album_id AND artists.id = > > albums_artists.artist_id > > AND artists.id = artists_tracks.artist_id AND tracks.id = > > artists_tracks.track_id > > > > I've tried LEFT JOIN as well, and have tried numerous other suggestions > out > > of the manual, but everything turns up 451 records. I haven't been able to > > track down WHY records are missing - it seems almost random. If I sort by > > album, sometimes all songs from an album have been listed, sometimes only > 3 > > of 5 or 7 of 10 are missing. > > > > Any help would be greatly appreciated. Thanks in advance. > > > > --Opus-- We never forbid where we do not also have the power to prevent. - Orson Scott Card (Xenocide) -------------------------------------------------------- Get added to my Humor list: mailto:[EMAIL PROTECTED]?subject=ADD_HUMOR Get added to my Neat list: mailto:[EMAIL PROTECTED]?subject=ADD_NEAT Get my PGP public key: mailto:[EMAIL PROTECTED]?subject=PSEND&body=send%20PublicKEY.asc Visit My Home Page: http://value.net/~opus/ --------------------------------------------------------------------- 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
