Database Tables
notification_services
This table stores information about the different notification services available in the system. Each service represents a source or category of notifications (e.g. Rating, Waitlist).
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | integer | Primary Key, Auto-generated | Unique identifier for the service |
| name | varchar(255) | Not null | Name of the notification service |
| created_at | timestamp | Auto-generated | Timestamp when the record was created |
| updated_at | timestamp | Auto-generated | Timestamp when the record was last updated |
| created_by | integer | Foreign Key (customer), nullable | Admin Customer who created the service |
| updated_by | integer | Foreign Key (customer), nullable | Admin Customer who last updated the service |
notification_preferences
This table stores each customer's preferences for notifications. It tracks whether a customer has opted in or opted out of specific notification types (Email, SMS) for particular services.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | integer | Primary Key, Auto-generated | Unique identifier for the preference |
| customer_id | integer | Foreign Key (customer), Not null | Customer to whom this preference belongs |
| notification_service_id | integer | Foreign Key (notification_services), Not null | Related notification service |
| notification_type | enum | Not null (values: 'email', 'sms') | Type of notification channel |
| reason | varchar/text | Nullable | Optional reason for opt-in or opt-out |
| status | enum | Not null (values: 'opted_in', 'opted_out') | Current status of the preference |
| created_at | timestamp | Auto-generated | Timestamp when the record was created |
| updated_at | timestamp | Auto-generated | Timestamp when the record was last updated |
Constraints
- Unique constraint on
(customer_id, notification_service_id, notification_type)to prevent duplicate preferences for the same customer, service, and notification type combination.
customer
This table represents the end users who receive notifications. It is referenced by both the notification_services and notification_preferences tables.
Summary
- The
notification_servicestable defines all available notification services. - The
notification_preferencestable links customers to their opt-in/opt-out choices per service and notification channel. - These tables work together to allow the system to efficiently check and respect users' notification preferences before sending any messages.