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.

.Jay. · 10 Jan 2012 at 19:45

$sql="SELECT DISTINCT genre.SID, song.Title, song.ID, song.Length, instrument.Instrument
FROM genre
LEFT JOIN song ON genre.SID = song.ID
LEFT JOIN song ON instrument.SID = song.ID
WHERE genre.genre = 'POP'
AND instrument.instrument = 'Guitar'";

I've not tested that however that should be right.

skyripper · 10 Jan 2012 at 19:46

Code:

SELECT genre.SID, song.TITLE, song.ID, song.LENGTH
  FROM genre, song, instrument
 WHERE genre.SID = song.ID  -- I'd rename song.ID to song.SID really
   AND song.SID = instrument.SID
   AND instrument.INSTRUMENT='guitar'
   AND genre.GENRE='pop'

I was bored waiting for a 3 page piece of SQL to return a couple of million rows from a database at work...

Haircut · 10 Jan 2012 at 19:47

If you're properly normalising things the Genre table shouldn't have a reference to any of the songs.
Genre should exist as a separate table and then have another table, something like SongGenre that joins the two together.

Otherwise you'll have duplication in the Genre table.

Instrument should work the same way.

skyripper · 10 Jan 2012 at 19:50

Haircut has a point there.. any one song could have multiple Genres and multiple Instruments..

So Song would need two child records, one for Song to Genre relationship, and one for Song to Instrument relationship.

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?

.Jay. · 10 Jan 2012 at 19:54

Just to add to that Haircuts point, if you are attempting to do things properly you should normalise the tables.

One song can have many genres (i.e. Christmas songs could be Christmas & Classic)
One song can have many instruments

Therefore you would want a database structure similar to:

Song <-- Song_Genre --> Genre

Song <-- Song_Instrument --> Instruments

Then song would look like:

song_id (PK)
title
length

Genres would look like:

genre_id (PK)
title (or name, whatever you want to call it)

Instruments would look like:

instrument_id (PK)
name

Then in the middle have using two columns as a primary key to make a compound key:

genre_id (PK)
song_id (PK)

And the same for Instruments / Song

instrument_id (PK)
song_id (PK)

That way you can then pull all of the genres and instruments for one song to get results such as:

Title: Song X
Length: 2:30
Genre: Pop
Instrument: Drums
Instrument: Piano

Thsharp01 · 10 Jan 2012 at 19:58

Exactly the help I needed. Fantastic. Cheers fellas!

Haircut · 10 Jan 2012 at 20:01

Yep, pretty much what Jay has said there.

As well as the duplication issue, it makes it much easier to add or modify information in your tables.
For instance, if you wanted to extend Instrument to include the type of instrument and then do a query for all songs containing stringed instruments you just need the handful of instruments that you have rather than doing it for every song.
The same applies to renaming something, you do it in one place rather than for each row where the name exists.

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. ??

RobH · 10 Jan 2012 at 21:49

You just have the order of the joins wrong, you need to have the link tables before the destination table.

PHP:

SELECT DISTINCT
  *

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.name = 'Pop'
  AND
  instrument.name = 'Piano'

.Jay. · 10 Jan 2012 at 21:53

Give this a go:

PHP:

SELECT DISTINCT song.title, genre.name
FROM song
JOIN song_genre ON song.song_id = song_genre.song_id
JOIN song_instrument ON song.song_id = song_instrument.song_id
JOIN genre ON song_genre.genre_id = genre.genre_id
JOIN instrument ON song_instrument.instrument_id = instrument.instrument_id

Edit - RobH beat me to it!

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.

RobH · 3 Mar 2012 at 17:17

Your condition in the WHERE clause is too restrictive, it's looking for a record which has the instrument_id of 1 AND 2 which will never be the case.

There are a couple of solutions:

PHP:

WHERE
  genre.genre_id LIKE '%'
  AND (
    instrument.instrument_id='1'
	OR
    instrument.instrument_id='2'
  )

Or use the 'IN' clause:

PHP:

WHERE
  genre.genre_id LIKE '%'
  AND
  instrument.instrument_id IN (1, 2)

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).

topdog@OC · 4 Mar 2012 at 02:36

Which DBMS? Since you're using PHP I'm going to guess at MySQL but if it's not, what are you using?

Code:

select * from (
select s.name, concat(',', group_concat(i.name), ',') as all_instruments
from song s
join song_instrument si on s.id = si.song_id
join instrument i on si.instrument_id = i.id
group by s.name
) squished
where all_instruments like '%,piano,%'
and all_instruments like '%,guitar,%'
;

