[RESOLVED] Please teach me how to normalize this

Dolemite50

Hello,

I have a site with songs and bands. On the site there are genres that they select from dropdown menus. They can choose a primary and secondary genre for band and song descriptions. I'm wondering how to optimize/normalize this situation. Each Band table has two columns for pri and sec genres so the word "Folk" or "Alternative" is likely listed several times throughout the table. The same goes for the song table.

Should I put all of the 35-50 genres in a SET? Should I create a seperate table with index numbers for genres and reference those instead? Or is creating a SET the same as the second method? What is the best way to optimize the setup?

Thanks for any advice. 🙂

Shrike

The common way would be to have songs and genres in separate tables, and then have a 3rd table which linked song to genre. That approach would allow a song to be linked to n genres without changing the table format.

Dolemite50

Hey thanks a lot,

I'm not sure if I follow you.
So if I have
songid=1234, song_title=Hey Baby, song_description=Love Song in table1,
id1 =rap, id2=jazz, id3=ska in table2
and the track has jazz as primary genre and ska as secondary,..
songid=1234, pri=id2, sec=id3 in table3?

Is that how you would recommend laying it out?

And if I am looping through several songs, wouldn't it be a little rugged to be having to query so many tables?
while...
Select song_title from table1 Where songid=$songid
Select pri, sec from table3 Where songid=$songid
Select id3, id2 FROM table2,..

I think I'm still a little green.

Shrike

I think that seems about right, although it would only require one query to obtain the songs:

// Table songs
id
title
other_stuff

// Table genres
id
genre
other_stuff

// Table songs_to_genres
id_song
id_genre

// SQL - select all songs based on genre ($genre1 and $genre2)
SELECT songs.id, songs.title, genres.genre
FROM songs, genres, songs_to_genres
WHERE songs.id = songs_to_genres.id_song
AND genres.id = songs_to_genres.id_genre
AND (
 genres.genre = '$genre'
 OR genres.gengre = '$genre2'
)

A quick explanation: the query selects song id, song title and song genre. The WHERE clause limits the result set to those rows in songs which have a matching song ID in songs_to_genres AND those genres which have a matching genre ID in songs_to_genres. The last part of the WHERE does some additional pruning by limiting the results to those rows in genre which have a matching genre title to either $genre1 or $genre2 (which would normally be supplied by client).

This is a standard way of acheiving a many-to-many relationship in a relational database.

Dolemite50

Wow,

I thank you very much. All of a sudden I feel like I'm on day one again, I don't recognize your query at all. I really appreciate the help but if you can get me to understand this one I think I can take it from there. Thanks again.

So this:
SELECT songs.id, songs.title, genres.genre
FROM songs, genres, songs_to_genres
is telling it to select from all three tables? I don't understand. Wouldn't it want to give a non-valid mysql source for trying to get songs.title from genres?

WHERE songs.id = songs_to_genres.id_song
AND genres.id = songs_to_genres.id_genre
AND (
genres.genre = '$genre'
OR genres.gengre = '$genre2'
)
is the parenthesis used here to not have to match each side of the AND? So that you wouldn't have to say:
Where this=1 AND that=1 and other=1 OR this=1 AND that=1 and other=2?

This might help me understand better; how about if you wanted to sort by songid and echo the song titles and genres for each?

I'm really sorry, it's humbling when you see something so alien after being at it for awhile. You're already above the call of duty so I won't be let down if you bail.

EDIT! Sorry, I just realized that you did leave an explanation,..thanks I'll check it out.

Shrike

the FROM clause basically tells the database which tables you are working with. I have specified the table name in the SELECT so there will be no ambiguity.

Parentheses are used just in the same way as PHP.

It's a slow day at work today so I'll help all I can, although right now I am off to the pub for lunch 😉

Dolemite50

Thats the best news I've heard all night!

Well there's another new one for me. I didn't realize that you could say table.column in a query. I feel like some freshly thawed neandrathal here. So could this be used to combine queries in general?
I have several instances where I say
Select title, desc, size FROM song WHERE songid='$songid'
Select score, date, lyrics FROM info WHERE songid='$songid'
Select review FROM reviews WHERE songid='$songid'
So could that be turned into:
Select song.title, song.desc, song.size, info.score, info.date, info.lyrics, reviews.review FROM song, info, reviews WHERE songid='$songid' ???
(Each table has songid as a primary index)

I tried like crazy to do something like that with joins and union but to no avail. Does that look like it would work?

I'm going to go ahead and mark this resolved but that doesn't mean I'll be putting the chips and beer away. 🙂

Shrike

You will be able to run one query to get the data, but you will need to state the relationship between the primary and foreign keys for each table

Select song.title, song.desc, song.size, info.score, info.date, info.lyrics, reviews.review
FROM song, info, reviews
WHERE song.songid='$songid'
AND song.songid = info.songid
AND song.songid = reviews.songid

Dolemite50

Shrike,

Thanks so much, I'm going to go try out all this great new info. I've got an ocean of work to do but it'll be great to have it purring when it's done.

