0

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.

2
  • 1
    No, don't go with comma-separated string in RDMBS, it's gonna hurt in the long run. So normalize your table.
    – Biju jose
    Commented Dec 16, 2019 at 7:01
  • Do you anticipate wanting to be able to run a report like "show all alerts that Bob is subscribed to"? Or to be able to do something like updating the email address of a person/ distribution group once and have that apply to all alerts rather than going in to each alert? If so, normalizing the data have obvious benefits. Commented Dec 16, 2019 at 18:47

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.