mySQL beginner help!!

[Alex]

im just starting to mess around with mySQL and PHP and i wanna catalogue my entire music collection in a database and then have a webpage to browse through it, as a first test.

right now i'm still trying to figure out the theoretical stuff, as in planning my database:

i have a very large music collection (60+ GB, 12,000 tracks) so this has to be as efficient as possible!

here's my initial thoughts:

- 1 table "Albums" (Album, Album Artist, Tracks, Rating, Year, Genre, Comments, Review)
- 1 "sub-table" for each Album (Track#, Title, Artist, Rating, Genre, Comments, Review)

I'm not too sure how efficient this will be because i have around 800 Albums so that would mean 800 other tables.... and searching for an "Artist" for example (different than "Album Artist" btw.... in compilations "Album Artist" = "Various") it would take forever to search each individual album table wouldn't it?

now i think it might be better to make the main table a "Song Title" table, but again this leaves me with creating a seperate table to store each Album's data...

any suggestions?

thanks!!!

 

[TheShiz]

you want the least # of tables that makes sense, 800 tables is way too many, I think this would work, unless you want to be able to assign an album to multiple genres, if you do then you need a genre table and an AlbumGenre table, same thing for artists, if you want to be able to have multiple artists for the same album, then you need an AlbumArtist table. The same rules apply if you want multiple comments, or multiple reviews, etc.


for the basics, have 3 tables

album table
albumID, AlbumName, ArtistID, rating, year, genre, comments, review

albumtracks table
albumID, trackNumber, trackname

artist table
ArtistID, ArtistName


*********** if albums can have more than one genre get rid of genre in the album table and have these tables*************

Genre table
GenreID, genre name

AlbumGenre table
AlbumGenreID, AlbumID, GenreID


********** if albums can have more than one artist, you will have to get rid of the artist column in the Album table and have this table instead**************

AlbumArtist table
AlbumArtistID, AlbumID, ArtistID

 

[Alex]

Originally posted by: TheShiz
you want the least # of tables that makes sense, 800 tables is way too many, I think this would work, unless you want to be able to assign an album to multiple genres, if you do then you need a genre table and an AlbumGenre table, same thing for artists, if you want to be able to have multiple artists for the same album, then you need an AlbumArtist table. The same rules apply if you want multiple comments, or multiple reviews, etc.


for the basics, have 3 tables

album table
albumID, AlbumName, ArtistID, rating, year, genre, comments, review

albumtracks table
albumID, trackNumber, trackname

artist table
ArtistID, ArtistName


*********** if albums can have more than one genre get rid of genre in the album table and have these tables*************

Genre table
GenreID, genre name

AlbumGenre table
AlbumGenreID, AlbumID, GenreID


********** if albums can have more than one artist, you will have to get rid of the artist column in the Album table and have this table instead**************

AlbumArtist table
AlbumArtistID, AlbumID, ArtistID

awesome!!! :beer:

thanks a lot dude this makes so much more sense to me now!!

can't wait to get home and get started!!

 

[Alex]

ok so i did some tinkering with it, is this about right?
i added the AlbumGenre, Genre and AlbumArtist tables and made changes to the album table to tie things up...


6 tables

Album table
albumID, AlbumName, AlbumArtistID, rating, year, AlbumGenreID, comments, review

AlbumTracks table
albumID, trackNumber, trackname, GenreID

Artist table
ArtistID, ArtistName

AlbumArtist table
AlbumArtistID, AlbumID, ArtistID

Genre table
GenreID, GenreName

AlbumGenre table
AlbumGenreID, AlbumID, GenreID

thanks!

 

[TheShiz]

that looks good to me. the only way to be sure is to start making up some data and see if it works like you expect, but I think that is pretty close or maybe all you need to fill your requirements.

 

[Alex]

thanks dude i think i nailed it now!

when i get home tonight i'll play around with this and start testing stuff!

:beer:

 

[kamper]

That looks like a proper design but it's not how I'd necessarily do it. I think I'd honestly go with a single big table with a column for each field the ID3 tag spec. Reason being that ID3 just isn't a relational data model. You're going to have tracks that don't fit well into an album, are going to have inconsistent genres... generally, tagging is full of errors and you'll have trouble enforcing your relational model on it.

What I'd do then, is index the hell out of the thing because after the initial import, it will be updated relatively infrequently. You'll still be able to run all the same queries, just with lots of unique keywords (or is it distinct?) and without joins.

Or maybe my entire design is just stupid, I've never tried such a thing...

Edit: oh, and the above would also be a terrible way to learn database design, so if this is more about learning then the final thing, the please ignore

 

[Alex]

Originally posted by: kamper
That looks like a proper design but it's not how I'd necessarily do it. I think I'd honestly go with a single big table with a column for each field the ID3 tag spec. Reason being that ID3 just isn't a relational data model. You're going to have tracks that don't fit well into an album, are going to have inconsistent genres... generally, tagging is full of errors and you'll have trouble enforcing your relational model on it.

What I'd do then, is index the hell out of the thing because after the initial import, it will be updated relatively infrequently. You'll still be able to run all the same queries, just with lots of unique keywords (or is it distinct?) and without joins.

Or maybe my entire design is just stupid, I've never tried such a thing...

Edit: oh, and the above would also be a terrible way to learn database design, so if this is more about learning then the final thing, the please ignore

thx for the idea!
its actually more of a learning excercise than anything!
i DO have ambitions to set up a complex site about my music library using a mySQL backend but this is me just getting around starting up with mySQL and seeing what i can create!

 

[sourceninja]

Not sql, but I actually had my students write a little bash script to do something similar in a plain text tab delimited file.

This wouldn't solve your problem, but I would thought I'd post it anyways cause it is as simple as it gets. This was the cleanest code a student turned in.

 

[Neverm1nd]

Originally posted by: franguinho
Album table
albumID, AlbumName, AlbumArtistID, rating, year, AlbumGenreID, comments, review
...
AlbumGenre table
AlbumGenreID, AlbumID, GenreID

This combination looks a little odd. What does the last table contain that isn't contained in the first? Is GenreID and AlbumGenreID diffrent? Why?

 

[Alex]

Originally posted by: Neverm1nd

Originally posted by: franguinho
Album table
albumID, AlbumName, AlbumArtistID, rating, year, AlbumGenreID, comments, review
...
AlbumGenre table
AlbumGenreID, AlbumID, GenreID

This combination looks a little odd. What does the last table contain that isn't contained in the first? Is GenreID and AlbumGenreID diffrent? Why?

they are different! GenreID refers to the genre of a particular song within an album. AlbumGenreID refers to the genre of the entire album as a whole!

this is useful for example in sepultura's Chaos AD album, where they have 2 instrumental tracks Jasco and Itsari that are acoustic and played with indians and stuff and are awesome ambient music. however the rest of the album is heavy metal. so this way i can actually classify those 2 songs as being ambient genre whereas the entire album is a heavy metal album!

 

[Neverm1nd]

Originally posted by: franguinho
they are different! GenreID refers to the genre of a particular song within an album. AlbumGenreID refers to the genre of the entire album as a whole!

this is useful for example in sepultura's Chaos AD album, where they have 2 instrumental tracks Jasco and Itsari that are acoustic and played with indians and stuff and are awesome ambient music. however the rest of the album is heavy metal. so this way i can actually classify those 2 songs as being ambient genre whereas the entire album is a heavy metal album!

That's not precisely what I mean. Are they different as in do they contain different kinds of data? From your description, they don't seem to do so. What I mean is that a specific identifier in the GenreID column means the same thing as the same identifier in the AlbumGenreID column.

The Album table already contains AlbumID and AlbumGenreID, so what does GenreID in the AlbumGenre table add? Setting different genres for different tracks is done via the AlbumTracks table, right? The same goes for the AlbumArtist table. What purpouse does it have? If I understand your design correctly, AlbumArtistID in the Album table identifies a row in the AlbumArtist table, which, aside from the redundant AlbumID, only points to a row in the Artist table. If this is the case, you should just get rid of it, and use ArtistID in the Album table instead of AlbumArtistID. The same goes for the AlbumGenre table.

 

[Alex]

Originally posted by: Neverm1nd

Originally posted by: franguinho
they are different! GenreID refers to the genre of a particular song within an album. AlbumGenreID refers to the genre of the entire album as a whole!

this is useful for example in sepultura's Chaos AD album, where they have 2 instrumental tracks Jasco and Itsari that are acoustic and played with indians and stuff and are awesome ambient music. however the rest of the album is heavy metal. so this way i can actually classify those 2 songs as being ambient genre whereas the entire album is a heavy metal album!

That's not precisely what I mean. Are they different as in do they contain different kinds of data? From your description, they don't seem to do so. What I mean is that a specific identifier in the GenreID column means the same thing as the same identifier in the AlbumGenreID column.

The Album table already contains AlbumID and AlbumGenreID, so what does GenreID in the AlbumGenre table add? Setting different genres for different tracks is done via the AlbumTracks table, right? The same goes for the AlbumArtist table. What purpouse does it have? If I understand your design correctly, AlbumArtistID in the Album table identifies a row in the AlbumArtist table, which, aside from the redundant AlbumID, only points to a row in the Artist table. If this is the case, you should just get rid of it, and use ArtistID in the Album table instead of AlbumArtistID. The same goes for the AlbumGenre table.

ok i think i understand what you mean... so i made some corrections... what does this look like to you now:

Album table
albumID, AlbumName, ArtistID, rating, year, GenreID, comments, review

AlbumTracks table
albumID, trackNumber, trackname, GenreID, ArtistID

Artist table
ArtistID, ArtistName

Genre table
GenreID, GenreName

so basically each album can have an ArtistID and a GenreID (which are the "album artist" and "album genre") and then in the albumtracks table there is also a ArtistID and GenreID which are the particular track's artist and genre.

so this can store exactly the same data as the original design but is more efficient right?

this actually got me thinking, so are the artist and genre tables really necessary then???

all they do is correlate the artist and genre IDs to their respective values right?
so since both tables (album and album tracks use the same ID for both artists and genres then really they could just point to the values instead of at an ID that references the value?

so i could just have 2 tables:

Album table
albumID, AlbumName, Artist, rating, year, Genre, comments, review

AlbumTracks table
albumID, trackNumber, trackname, Genre, Artist

if this is correct i now have a new doubt...!

so say i wanna do a search for a particular artist that is featured in a compilation album... so i search by "Artist" (but it'd have to be from the albumtracks table) and that way it would return all tracks by that artist, including both from entire albums by that artist and also any tracks that artist might have from a compilation album right?
at least in theory...

i want this to be really flexible, as in you can search for and organize the data according to all categories!

thanks a lot for your help btw! :beer:

 

[EagleKeeper]

It is more efficeint to search my Artist when using a number than a string.

There is a possiblity that you could mistype the Artist name.

It is best to keep all unique strings as seperate tables and use the tableID/Index

Stick with a table for Genre and a table for Artist as well as album and tracks

 

[Alex]

Originally posted by: EagleKeeper
It is more efficeint to search my Artist when using a number than a string.

There is a possiblity that you could mistype the Artist name.

It is best to keep all unique strings as seperate tables and use the tableID/Index

Stick with a table for Genre and a table for Artist as well as album and tracks

i see your point, but really i'm gonna be searching using a string anyway!

imagine a front-end and there's a search field, i'm gonna type the artist's name, so really the index number just creates an extra step along the way!

 

[EagleKeeper]

And what happens if you mistype the artist name or genre in a field?

by easily having a list of valid names will allow you to select the one to choose. IF what you desire does not exist (via your eyeballs) then you can add the artist/genre to the master list.

Too many times, people will have a typing error and not realize it. Then string matches will fail.

By "selecting" a string, then you will have the proper index available and matches will always pass.
If you ever choose to edit the name (capitalization/hyphenation, etc) then that will not have an impact on the actual data and searches.

 

[Alex]

Originally posted by: EagleKeeper
And what happens if you mistype the artist name or genre in a field?

by easily having a list of valid names will allow you to select the one to choose. IF what you desire does not exist (via your eyeballs) then you can add the artist/genre to the master list.

Too many times, people will have a typing error and not realize it. Then string matches will fail.

By "selecting" a string, then you will have the proper index available and matches will always pass.
If you ever choose to edit the name (capitalization/hyphenation, etc) then that will not have an impact on the actual data and searches.

ok i see your point, so you mean if I make a mistake filling in the artist or genre in the table?

because i mean if you misspell it in the searchbox its not gonna find anything either way!

 

[EagleKeeper]

Instead of using a search box; use a dropdown Combobox.

When the item is not located in the combobox (via eyeball if needed), then you add a new entry

 

[Neverm1nd]

Originally posted by: franguinho

Originally posted by: EagleKeeper
It is more efficeint to search my Artist when using a number than a string.

i see your point, but really i'm gonna be searching using a string anyway!

imagine a front-end and there's a search field, i'm gonna type the artist's name, so really the index number just creates an extra step along the way!

If you have 1000 albums made by 50 artists, it's more efficient to scan the 50 item artist table (especially if you want to do free-text or LIKE queries) than the 1000 item album table. Same goes for genre.

As pointed out earlier, this also makes it a lot easier to have consistent naming of artists and genres, or you may end up with some albums by "Firstname Lastname", some by "Lastname, Firstname", etc.

Without going into stuff like normalization, as a general rule of thumb, try not to repeat data, especially strings. In your system, you have artists for albums and tracks. In more complex systems, the same data may be in many tables. So what happens when an artist suddely decides that he will now call himself something else? If the artist name is in one, central place, you can easily and efficently update all his listings in your database by changing one field, instead of each and every instance in the tracks and albums tables.

 

[EagleKeeper]

Originally posted by: Neverm1nd

Originally posted by: franguinho

Originally posted by: EagleKeeper
It is more efficeint to search my Artist when using a number than a string.

i see your point, but really i'm gonna be searching using a string anyway!

imagine a front-end and there's a search field, i'm gonna type the artist's name, so really the index number just creates an extra step along the way!

If you have 1000 albums made by 50 artists, it's more efficient to scan the 50 item artist table (especially if you want to do free-text or LIKE queries) than the 1000 item album table. Same goes for genre.

As pointed out earlier, this also makes it a lot easier to have consistent naming of artists and genres, or you may end up with some albums by "Firstname Lastname", some by "Lastname, Firstname", etc.

Without going into stuff like normalization, as a general rule of thumb, try not to repeat data, especially strings. In your system, you have artists for albums and tracks. In more complex systems, the same data may be in many tables. So what happens when an artist suddely decides that he will now call himself something else? If the artist name is in one, central place, you can easily and efficently update all his listings in your database by changing one field, instead of each and every instance in the tracks and albums tables.

Example: How many times had "Prince" had a new name

 

[Alex]

Originally posted by: EagleKeeper

Originally posted by: Neverm1nd

Originally posted by: franguinho

Originally posted by: EagleKeeper
It is more efficeint to search my Artist when using a number than a string.

i see your point, but really i'm gonna be searching using a string anyway!

imagine a front-end and there's a search field, i'm gonna type the artist's name, so really the index number just creates an extra step along the way!

If you have 1000 albums made by 50 artists, it's more efficient to scan the 50 item artist table (especially if you want to do free-text or LIKE queries) than the 1000 item album table. Same goes for genre.

As pointed out earlier, this also makes it a lot easier to have consistent naming of artists and genres, or you may end up with some albums by "Firstname Lastname", some by "Lastname, Firstname", etc.

Without going into stuff like normalization, as a general rule of thumb, try not to repeat data, especially strings. In your system, you have artists for albums and tracks. In more complex systems, the same data may be in many tables. So what happens when an artist suddely decides that he will now call himself something else? If the artist name is in one, central place, you can easily and efficently update all his listings in your database by changing one field, instead of each and every instance in the tracks and albums tables.

Example: How many times had "Prince" had a new name

:laugh:
fair enough... altho i'm glad to say i don't have any prince in my collection!

the good news is that all my music, rouhgly 800 albums from 150 artists is all meticulously organized in folders and tagged using a predefined scheme!