Can you direct me to any good resources to learn more about this stuff? I know I can Google for normalization but am wondering if you have a secret stash in your faves.

BTW, I'm going to be applying this to a site that currently has about 10k members so I have to be really careful Beyond backing up the DB often, can you share any tips for safely overhauling a DB? I'm thinking of leaving all of the current DB's intact for awhile and seperating / copying the current tables into the new stripped down version. Maybe have the script update both versions for awhile?

I really appreciate all of the help, hat's off sir.

Dolemite50

Just a quick question in case it's a slow day at work. I'm wondering how far to break these down. I made a table that just has songid, bandid, and song_title. Another table has description, lyrics etc. The second table contains info only found when on the song page itself so I have it seperate.

Another column has the submission date. (sdate) I am wondering where it should go. There are several spots throughout the site where I would select songid, songtitle FROM table WHERE bandid=$bandid ORDER BY sdate DESC.

My question is, how much more server effort is it to have sdate in the songid,bandid,stitle table when most queries won't refer to sdate but several will. Am I better off creating yet another table for songdates or is it not too much of a problem to have it sort through a table that has a DATETIME field not being referenced in the query? If I just say SELECT songid, stitle FROM song where bandid=$bandid will it be noticably slower to have the sdate field in the same table?

Thanks again.

Weedpacket

No; DBMSs are designed so that fields you're not using in a query don't impact on the time it takes to run the query. If a song can have no more than one sdate then it might as well be in the same table as the song itself. (Conversely, if you do put the sdate in a separate table, and you do want to select it along with other fields from the song table, then that will have an effect on performance because of the extra joining involved.)

Dolemite50

Hey hey! It's GD Money. 🙂

Thanks as usual WP. OK, so I'm getting it broke down but am confused about the need for a seperate table for the song genres.

I have a table called "genres" that has each genre listed such as:
table genres
genid genre
1 Acoustic
2 Ambient
3 Alternative

and also a seperate table that has only the genres for each song:
table songgenre
songid gen1 gen2
1234 3 2
322 1 3

and additional tables that have info for the song such as:
table song
songid song_title bandid
1234 Yah Baby 23
322 Doo Wop 17

What I don't understand is why I wouldn't have the genids listed in table song. I don't see how having them in a seperate table would be reducing redundant info because either way it will be in a row with the same numbers. It seems like adding redundant info because of the additional songid being stored. I must not be doing it right or something.

How would this:

//////////////////
table songgenre
songid gen1 gen2
1234 3 2
322 1 3

table song
songid song_title bandid
1234 Yah Baby 23
322 Doo Wop 17
///////////////////////

be better than this:

//////////////////////
table song
songid song_title bandid gen1 gen2
1234 Yah Baby 23 3 2
322 Doo Wop 17 1 3
///////////////////////

The latter method is already a lot better than what I am currently doing since it is only storing tinyint instead of 20varchar, but I want to make sure I'm doing it the best way possible before I commit 100%. I'm happy to stick with Shrikes excellent advice and I'm certainly not doubting him but the way I'm doing it seems like overkill. Can anybody please help me see the light?

also,..I got everything laid out and setup my old query method first before I suped it up with my newly learned tricks. I think I can apply what Shrike has taught me to this query with the exception of the trickery in getting the genres from the genres table.

Old method with new tables:

