many-to-many

edspace

Hello.

In the process of designing e music web site I set up a many-to-many relationship in mysql and I am paying for it with very difficult queries.

I set up the many-to-many to satisfy these relationships.

One artist has more than one album
One album can have more than one artist.

This allows me to display all the artists that sing in that album and link to other albums they might have in the database.
ex: if someone visits one album from artist A and B I can also link to all the albums from artist A and all the albums from artist B.

Another problem I have is that John Doe might have an album and James Doe might have another album but they might have published an album together under the name of Doe Brothers.
How would you link that to both brothers.

Here are the tables.

| albums |

| alb_id | alb_name | alb_hits | art_count |

| 1 | album1 | 500 | 1 |
| 2 | album2 | 456 | 1 |

| 3 | album3 | 321 | 2 |

--------------------------------------------
| artist |

| art_id | art_name |

| 1 | John Doe |

| 2 | James Doe |

| art_alb |

| art_id | alb_id |

| 1 | 2 |
| 2 | 1 |
| 1 | 3 |

| 2 | 3 |

This looked like a very nice idea at first but when I started to change the queries I realized that it was a lot worse than I thought. My tables are a little bit different from the ones above but I will leave it up to you to come up with a better set-up.

Is there a better way to set up this relationship? (comma delimited text fields, etc)
Is there a query that can send back the top 10 albums based on the alb_hits field?
How would you link an album from Doe Brothers to both John Doe and James Doe and still display Doe Brothers as the artist?

I have not been able to find a lot of info on this topic. An online tutorial or some examples would be greatly appreciated.

Thank you

khaldryck

that's just a try.

maybe you could have another tale called band, where you have a band id and a band name,
and then yet another table band_art with band id and art name in this band.

that way you can know that doe brothers are james and john.

now in your art_alb table, you need another field so you know if the id in art is a band id or an art id.

that way, you can have a single artist appear in more than one band.
the problem is if the band changed.

for exemple, having metallica, you could have cliff burton (for the first album) and then Jason newstead, but both are not in it anymore, so it would mean you have to create a new band with the same name and another id ... or keep track of date ...

i don't know exactly how ... yet.

just let me know if you like this idea.

edspace

It's not a bad idea but I think it's already a little bit too complicated and adding more tables will just make it worse.
What I am trying to accomplish is not worth redesigning my database, rewriting the page again and overload the server.

Thanks for your answer.

dombey

first: the top ten albums query:
Select album_title from albums order by alb_hits DESC limit 10
second:
just a thought, you could just add a column to the artist table, called primary or something and the doe brothers entry would have a 1 in the column and each of the joe, and john brothers entries would have twos in the column to tag them as artisits but not the primary artist for dispaly. This would be an easy fix in your queries, and the table.

edspace

doombey,

For the top ten query I need the name, the artist and the hits.

I decided to change the database again.

album
alb_id
art_id
alb_name
alb_hits

artist
art_id
art_name
art_count

band
band_id
art_id

albums.art_id = artist.art_id AND artist.art_id = band.bad_id

ex:

album table
1, 3, album A, 500
2, 1, album B, 498
3, 2, album C, 854

artist table
1, John Doe, 1
2, James Doe, 1
3, Doe Brothers, 2

band table
3, 1
3, 2

On the average query I can join the albums table with the artists table and this will give me all the info I need. If I need to find related albums for the doe brothers I can query the band table and find the artists that make it up, in this case John and James.

It is similar to khaldryck idea but I took out the art_alb table which was making the querys three lines long.

The top ten query is now very easy.

SELECT * FROM album, artist WHERE album.art_id = artist.art_id ORDER BY alb_hits LIMIT 0, 10

khaldryck

that's good 🙂

you'll just have problem if an artist change band.
(i had this problem in my solution too)

edspace

I am not really concerned about that, at least not right now. The way I have designed the pages I relate the albums based on the artist(s) and not on the name of the band.

For now this is the best balance between functionality and level of difficulty.

Thanks.