Full demo using mocked tables and data:

Code:

create table song(id numeric, name varchar(255));

create table instrument(id numeric, name varchar(255));

create table song_instrument(id numeric, song_id numeric, instrument_id numeric);

insert into song(id, name) values (1, 'Jingle Bells');
insert into song(id, name) values (2, 'Never Gonna Give You Up');
insert into song(id, name) values (3, 'Raining');
insert into song(id, name) values (4, 'Sons of Metal Skyrim');

insert into instrument(id, name) values (1, 'Bells');
insert into instrument(id, name) values (2, 'Hells Bells');
insert into instrument(id, name) values (3, 'Angelic Voice');
insert into instrument(id, name) values (4, 'Guitar');
insert into instrument(id, name) values (5, 'Piano');

insert into song_instrument (id, song_id, instrument_id) values (1, 1, 1);
insert into song_instrument (id, song_id, instrument_id) values (2, 2, 2);
insert into song_instrument (id, song_id, instrument_id) values (3, 2, 3);
insert into song_instrument (id, song_id, instrument_id) values (4, 3, 4);
insert into song_instrument (id, song_id, instrument_id) values (5, 2, 5);
insert into song_instrument (id, song_id, instrument_id) values (6, 3, 5);
insert into song_instrument (id, song_id, instrument_id) values (7, 4, 4);

commit;

select s.name as song, i.name as instrument
from song s
join song_instrument si on s.id = si.song_id
join instrument i on si.instrument_id = i.id
order by s.id, i.name
;

select * from (
select s.name, concat(',', group_concat(i.name), ',') as all_instruments
from song s
join song_instrument si on s.id = si.song_id
join instrument i on si.instrument_id = i.id
group by s.name
) squished
where all_instruments like '%,piano,%'
and all_instruments like '%,guitar,%'
;

-- cleanup
-- be sure you want to run these before you run these..
-- 
-- drop table song;
-- drop table instrument;
-- drop table song_instrument;

Results:

Code:

song			instrument
Jingle Bells		Bells
Never Gonna Give You Up	Angelic Voice
Never Gonna Give You Up	Hells Bells
Never Gonna Give You Up	Piano
Raining			Guitar
Raining			Piano
Sons of Metal Skyrim	Guitar


name	all_instruments
Raining	,Guitar,Piano,

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?

topdog@OC · 6 Mar 2012 at 14:38

Two many-to-many joins is causing them to multiply (2 on one side, 3 on another, 6 results). It also seems you have a mixup in your query criteria and the results, but I'll make the assumption the criteria was looking for pop or latin and 2 of the piano/drums/percussion results matched.

You'll want to squash the many-to-many results individually as subqueries before folding them back into the song table.

This approach of compressing all the results in the table and then filtering them isn't the most efficient (by far), but it makes for easier code to write the sql on the fly and less data filtering in PHP. You may be able to offset that performance cost with some smart indexing to a satisfactory level.

If I extend my previous demo to include these tables and rows:

Code:

create table genre(id numeric, name varchar(255));

create table song_genre(id numeric, song_id numeric, genre_id numeric);

insert into genre(id, name) values (1, 'Pop');
insert into genre(id, name) values (2, 'Latin');

insert into song_genre(id, song_id, genre_id) values (1, 2, 1);
insert into song_genre(id, song_id, genre_id) values (1, 2, 2);

commit;

And then modify my query (or run another one) like this:

Code:

select s.name, all_instruments, all_genres
from song s
join (
select si.song_id as song_id, concat(',', group_concat(i.name), ',') as all_instruments
from song_instrument si
join instrument i on si.instrument_id = i.id
group by si.song_id
) i
on s.id = i.song_id
join (
select sg.song_id, concat(',', group_concat(g.name), ',') as all_genres
from song_genre sg 
join genre g on sg.genre_id = g.id
group by sg.song_id
) g
on s.id = g.song_id
where all_instruments like '%,piano,%'
and all_instruments like '%,angelic voice,%'
and all_genres like '%,pop,%'
;

It produces:

Code:

 name                     all_instruments                    all_genres    
 -----------------------  ---------------------------------  ------------- 
 Never Gonna Give You Up  ,Hells Bells,Angelic Voice,Piano,  ,Pop,Latin,   

 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 

 [Executed: 06/03/12 14:36:40 GMT ] [Execution: 10/ms]

N.B: This wasn't meant to be a rick-roll, really...

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.