Relational database help

Thsharp01 · 10 Jan 2012 at 19:36

Hi guys,

Just need to get my head around something, hopefully somebody can point me in the right direction.

If I am creating a database that stores song information, whereby each song would have multiple genres, would I be right in assuming that my database tables should be setup in a similar fashion to this:

Table: Song
Columns:-
ID
TITLE
LENGTH

Table: Genre
Columns:-
ID
SID (Song.ID)
GENRE

This allows me to then put in as many genres as possible for each song. I can search for specific genres or multiple genres and it returns the correct result.

PHP:

$sql="SELECT DISTINCT genre.SID, song.Title, songs.ID, songs.Length
				  FROM genre
				  LEFT JOIN song ON genre.SID = song.ID
				  WHERE genre =  'Rock'
				  OR genre =  'Pop'";

So, this query will return a single result per song if it contains the genre Pop or Rock, which is exactly what I want.

However, my problem comes about when I try to add a third table. I now want each song to be searchable by instruments used. So, I create a new table similar to the genre.

Table: Instrument
Columns:-
ID
SID (Song.ID)
INSTRUMENT

Now the bit I am stuck on is this next bit, lets say I now want to return all results that have the genre of 'Pop' but also use the instrument 'Guitar'. What sort of query should I be using?

Any help greatly appreciated, and if my database design is completely incorrect please let me know what I can do to fix it!

Many thanks.

Thsharp01 · 10 Jan 2012 at 19:52

Thanks for the quick reply guys. I suppose the question is, which is the best way to do this, using left join or just selecting from multiple tables? Going to test both solutions in a few minutes.

Also Haircut I understand where you are coming from, because at present the genre table could for example have 15 rows of Guitar, but with a different SID.

So would I have a table of genres, and then SongGenre that just links SID and GID?

Thsharp01 · 10 Jan 2012 at 19:58

Exactly the help I needed. Fantastic. Cheers fellas!

Thsharp01 · 10 Jan 2012 at 21:29

OK I am a bit confused now about normalising the database. How exactly do I pull everything together.

PHP:

SELECT DISTINCT *
FROM song
LEFT JOIN genre ON genre.genre_id = song_genre.genre_id
LEFT JOIN instrument ON song_instrument.instrument_id = instrument.instrument_id
LEFT JOIN song_genre ON song_genre.song_id = song.song_id
LEFT JOIN song_instrument ON song_instrument.song_id = song.song_id
WHERE genre.name = 'Pop'
AND instrument.name = 'Piano'

That doesn't work but in my head something like that should be happening (I am perhaps using an incorrect join), what I am trying to imagine is that I need to join the song_id to both song_genre and song_instrument and then need to join the genre_id to song_genre.genre_id and and instrument_id to song_instrument.instrument_id. ??

Thsharp01 · 3 Mar 2012 at 16:26

Hi again guys, dragging up an old thread but I am still playing with this and have come across an issue that I cannot get my head around.

PHP:

sql="SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig
FROM song
LEFT JOIN song_genre ON song_genre.song_id = song.song_id
LEFT JOIN genre ON genre.genre_id = song_genre.genre_id
LEFT JOIN song_instrument ON song_instrument.song_id = song.song_id
LEFT JOIN instrument ON song_instrument.instrument_id = instrument.instrument_id
WHERE genre.genre_id LIKE '%'
AND instrument.instrument_id='1'
AND instrument.instrument_id='2'
";

Now the above query returns no results, however, if I specify a single instrument it works fine, this appears to be because it is only pulling for first associated instrument ID.

Any way round this, I will be potentially searching for many different instruments, and would like it to return a result (i.e. 1 song may have 10 instruments).

Many thanks.

Thsharp01 · 3 Mar 2012 at 17:48

Cheers for the quick reply but I am still confused. What if a specific song actually does have more than one instrument_id attached to it? I want to search for a record that has say both Piano and Guitar, not Piano or Guitar?

The way the database is laid out, I have a table called instrument, that contains the instrument name and an ID. Then I have a table named song_instrument that links the songs to the individual instruments (contains song_id and instrument_id).

Thsharp01 · 4 Mar 2012 at 16:08

Cheers for the help on this, yes I am using MySQL. Won't have chance to have a proper look at this until I am back home tomorrow but it looks like exactly what I need.

Thsharp01 · 5 Mar 2012 at 19:10

OK, thanks top dog your code worked great, but I have altered it to fit the requirements of my implementation, but have fun in to some issues

Here is my query, as you can see I have implemented an addition criteria, so we now have genre and instruments. Now this does work, but for some reason it starts repeating the genre and instrument data, and can time out if I add additional criteria to the query.

Code:

SELECT * from (

	SELECT 
	
		song.title,	
		concat(',', group_concat(genre.name), ',') as genres,
		concat(',', group_concat(instrument.name), ',') as instruments
		
	FROM `song`
	
		JOIN song_genre ON song.song_id = song_genre.song_id
		JOIN genre ON song_genre.genre_id = genre.genre_id
		JOIN song_instrument ON song.song_id = song_instrument.song_id
		JOIN instrument ON song_instrument.instrument_id = instrument.instrument_id
		
	GROUP by song.title

) squished

WHERE instruments LIKE '%,Piano,%'
AND instruments LIKE '%,Guitar,%'
AND genres LIKE '%,Uplifting,%'

Returns results:

Code:

title 
Song 1    

genres
,Pop,Latin,Pop,Latin,Pop,Latin,	

instruments
,Piano,Piano,Drums,Drums,Percussion,Percussion,

Any ideas why this is repeating?

Thsharp01 · 6 Mar 2012 at 15:03

Cheers topdog, this looks good, I can see though, as you suggest that this isn't going to be the most efficiant method long term if the result set gets much larger.

Is the database design wrong? Should I be thinking about a re-design, and if so, what pointers can you give me that will set me off in the right direction?

I'll have a play with this later on this evening when I am home. Thank you very much.

Thsharp01 · 6 Mar 2012 at 23:33

Thanks for all the help guys. I have settled on this solution, but have learnt a lot from this thread!

PHP:

SELECT DISTINCT song.song_id, song.title
FROM song

INNER JOIN (SELECT song_id, COUNT(*) FROM song_genre 
WHERE genre_id IN ('1','2') 
GROUP BY song_id HAVING COUNT(*) = 2) genre1 ON genre1.song_id = song.song_id

INNER JOIN (SELECT song_id, COUNT(*) FROM song_instrument 
WHERE instrument_id IN ('1','2') 
GROUP BY song_id HAVING COUNT(*) = 2) instrument1 ON instrument1.song_id = song.song_id