❔ ERD Review
I'm working on an ERD for the FE mockups here: https://imgur.com/a/E3XJ0OU. It's a multi-tenant database where a tenant represents a company. Azure AD is used for authentication.
- Name
- TenantId (foreign key to Tenant table)
- Email
- Phone
The Event table represents the different types of events for which notifications can be sent. It could be the different stages of an offer: Offer Filled, Offer Completed, Offer Cancelled, Offer Rejected, Offer Expired.
- EventId (primary key)
- EventName
- AlertToneName
- AlertToneUrl
For each user, we can store their individual preferences for event notification types in the UserEventNotification table. This table also includes the user's chosen alert tone for each event type. The XEnabled column allows users to enable/disable notification types for specific events, but only for those events that were enabled by the administrator in the GlobalEventNotification table.
- UserEventNotificationId (primary key)
- UserId (foreign key to User table)
- EventId (foreign key to Event table)
- InAppNotificationEnabled (boolean, whether in-app notification is enabled for this user and event)
- EmailNotificationEnabled (boolean, whether email notification is enabled for this user and event)
- SmsNotificationEnabled (boolean, whether SMS notification is enabled for this user and event)
- AlertToneId (foreign key to AlertTone table)
- EventId (foreign key to Event table)
- InAppNotificationEnabled
- EmailNotificationEnabled
- SmsNotificationEnabled
Tenant
- TenantId (primary key)- Name
User
- UserId (primary key)- TenantId (foreign key to Tenant table)
- Phone
Event
The Event table represents the different types of events for which notifications can be sent. It could be the different stages of an offer: Offer Filled, Offer Completed, Offer Cancelled, Offer Rejected, Offer Expired.
- EventId (primary key)
- EventName
AlertTone
- AlertToneId (primary key)- AlertToneName
- AlertToneUrl
UserEventNotification
For each user, we can store their individual preferences for event notification types in the UserEventNotification table. This table also includes the user's chosen alert tone for each event type. The XEnabled column allows users to enable/disable notification types for specific events, but only for those events that were enabled by the administrator in the GlobalEventNotification table.
- UserEventNotificationId (primary key)
- UserId (foreign key to User table)
- EventId (foreign key to Event table)
- InAppNotificationEnabled (boolean, whether in-app notification is enabled for this user and event)
- EmailNotificationEnabled (boolean, whether email notification is enabled for this user and event)
- SmsNotificationEnabled (boolean, whether SMS notification is enabled for this user and event)
- AlertToneId (foreign key to AlertTone table)
GlobalEventNotification
- GlobalEventNotificationId (primary key)- EventId (foreign key to Event table)
- InAppNotificationEnabled
- EmailNotificationEnabled
- SmsNotificationEnabled
