Database model for multi-channel notification system

We're currently redesigning the way our application sends out notifications to users (so far, it has been mainly calls at various places in the code, directly sending mails to the user) to a system that allows multiple notification channels and should give the users options to select which notification channels they want to receive their notification on. The database is currently MySQL. The requirements are as follows: - Multiple notification **channels** should be supported (like mail, firebase push notifications, slack) - Some **channels** are targeted to **individual** users (mail, firebase), while others are **not user specific** (messages to Slack channels) - Not all **notifications** support all **channels** - **Notifications** are uniquely identified by a name (string) and belong to exactly one **notification category** - Each **channel** requires a certain set of **parameters for each notification** supported (e.g. for mail, we require a template_id associated with the notification, for firebase we require a title and body template, and so on) - **Users** should be able to select which b they want to receive **notifications on, per category** - Some combinations are mandatory (e.g. mail is required for almost all notifications, firebase is typically optional) So far, we came up with a design similar like this. In the meantime, I've come up with a second iteration, which may or not be better, it does address some of the concerns in terms of normalisation though: There is a set of questions around this: 1. The notification (now notification_event) table is what maps the individual name to all the fields that are required for the various channels, such as email_template_id for mail or push_*_template for firebase. A value of NULL means that this particular channel is not supported for this notification. It feels wrong in terms of database normalisation to store these values directly in the same table, as the table structure would need to change every time a new notification channel is added. How to handle these per-channel settings correctly? 2. There are some combinations that are mandatory (e.g. a category with mail_mandatory set to true). Should we pre-create one entry for each user in user_notifications and simply mark them as enabled there, or is it better to not create these settings in the first place and ensure in code that the default configuration always applies, even if for whatever reason the entry in user_notification says otherwise (this should be prevented by the API in the first place, its still a design decision though). Trying to answer some of the questions that came up in the comments: - The backend is written in Python, using SQLAlchemy as ORM. API is FastAPI, with arq as worker for async task. The notifications are triggered anywhere, but submitted / sent by the worker. - There are roughly 25 different events / notification types, around 1000 users. - The amount of notifications is rather low - I'd say typically below a 100 each day. - Today, we only have notifications via mail. Currently we plan an extension and support Firebase push notifications as well as slack channel notifications, which led us to re-think the system and make it more modular to grow in the future (e.g. show notifications on the website, ...)