When you are designing a database and you are thinking about creating a
comma delimted list, this is a good sign that you need to rethink your
design. Bitfields are a good option, however if you ever need to add
elements to the bitfield (ie bitfield A can signify the presence of 4
elements, but now you want 5) then bitfields isnt the way to go.
This is what I would suggest, because it allows for some very flexible
querying:
Artists table:
- artistid (auto increment field)
- name
- birthday
- whatever...
Album table:
- album id (auto increment)
- artistid (index to the artist table that contains the artist of the
album)
- number of tracks
- total length
- record label
- ect...
Track table:
- trackid (auto increment)
- artistid
- albumid
- title
- length, ect...
Playlist table:
- trackid (auto increment)
- DJ who played it
- time of play
- ect..
By setting up a table for each of these different things, not only would
your database be normalized (ie the length of a song would never be included
twice), but it allows for some interesting querying.. For instance you can
select all of the tracks that where played during the month of april from
arist X... or all of the songs from album Y. This is accomplished through
joining the tables together on the trackid/artistid/albumid.
Hope this gets the juice flowing.
ryan
> Hello all!
>
> I have a question for all of you... I would very much appreciate your
> input.
>
> I'm building a database for a radio station. The database must allow
> the DJ to enter what they play and when, and allow the program director
> to create weekly reports for the record labels.
>
> I'm wrestling with how to design the database. I have already
> determined, via beginnings of normalization, that I need separate tables
> for Albums, DJs, Genres, and so forth... The problem I'm having is how
> to store the track data. I have two ideas:
>
> First, to maintain a single table with every bit of track data there is
> (ie, title, artist, length, etc) and store this data into the albums
> table via either a bitfield (ie, binary additions, etc) or via a comma
> (or other) delimited list... ie "32897,39823,1234,29844" etc.
>
> The problem with this is that there are probably nearly 250,000 tracks
> among all of our assets...
>
> The other thought would be to have an album table that would contain
> things like the label, artist, number of tracks, etc, and another table
> that would contain the track data for that album... ie "1238_tracks".
>
> I'm rather new to all of this, so I don't know the relative
> benefits/detriments of these two options... can you give some advice?
>
> Thanks,
> Ben
>
>
> ---------------------------------------------------------------------
> 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
