SQL schema design question around relating 2 different types of ID's to one piece of information

Question

I'm working on redesigning some parts of our schema, and I'm running into a problem where I just don't know a good clean way of doing something. I have an event table such as:

Events
--------
event_id

for each event, there could be n groups or users associated with it. So there's a table relating Events to Users to reflect that one to many relationship such as:

EventUsers
----------
event_id
user_id

The problem is that we also have a concept of groups. We want to potentially tie n groups to an event in addition to users. So, that user_id column isn't sufficient, because we need to store potentially either a user_id or a group_id.

I've thought of a variety of ways to handle this, but they all seem like a big hack. For example, I could make that a participant_id and put in a participant_type column such as:

EventUsers
----------
event_id
participant_id
participant_type

and if I wanted to get the events that user_id 10 was a part of, it could be something like:

select event_id
from EventUsers 
where participant_id = 10 
and participant_type = 1

(assuming that somewhere participant_type 1 was defined to be a User). But I don't like that from a philosophical point of view because when I look at the data, I don't know what the number in participant_id means unless I also look at the value in particpant_type.

I could also change EventUsers to be something like:

EventParticipants
-----------------
event_id
user_id
group_id

and allow the values of user_id and group_id to be NULL if that record is dealing with the other type of information.

Of course, I could just break EventUsers and we'll call it EventGroups into 2 different tables but I'd like to keep who is tied to an event stored in one single place if there's a good logical way to do it.

So, am I overlooking a good way to accomplish this?

Answer 1

Tables Events, Users and Groups represent the basic entities. They are related by EventUsers, GroupUsers and EventGroups. You need to union results together, e.g. the attendees for an event are:

select user_id
  from EventUsers
  where event_id = @event_id
union
select GU.user_id
  from EventGroups as EG inner join
    GroupUsers as GU on GU.group_id = EG.group_id
  where EG.event_id = @event_id

Don't be shy about creating additional tables to represent different types of things. It is often easier to combine them, e.g. with union, than to try to sort out a mess of vague data.