what is the best way to store alerts where many users may subscribe to an alert?

Question

I have a system which responds to different types of events:

Type1 events Type2 events Type3 events ...

The purpose of the system is to give users alerts for the types they subscribe to.

I also have users who can subscribe to each of these events. Relational database seem unwieldly for storing which types of events a user is subscribed to. How I set to implement in SQL Server is as the following user's table:

userId   |    Type1    |   Type2   |   Type 3
----------------------------------------------
1        |    True     |           |          
2        |             |   True    |   True     

This might seem already for where we only have two or three types of events, but not when we have hundreds, which is my case.

Can I still implement this in relational databases - and what would I need to change?

Answer 1

Yes, you can still implement this, using relational database.

Let's assume a table users

userId	firstName	lastName
1	Lloyd	Earsley
2	Rose	Xavier

and you have a second table, where you want to store event subscriptions, called user_event_subscriptions:

userId	event
1	1
2	2
2	3

To now check, which users are subscribed for event1, you can JOIN:

SELECT 
  * 
FROM 
  users AS u 
INNER JOIN 
  user_event_subscriptions AS ues 
ON 
  u.userId = ues.userId 
WHERE 
  ues.event = 1

To check, for which events a user is subscribed, (for example Lloyd Earsley), you can query:

SELECT 
  event 
FROM 
  user_event_subscriptions AS ues 
INNER JOIN 
  user AS u 
ON 
  ues.userId = u.userId 
WHERE 
  u.userId = 1