[RESOLVED] Relations between 3 tables

knucklehead

Hello!
I have a problem with understanding relations between 3 tables.
Let's say I have those 3 tables: events, artists, artist_descriptions.

events<>artists is in many-to-many relation,
artists<>artist_descriptions is in many-to-many relation,
events<>artist_descriptions is in many-to-many relation.

I made junction tables for events<>artists and artists<>artist_description tables, but I'm not sure how should I make that third relation. Is it enough to alter events<>artists junction table with one more foreign key or is it made in some different way?

Thank you very much for help =)

johanafm

You deal with that when retrieving data. The only way an event relates to an artist description is via the artist. But shouldn't artist:artist_description be 1:n relation? Unless you deal with multiple languages anyway.

Still, I trust you have this part the way you want it, so

SELECT E.name, A.name, description
FROM event AS E
	INNER JOIN event_artist AS EA ON E.id = EA.event_id
	INNER JOIN artist AS A ON EA.artist_id = A.id
	INNER JOIN artist_description_artist AS ADA ON artist.id = ADA.artist_id
	INNER JOIN artist_description AS AD ON AD.artist_id = ADA.artist_id

knucklehead

Hey, thanks a lot for help!

johanafm;10930184 wrote:
You deal with that when retrieving data. The only way an event relates to an artist description is via the artist.

Yes, that's the idea =)

johanafm;10930184 wrote:
But shouldn't artist:artist_description be 1:n relation?

Hmmm, in theory, no... In my case one artist can have many descriptions and one description could refer to one or more artists. I know it sounds weird now, but 'the client' wants it that way =)

johanafm;10930184 wrote:
Unless you deal with multiple languages anyway.

Oh, fortunately there is only one language for now =)

I will study your example and report back... I don't use JOIN syntax so I will try to 'implement' it in my WHERE clauses...

johanafm

FROM event, event_artist, artist etc...
WHERE event.id = event_artist.event_id AND etc

knucklehead

Hi again and thanks!

johanafm;10930206 wrote:
FROM event, event_artist, artist etc...
WHERE event.id = event_artist.event_id AND etc

I understand how to make that query but it is obviously hard to me to explain what I need...
So I will try differently =)
My table events:

[CODE] ID | eventTitle | etc.

1 | event 1 |
2 | event 2 |
etc.[/CODE]
Table artists:

[CODE] ID | artistName | etc.

1 | artist 1 |
2 | artist 2 |
etc.[/CODE]
Table descriptions:

[CODE] 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.[/CODE]
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 - artist can have many descriptions. That actually means that each description belongs to 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

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 you understood my problem now… Thanks in advance!

johanafm

Ah, a bit different from how I first understood it. But if I got it right this time, the artist doesn't ever have a description that is NOT linked to an even. So that each description matches an artist at a particular even.

Well, one link table ought to do in this case: (artist_id, event_id, description_id), primary key on all fields.

knucklehead

Whoooa, I actually managed to explain my problem 😃
Ok, I will try with such a table...
Just to ask, since I already have a table which manages the relation between artists and events with keys artistID and eventID could I use that table (just to add one more key - descriptionID) or should I make completely different table which will handle only that events-descriptions relation?
Thank you one more time 😉

knucklehead

And sorry, just to ask you one more thing;

johanafm;10930277 wrote:
Well, one link table ought to do in this case: (artist_id, event_id, description_id), primary key on all fields.

I was reading many articles/tutorials/whatever about relational databases and sometimes people insist to make all keys in junction tables PRIMARY, while other say that it is not really important... I didn't put PRIMARY keys to those tables and I still don't understand relations completely so could you please explain me why you said to make all fields PRIMARY key?
Thanks 😃

johanafm

knucklehead;10930279 wrote:
Whoooa, I actually managed to explain my problem 😃
Ok, I will try with such a table...

hehe, yeah. I really misnterpreted the purpose at first :p

knucklehead;10930279 wrote:
Just to ask, since I already have a table which manages the relation between artists and events with keys artistID and eventID could I use that table (just to add one more key - descriptionID)

Yes, this should do fine. If the primary key/index is (artist, event, description), then the first part of it (artist, event) will be used for when you only link artist and event so that only those two fields are involved.

knucklehead;10930279 wrote:
insist to make all keys in junction tables PRIMARY, while other say that it is not really important...

Well, a primary key means two things from the db engines perspective:
1. Uniqueness constraint
2. Indexed
In your case, I believe you want an index over all three columns, and I also believe that you want it to be unique, so primary key seems like a good idea.

Consider what could happen

-- With uniquness constraint
event		artist		desc
1		1		1
2		1		1		-- OK: same desc is used for this artist at another event
1		2		1		-- OK: another artist is using the same desc
1		1		2		-- OK: first artist has two descriptions at same event

-- Without uniquness constraint, this could happen
1		1		1		-- you managed to insert the exact same data again

And when you later on do a join, either by FROM t1, t2 or by FROM t1 JOIN t2, you will retrieve the same data from the related tables twice.

knucklehead

Brilliant!

Thank you very much for your time and help and for clearing out some basic stuff to me =)
Resolved!