[DB]3-way relationship between tables?

[LazyManc]

Trying to work this out on a bit of a hangover day isn't the best idea so I'm asking for some help.

I have 3 tables that I need to relate:
news_item
event_type
whitelabel

news_item is just a table for storing news articles (title, body, etc).

event_type is basically a genre, i.e. music / sport / theatre / comedy / etc

whitelabel is a branded website.

A news item needs to be associated with multiple genres so that it displays in the correct section - i.e. if it's a musical comedy theatre production then it needs to display on the musical, comedy, and theatre pages.

BUT... this is dependent on which whitelabel is displaying the info - e.g. a whitelabel site may want to display it on the muscial and theatre pages but not the comedy page for some reason.

So... what associative tables do I need to link it all in this manner?

 

[LazyManc]

Yeah event_type is the genre, I should've kept genre out of it

I don't think it's as simple as just saying:

Show me all the news items where event type in ( musical, comedy ) and whitelabel in ( siteA, siteB)

because I need different event types for some of the whitelabels - if I have news_item_event_type and news_item_whitelabel then it fixes the event types for that news item across all whitelabels!

 

[LazyManc]

Let me give an example.

I'm in whitelabel X, and I want to see all news_items for event_type = "rock"

but in whitelabel Y, these same news_items could be event_type = "indie rock"

 

[LazyManc]

So now, if I want to create a news_item:

(taking liberties with syntax here)

newsItemId = INSERT INTO news_item ....
INSERT INTO news_item_genres (newsItemId & genreId) for each genre
INSERT INTO whitelabels_genres (whitelabelId & genreId) for each genre

this is where I'm confused...


What about:

table: news_item_genre_whitelabel
fields: newsItemId, genreId, whitelabelId

inserting a news_item:
newsItemId = insert (news item)
for each genre selected {
for each whitelabel selected {
insert (newsItemId, genreId, whitelabelId)
}
}