Other than it is uglier than a Hut?
The higest ideals of a database is to separate data into tables that keep track
of data for the same objects. Some databases break these rules (oh and how)
for simpler queries, and sometimes for faster queries.
It is actually better to break the relationship info into separate tables.
Makes it easier to track at some point.
Something to add to the Albums_Tracks table would be index. IE
Album_ID, Index, Track_ID
1, 1, 1
1, 2, 5
1, 3, 4
With this, you can keep trock of which order the tracks appear on albums.
As for the Playlists. This is simular to album data. IE, an ordered
collection of tracks with a title, and a date.
So, expanding Albums (possible rename to Collections) we have:
Collection_ID, Title, Date, Play_List
1, Oxygene, 1976 (use any date here, the year is normally all you get), N
2, Club Six Groove, 6/23/2002, Y
Note that you can put any date in for the 'album' and ignore everything other
than the year, or you can research that data.
Now, when you create a new album or playlist (collection of tarcks) you enter a
new row in the Collections table, and build the Albums_Tracks
(Collections_Tracks) table.
Advantages here. With a specific Track (say #50), we can find out what
collections it was used for in the following way:
SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT
WHERE C.Collection_ID = CT.Collection_ID
AND CT.Track_ID = 50;
You could also find when it was played (include the Date, and filter for
Play_List), when it appeared in the performance, etc.
To recreate the playlist, you would sort on the index.
If you create new tables for each playlist, then you can't do any data
crunching, or comparisons to see what songs you use most, or least, or paterns,
or what ever...
As a DJ, you might want to spend the extra time figuring out BPM for each
track, and adding that to the track data, then you can get the computer help
you look for good songs to tie together
You know, you could do all of this via a text file.... Of course a spread sheet
app would help you copy and paste, and keep things neat. Flat tables will rule
the WORLD!!! Oh wait, that was FrameWork will rule the world. NOT!
On 23 May 2002 at 17:28, Jason Soza wrote:
> 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!
--Opus--
The more absurd the belief, the more deeply it must be held,
the more aggressively it must be promoted and angrily defended
if the <person> is to see himself as right and sane.
- Unknown
--------------------------------------------------------
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
