I'm using MariaDB.
I'm designing a simple application that can send notifications to subscribers for when certain websites they've subscribed to have been updated.
A user can be subscribed to several different sites and multiple users can subscribe to same site.
So both tables are independent in a vacuum. Simple schema for both, just a
- sites table containing ids and urls
- subscribers table containing ids and phone#s
My tentative flow for efficiency and scalability is something like the following
- Cron job every hour that gets all Sites from sites table
- Makes parallel web requests for all sites
- Detect sites that have been changed (this will be compared against to another column in sites table)
- Alert users of changed sites which sites got updated (prob also send alerts in parallel)
Is introducing a junction table even necessary for this? I feel like it'd introduce complexity bc I'd have to join on that everytime and update that everytime a change has been made to either subscribers table.
Or.. should I just denormalize..
Seems like sites is kind of the "common denominator" here (i.e. I'll always check if EVERY site has changed, but I don't necessarily need all subscribers)
So given ^, I was thinking of maybe just adding a Sites.subsriber_ids field..
Or..should I just go for a NoSQL approach?
Any thoughts and recommendations ?
CREATE TABLE provsub ( provider_id int(11) NOT NULL, subscriber_id int(11) NOT NULL, created timestamp NOT NULL DEFAULT current_timestamp(), updated timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (provider_id,subscriber_id), KEY subscriber_id (subscriber_id), CONSTRAINT provsub_ibfk_1 FOREIGN KEY (provider_id) REFERENCES providers (id), CONSTRAINT provsub_ibfk_2 FOREIGN KEY (subscriber_id) REFERENCES subscribers (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |