Relations between 3 mysql tables

Question

Hello. I have 3 mysql tables: events, artists and (artist) descriptions. All of them are in many-to-many relations.

Table 'events':

 ID | eventTitle | etc.
-----------------------
  1 | event 1    |
  2 | event 2    |
etc.

Table 'artists':

 ID | artistName | etc.
-----------------------
  1 | artist 1   |
  2 | artist 2   |
etc.

Table 'descriptions':

 ID | artistDesc             | etc.
----------------------------------
  1 | artist 1 description 1 |
  2 | artist 1 description 2 |
  3 | artist 2 description 1 |
  4 | artist 2 description 2 |
  5 | artist 3 description 1 |
etc.

I made also junction tables events_artists and artists_desctriptions. Both of them have only 2 foreign keys and serve only for linking event, artist and description IDs.

Notice in my descriptions table - each artist can have many descriptions. That actually means that each description belongs to only one specific event. =)

If I do a query like this:

$q = "SELECT 
        events.*,artists.*,descriptions.*,events_artists.*,artists_descriptions.* 
        FROM 
        events,artists,descriptions,events_artists,artists_descriptions 
        WHERE 
        events.eventID = events_artists.eventID AND 
        events_artists.artistID = artists.artistID AND 
        artists.artistID = artists_descriptions.artistID AND 
        artists_descriptions.descID = descriptions.descID";

I will get all the descriptions for a particular artist. But none of descriptions will be aware which event they belong to... What I want to display to user is something like this:

EVENT 1
artist 1 (artist 1 description 1)
artist 2 (artist 2 description 2)

EVENT 2
artist 3 (artist 3 description 6)
artist 1 (artist 1 description 3)

etc.

Should I make a junction table for event-description relation? If I do, I don't know exactly how to use it, uff! =) Or maybe my problem isn't solvable with a simple query? Should I do something with php too? Sorry but I am totally confused =)

I hope I managed to explain my problem properly...

Thanks in advance for any help!

Answer 1

you should combine events_artists & descriptions tables to have only 1 table that links artist to an event and a description.

Answer 2

Can you modify the tables you already have? If so, and if each artist description can have only one artist and one event, then I would modify your schema to be:
Table event as is.
Table artists as is.
Table descriptions:

ID | artistDesc                     | artistId | eventId | etc.
------------------------------------------------------------------
 1 | description of artist at event | 4        | 1       | ...

Then you can select all descriptions for an event with:

$query = "SELECT 
    events.*,artists.*,descriptions.*
    FROM 
    events,artists,descriptions
    WHERE 
    artists.artistID = descriptions.artistID AND 
    events.eventID = descriptions.eventID AND
    events.eventID = $eventIdYouWant";

You can change the last row of that query to either events.event_name = $eventNameYouWant or artists.artist_name = $artistNameYouWant and it will work the same as if you were directly specifying the id.

Answer 3

you have already said its a relation between 3 tables .. so i think the answer is pretty obvious .. you need to have 1 table artist_id , event_id and description_id in ONE table instead of diving that into two tables like u have now.

Answer 4

I think you should alter your table structure, if you can. It will result in a neater design. Make a table, artist_event_description which contains all the IDs as foreign keys, instead of the two junction tables, it will help you to find out, to which event and artist a description belongs.

Another thing you can do is to include two more columns in the Description table, eventId and artistId (these will be foreign keys) and remove the junction tables. This way you will directly get all the information you need by just doing a SELECT over the description table.