Creating a Discography

[cjlawrence]

Hi

 

For my site I have one MySQL database called "discography". In there I have three tables: "albums", "singles" and "songs".

 

The "albums" and "singles" tables contain fields such as 'id', 'title' and 'releasedate', while the "songs" table contains fields such as 'songid', 'songtitle' and 'songlength'.

 

My site is laid out with a Discography section split into Albums and Singles. If for example you go to the Singles section, by selecting a certain Singles release you are able to view the information stored about it from the "singles" table, what I need to do now is find a way to incorporate a tracklist by selecting songs that appear on each release from the "songs" table.

 

Some songs appear on a Single, and also on an Album, so what I would like to be able to do is something like this (if there's a better way, please suggest one): I would like to add an extra field into the "albums" and "singles" tables called 'tracklist', then for each release I would enter into the 'tracklist' field the 'songid''s (multiple values?) of the song's that appear in that release's tracklist, then somehow when calling up the information for, say, a specific Single release, it would then take the 'songid''s from the 'tracklist' field and display the song title's from the "song" table.

 

This is the closest example I can find online to what i'm trying to do: http://www.jmusiceuropa.com/us/database-item.php?id=6765

 

OR, basically:

 

I want to select all the (multiple) values from a 'tracklist' field in the "singles" or "albums" tables, then display the titles of the songs from the "songs" table that match the selected values against the 'songid's.

 

--

 

I'm fairly new to MySQL, but I have been searching for a few days now and can't seem to find a way to do this. If anyone has any suggestions then I would be really grateful. If you need more information then let me know, not sure what is needed. Thanks in advance for any help. Apologies is this doesn't make sense...

[cjlawrence]

Oh, also:

 

MySQL Server Version: 4.1.22

[fenway]

What have you tried so far? Sounds like a JOIN would work easily...

[cjlawrence]

Thanks for the response.

 

I'm not that familiar with JOINs, being fairly new to MySQL. How would that work in this case?

 

To be honest, I haven't tried much so far as I don't know where to begin. Thanks for any advice you can give me!

[fenway]

Original post was TLDR; could you summarize?

[cjlawrence]

Sure.

 

I'm creating a large detailed Discography:

 

I have created a 'discography' database with a 'releases' table and a 'songs' table.

 

Basically, on my site, when you view the detailed information on a specific release, an album for example, I want the tracklist to be generated by entering in the ID's of the song's (from the 'songs' table) into a 'tracklist' field on the 'releases' table. Or something similar.

 

Alternatively, I suppose it could be generated by entering the ID's of the release's that each song appears on, into an 'appearsOn' field in the 'songs' table, but for me it seems to make more sense doing it the other way.

 

(Just FYI, my reasons for wanting to do this is that I eventually want each song in the tracklist to be a link to a page that displays which release that song is featured on, as a lot of songs in database appear on more than one release. See http://www.jmusiceuropa.com/us/database-title.php?id=14701)

 

Thanks again.

[fenway]

Barand usually makes pretty diagrams... but I'm too lazy :-)

 

You shouldn't use a tracklist field -- you should have a table for tracks and a table for albums, and then include an album_id in the songs table.

[cjlawrence]

Thanks for the response.

 

What if the song appears on more than one album? Is it possible to specify multiple id's for one song?

[fenway]

What if the song appears on more than one album? Is it possible to specify multiple id's for one song?

If that's the case, add a third table, with a (song_id, album_id) record for each pair.

[cjlawrence]

I was hoping there would be a way to do it like this (I made up CONTAINS to show what I want to try and do):

 

SELECT * FROM release, song WHERE song_id CONTAINS ANY OF release_tracklist

 

The release_tracklist value would have been entered as something like this "161, 168, 169, 154". The numbers being the id's of the songs. Something like that would then hopefully return 4 different results?

 

 

[fenway]

Why is that any easier?

[cjlawrence]

Well how do you arrange the songs in the tracklist into the right order the other way? And some songs can appear on up to 5 releases.

[awpti]

Sounds like you need a many-to-many relationship.

 

Essentially, you need a pivot table to handle the relationships back and forth. It is, quite literally, the only way to properly handle many-to-many relationships.

 

CREATE TABLE album_song_link (

  album_id int,

  song_id int

);

 

You can use that to figure out how many albums a song is associated with.

[acidglitter]

Well how do you arrange the songs in the tracklist into the right order the other way? And some songs can appear on up to 5 releases.

 

 

i have to reply now because dir en grey is my favorite band :D

 

i'm not sure if this answers your question, i can't think of a better way right now to have one song be on multiple albums besides just entering each song as multiple rows (like if one song is one two albums, have two rows for that song with the different album id's). its probably not the best way. but if you did that, you could easily just put something like

 

SELECT albums.album_title, albums.other album info, songs.song_title

FROM songs

INNER JOIN albums ON

songs.album_id = albums.album_id

WHERE songs.album_id='1233'

ORDER BY songs.track_number ASC

[cjlawrence]

Thanks for the replies.

 

@ awpti - Looked into many-to-many relationships and it seems that's what I need to be using. I'm having a bit of trouble getting it to work at the moment though. What would I need to post here to get some help getting that to work?

 

@ acidglitter - That's a strange co-incidence! Thanks for your help also.