Hi,
I am getting ready to take a stab at normalizing my DB and am wondering how far I should be breaking down the tables. I received some great advice here on the board, and I now have a better understanding of the how and why, but I don't know the how much. If anybody could please give me some final advice I would really appreciate it.
As an example, this is my songs table (don't laugh):
songid - MEDIUMINT 7
bandid - MEDIUMINT 7
song_title VARCHAR 50
primary_genre - VARCHAR 20
secondary_genre - VARCHAR 20
mp3 - VARCHAR 20
sdate - DATETIME
description - TEXT
lyrics: - TEXT
score - MEDIUMINT 7
weekly_score - SMALLINT 5
monthly_score - SMALLINT 5
downloads - MEDIUMINT 7
weekly_downloads - SMALLINT 5
monthly_downloads - SMALLINT 5
exclusive_track - TINYINT 1
target_track - TINYINT 1
mature_content - TINYINT 1
download_option - TINYINT 1
sort_order - SMALL INT 3
daily_winner - TINYINT 1
weekly_winner - TINYINT 1
monthly - winner - TINYINT 1
genre_winner - TINYINT 1
As advised I will be putting the available genres in their own table such as
TABLE GENRES
genre_id . gerne
. 1 . . . Acoustic
. 2 . . . Alternative
Most of the table's items will only be queried when on the song page itself (lyics, description, etc) , so I figured it would be safe to put those items in their own table.
TABLE SONGINFO
songid
description
mp3
lyrics
download_option
mature_content
There are also total, weekly, and monthly stats for each scores, plays and downloads that are called on the song page but also queried by and inserted seperately. I assumed I should have them seperated to:
TABLE SONGSCORES
songid
score
weekly_score
monthly_score
TABLE SONGPLAYS
songid
plays
weekly_plays
monthly_plays
TABLE SONGDOWNLOADS
songid
downloads
weekly_downloads
monthly_downloads
And the bottom fields are for awards so:
TABLE SONGAWARDS
songid
daily_winner
weekly_winner
monthly_winner
genre_winner
And now there are some leftover items and I'm not sure how to break them up. For all I know I may have broken up too much already but here's whats left.
songid
bandid
song_title
primary_genre
secondary_genre
exclusive_track
song_date
sort_order
target_track
Technically I believe that each of the remaining items (genres grouped) could be broken into their own tables because I query on each seperately. I also query by awards, plays and downloads so I'm having an EXTREMELY hard time trying to figure out how I should do this. Here are some examples:
SELECT songid, song_title FROM songs:
WHERE primary_genre = '$genre' or secondary_genre = '$genre' and exclusive_track = 1
WHERE bandid='$bandid' ORDER BY sort_order ASC
ORDER BY monthly_downloads DESC
WHERE primary_genre = '$genre' ORDER BY song_date ASC
If I put all of the remaining items in a table it would look like this:
songid|bandid|sng_title|pri_gen|sec_gen|exc_trk|sng_date|srt_ord|targ_trk
1234 332 Fame 4 6 1 2005:02:02 5 1
So I guess what I need to know is what would be an at par tradeoff between seperated tables and demanding queries. I don't want to have to query 6 tables for results but I do want them to be broke down. I need to know general things like: would the tradeoff between having a tinyint field of 1 in a table be worth not having to query an extra table? I query by genre and sort by sdate often.
Any advice is very much appreciated. I'm sorry to go into so much detail but it's going to be a huge job and I want to try and do it right.
Thanks again. 🙂
