We have a contacts table with the following structure:
| id | name | status | last_appointment_at | campaign_id | ... | |
|---|---|---|---|---|---|---|
| 1 | John Smith | [email protected] | HOT | 2023-01-01 | 1 | ... |
| 2 | Jane Doe | [email protected] | COLD | NULL | 1 | ... |
| 3 | Alex Wu | [email protected] | WARM | 2023-05-11 | 2 | ... |
| 4 | Allie May | [email protected] | HOT | 2023-03-24 | 1 | ... |
We want to support user-created "groups" for targeted marketing and improved analytics. These contact groups would essentially be saved filters:
SELECT * FROM contacts
WHERE last_appointment_at > '2023-02-01' AND status IN ('HOT', 'WARM');
What is the best way to represent/cache subgroups created with filters on the parent contacts table? Ideally the relationship would be dynamic, so if a contact's status is changed, they are removed or added to a group.
Our initial thinking was a contacts_groups join table that is updated asynchronously at regular intervals:
| contact_id | group_id |
|---|---|
| 1 | 1 |
| 3 | 1 |
| 3 | 4 |
| 10 | 1 |
| 99 | 3 |
| 99 | 2 |
The contacts_groups table would just serve as a cache that is cleared and updated every hour or so. Is there a better way to achieve this while maintaining a real relationship between contacts and the groups they fall under?