String or separate Table

Question

I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to customize their own alerts so that they can receive alerts when they want, not when I, as the product engineer, thinks they should get them. I'm also allowing admins on the account to create alerts on behalf of other users, i.e. Boss can create alerts to be sent out to employees who have the responsibility to handle the alert.

So, there are three different ways that a user can be signed up for an alert: 1) all employees 2) all admins 3) manually decide which users get it.

I've decided that having a column is_all_users and is_admin as booleans is the right approach to handling those scenarios, but the question is, when dealing with "manually deciding users" is it ok to be a string of user ID's that then get parsed (I don't anticipate there ever being more than 5 recipients in this scenario), or should I make it a separate table?

The two schemas are as follows:

alerts

• client_id (int)
• alert_id (int)
• etc. etc.
• recipients (string)
• recipients_is_admin (boolean)
• recipients_is_all (boolean)

OR

alerts

• client_id (int)
• alert_id (int)
• etc etc.

alerts_recipients

• alert_id (int)
• user_id (int)
• recipients_is_admin (boolean)
• recipients_is_all (boolean)

In scenario 1, there could be a speed and/or parsing concern but scenario 2 just seems unwieldy and unnecessary. Of course, in the second scenario, I could also put recipients_is_admin and recipients_is_all in the first table, but then I'd be dealing with two different tables for a single purpose which just seems wrong.

Answer 1

It may seem unwieldy, but I would go with the approach of having a separate table rather than using a comma-separated string.

In addition to avoiding the parsing complexities, this allows you to do nice things like apply foreign key constraints and / or indexes to that user_id column. It also simplifies mass updates if they are ever needed ("remove user 23 from all alerts").

I would differ from your proposed schema in that I would leave the two boolean fields on the main alerts table, as they apply whether or not there are individual sets of recipients.