$getInfo = "SELECT stitle, bandid FROM song WHERE songid = '$songid'";
$rs = mysql_query($getInfo, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($rs);
$stitle = prepOut($row['stitle']);
$bandid = $row['bandid'];

$getInfo = "SELECT gen1, gen2 FROM songgenre WHERE songid = '$songid'";
$rs = mysql_query($getInfo, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($rs);					
$gen1 = $row['gen1'];
$gen2 = $row['gen2'];

$getInfo = "SELECT genre FROM genres WHERE genid = '$gen1' OR genid = '$gen2'";
$rs = mysql_query($getInfo, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($rs);					
$gen1 = $row['gen1'];
$gen2 = $row['gen2'];

$getInfo = "SELECT mp3, sdescr, lyrics, dlopt, vimage FROM songinfo WHERE songid = '$songid'";
$rs = mysql_query($getInfo, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($rs);					
$sdescr = prepOut($row['sdescr']);					
$mp3 = prepOut($row['mp3']);
$vimage = $row['vimage'];
$lyrics = prepOut($row['lyrics']);
$dlopt = $row['dlopt'];

NEW METHOD ATTEMPT:

$getInfo = "SELECT song.stitle, song.bandid songgenre.gen1, songgenre.gen2, genre.genres songinfo.mp3, songinfo.sdescr, songinfo.lyrics, songinfo.dlopt, songinfo.vimage FROM song, songgenre, genres, songinfo WHERE song.songid = '$songid'" 
AND songgenre.songid = song.songid 
AND ( 
genres.genre = songgenre.gen1
OR genres.genre = songgenre.gen2
) 
AND songinfo.songid=song.songid";

It's looking in the ballpark but the seperate songgenre table is throwing me off a bit. Any thoughts?

Weedpacket

and also a seperate table that has only the genres for each song:
table songgenre
songid gen1 gen2
1234 3 2
322 1 3

This ought to be

songid | genreid
-------+--------
1234   | 3
1234   | 2
322    | 1
322    | 3

So that one song can has as many or as few genres as you like (not not limited to two).

Of course if the genre table is just

genid | genre
------+------------
1     | Acoustic
2     | Ambient
3     | Alternaitve

then there's no real reason wrt normalisation to have a separate table for it, but there are two advantages.

The first is that it provides a hook for if you want to add additional information about genres. It would go in this table.

The second is that I misspelled "Alternative". If you use the term itself instead of an ID, then you'll have to make sure that you use "Alternative" every single time you mean Alternative - not "Alternaitve", "alternative" or anything else. And if you do get it wrong in the genres table, it's trivial to correct it there without it impacting on anything in any other table (since they're all using the ID).

Dolemite50

Thanks again. 🙂

Here's are some considerations for my site:

Each song will have a choice of Primary and Secondary genre. The first better defines the song. The second is optional so I don't think the table you posted would work since it doesn't differenciate the two.

There will never be a genre typed in. Genre selections are from dropdowns and links only. It's definitely a good pointer to keep in mind though.

I like your idea of keeping the genre table for adding more genre-related items to it. What I don't understand is why you said it may not be necessary unless that's what I had in mind. Without using it wouldn't each genre title be listed over and over again? If there are 10,000 Native American tracks on the site then the word Native American is listed in the DB 10,000 times. I assumed that the reasoning behind the genre table was so that the DB could store the number 12 instead of Native American so many times.

So considering that each song will always have two genres and likely won't change; am I better off adding gen1 and gen2 to the song table and storing the genre as a tinyint which references the genres table, and then getting rid of the songgenre table?

Thanks again, sorry to be such a pain but this is going to be a gigantic overhaul so I want to make sure I know what I'm doing.

Dolemite50

Well the more I think about it the more confused I get. When you mentioned that mysql just ignores the columns not in the query; you changed my entire game plan. My song table has about 20 columns with two TEXT fields for lyrics and description so the table itself is pretty big. I was under the impression that in situations where only songid, songtitle, and bandid were needed for a loop that it would be sleeker to have it sort through a small table only containing those three items instead of searching through the monster table.

So what you're saying is that a query for SELECT song_title where songid='$songid' would be just as fast on a table with only those two columns as it would with a 100 column table loaded with info? If that's the case then I might as well leave everything as-is except the genre situation. There isn't any redundant info in there, there's just a lot of info.

I think Don Henley was studying PHP when he said "The more I know, the less I understand".

Weedpacket

Dolemite50 wrote:
Without using it wouldn't each genre title be listed over and over again?

Yes; but if it's unique that's all that the normalization process would care about.

Dolemite50 wrote:
...a small table only containing those three items instead of searching through the monster table.

The number of fields isn't a consideration (unlike the number of rows). Presumably you'd build an index on the fields you'd search on. Those indexes are (in a sense) your "sleeker" tables in that they contain only information from the fields they're indexing. They're stored separately from the tables themselves. So any other information that might be contained in each row doesn't matter for the search.

(In reality indexes are not tables but structures designed for efficient searching: they work out where the rows being searched for are to be found so that the DBMS can go to those rows it needs to. The point is that they contain only the information required to carry out the search.)

Dolemite50

Thanks again WP. I think I have a better understanding now. I currently have the auto_inc songid set as the index so I assume that as long as I'm searching by "where songid=songid" then all should be smooth.

A big thanks to you both.

I now have a slight GD problem. I'm tempted to ask it right here since GD Money is in the hiznouse but I'll start a new one just in case. 😃

Shrike

I can't really add much to what WP has already said. To reiterate a little, you should be aiming to store data in your database in such a way as to provide the most convenient access to the data. It might seem like overkill to have your song genres separate to the songs themselves, but it allows more flexibility in the long run. Thinking logically songs and genres are separate entities and so should be separate in the database.

As an alternative scenario to a typo in the genres - imagine you decide that you want to content manage the genres from within an admin area (or perhaps generate the drop down lists dynamically). With your original database schema you would have to load all rows from the songs table, go through the list and remove any duplicate entries (or use a DISTINCT query), and then build the genre list. That could be a serious unnecessary overhead if you had 10 thousand songs and 5 genres. With the genres in a separate table you would just load the 5 rows and build your list.

Dolemite50

Shrike, you've come back? Are you some sort of masochist??? 🙂

This has been quite the valuable thread for me, thanks guys.

One thing that I'm still wondering on is when I would use a SET. If the genres are predetermined then would that be a good time to use it?

Dolemite50

Hey Shrike,

I just wanted to let you know that I attempted the combining query method that you taught me and it works like a champ! Man, I can't believe I've done without that for so long. I think the main thing scaring me off from normaization was that I thought I would have to do all the queries using my caveman method. I could have easily been scared off by something so greek to me were it not for you and WP taking the time to spell it out for me.

Thanks again,..now it's time to go slap up a ton of my old code. HiiiYAAA!