Search code examples
mysqlforeign-keysrelationships

MySql Shared lookup table

I've got two 'event' type tables, both of which can have a list of 'users' involved with the event. I'd like to use a common lookup table for the users, but I'm not sure how to setup the FKs.

Highlight:
|ID   |Details|

Event:
|ID   |Details|

Contacts:
|event_id  |highlight_id  |user  |type |

This doesn't really work, as event_id and/or highlight_id could be null depending on which event type I'm dealing with.

I'd like to avoid a lookup table for each event if possible

Solution

  • I'd definitely use separate lookup tables for user_event and user_highlight. Unless there is good reason to create a user_id|event_id|highlight_id relation (row), then it makes little sense to define it that way. 

    events
+-----+
event_id
detail

highlights
+---------+
highlight_id
detail

users
+----+
user_id

user_event
+---------+
user_id  (FK)
event_id (FK)

user_highlight
+-------------+
user_id       (FK)
highlight_id  (FK)