Which of these storage/retrieval methods is better

Dolemite50

Hi,

Just a quick newbie logic question here;

I have a mysql db which holds info for bands; one table for band information and one that holds info for each band's songs. The band_table also has a column called bandpics that stores a path to the band's picture.

I have a page that dynamically shows a list of songs for various bands and pulls the info entirely out of the song_table such as:
Song title: $song_title
Band Name: $band_name
Genre: $genre
<br>..etc

I am setting up the GD Library and now would also like to include a thumb of the band's image on the song lists. Before I begin, I realize that I have a few options. Should I,..

a. Create a column in songs called bandpics and transfer all of the paths over to it before I begin?
or
b. Leave the paths in the band_table and call upon them during the loop?

If I go with option A then I will also have to set it to retreive the image path for every new song that they upload so that it can be inserted with every submission. Song table already has a ton of columns and I hate to add another. I guess that would make an option c:

c: Create a two-column table that only contains image_path and bandid. I would still have to access it during a loop but it wouldn't have to sort through as much info.

I'm hoping to get some advice here. Is it worse to have a redundant information in the DB or to force the server to do added functions within a loop?

Thanks for any help.

LoganK

Redundant data = bad.

Normalization = good!

Dolemite50

Thanks Logan,

I did a search for normalization and found a good article that laser posted a link to. From what I can tell they are seperating data so that in a case like mine,..if an artist has 10 songs uploaded, then there is no need to store their band name within the table ten times over. I suppose I would create one table that only has band name and bandid? Or maybe band name, band Picture, and band id?

So hypothetically, if I were to setup tables in this way,..and somebody requested a random list of songs,..I would have to pull the band name and assorted info from additional tables while looping? I could see where that may be less demanding on a server if it has less stuff to sort through to find the results,..is that the idea? It just seems so clean easy to pull the data from one table in a single query. Got any advice?

Thanks again. 🙂

LoganK

Normalization is great. It does come at a side effect of having more work for the DB, you're right about that. But is more efficient because you're not storing redudant data (and taking up extra space), nor does the DB have to take time to process all those other columns in a table. The data should definitely been in two or more tables, like you said. And for a random song list, it won't be any harder on the DB - use joins to get all the data from the DB, and do the randomization and song picking in PHP itself, thus saving the DB some valuable CPU cycles.

Dolemite50

Thanks again, I really appreciate it.

Well as soon as I free up a few days I need to devote it to narmalizing my DB's. In the meantime, I already have the pic path stored in the band Table. (I'm not being lazy or looking for shortcuts, I just want to make sure I have the time set aside to do it right) So if I could ask just one more thing from you that'd be great.

What would be the best method to use for my current setup?
If I am pulling out a list of 20 songs from the song table, and each song has a bandid which corresponds to the band_table;and I need to grap the pic path from the band_table, what's the smoothest?
To date I've been using a standard:
SELECT songid, bandid FROM song_table..
WHILE $row=..
songid=$row['songid'];
bandid =$row[bandid ];
//and then begin a select statement within the loop
SELECT band_pic FROM band_table where bandid='$bandid...
$bandpic=...

echo"$bandid, $songid, $band_pic.."
}
I've never used JOIN, is this job better suited for a JOIN statement?

LoganK

It might be better suited for a join, however, I'm not all that experienced with joins (you should know seeing as you saw my post), so, treat advice from me about joins with caution. I'm not sure the exact syntax (and I could swear almmost 99.9% this won't work), but this is the basic idea:

SELECT songid, bandid, pic_path FROM song_table AS songs, band_table AS bands LEFT JOIN songs.bandid = bands.bandid

This assumes that the primary key in band_table is "bandid", and the foreign key in song_table is "bandid".

Dolemite50

Thanks alot Logan, I'm sure that got me in the ballpark. I'll go do some boning up, wish me luck.

tekky

Dolemite50 wrote:
Thanks alot Logan, I'm sure that got me in the ballpark. I'll go do some boning up, wish me luck.

Just to echo on the normalization

Its what I would recommend too... i actually created a table for something similiar to this... but now that I think about it Its lost I believe 🙁

if I recall i did band info in 1 table... (it was a DB for tracking CD's I owned + track info)

band info like name, size (still together etc) went in 1 table
album info went in another table, this comprised of ablum release date, link to band ID, title, label company etc (things that change/can change with each album)

then I had a song table... this had a link to artist, album, original album (incases of greatest hits albums) track length, genre (since many artists often mix genres these days) and original release year

then I had a track table, this was the guts of the whole thing... and was the simplest table, album ID, song ID, track #

I know this may seem irrelevant to your post... but maybe it will give you an idea of how things can be done for your database 😉

bretticus

Just wanna throw in my two cents...

Normalization doesn't only save you harddrive space, it also makes it easier to later go back and extend your application. I find the key is to build an entity that can stand on it's own. The principle here is the same as extending a class in PHP. In other words (this may not be the best example but here goes.) Say you have a database with a persons table. A person is just a person right? Really, a username attribute is not a proper attribute to describe a person for every conceivable scenario. However, a lastname attribute usually is. The point here is that you can add a users table that uses a foreign key to point to a particular person id. Now your application(s) can use this table and not have to worry about a username attribute that would have no purpose in that application. You will find that this keeps your table structures a little less confusing when you start making your application(s) feature rich.

THERE! {wheeeww!}

LoganK

Looks like you've got some pretty good information here Dolemite!

For more, you might want to check out Wikipedia on database normalization or joins, or do a search on Google for database normalization (there are a ton of great resources out on the web).