Thanks for the tips! I'll definitely try that out. I've already finished the arduous task of entering all of the playlist data, I have all of the data in a readily sortable form, so trying out different stuff doesn't involve much more than creating tables and writing my queries.
Let me throw this idea out and see what you think. I was at work all day trying to think of better ways to do all this... What if I have one 'master' table, containing the following: master_id track_id album_id artist_id The first column is the index for this table. The next 3 columns contain complete track/album/artist links, i.e. the id's of each from their respective tables. So now instead of my 3 'linking' tables, I have one 'master' table. By querying the master_id, I get a complete track/album/artist return. Of course, I could vary the query statement to get only tracks/albums, albums/artists, and artists/tracks. The ultimate goal here is for me to be able to assemble complete playlists for different shows I've DJed. Using the above setup, I could have one master_playlist table, contains 1 column, the date of every show. Everytime I do a new show (or create old ones) I simply create a new 'playlist' table named after the date it aired. This table would contain an auto_increment column to show the order and number of songs played, and it would also contain a column referencing the master.master_id column. When I queried the playlist table for a certain date a show aired, or a certain period of time even, it would then reference the correct playlist tables, which then reference back to the master table. Are there any inherent flaws with this design that you guys can point out for me? Or is this actually a little more efficient than the current way? Jason Soza ----- Original Message ----- From: "Opus" <[EMAIL PROTECTED]> Date: Thursday, May 23, 2002 4:50 pm Subject: RE: Query Problem > 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! --------------------------------------------------------------------- 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
