I am currently developing a subscription based Analysis report site, and one design feature we have is that users can subscribe to one, or more categories of reports, and reports too, can be relevant to one or more categories.
This has prompted the need to link users to many subscriptions, and also to link articles to many subscriptions.
Currently the tables are:
Users:
User_id, subscription ID[foreign key] (+ other user details)
Subscritpions:
subscription_id, subscription_name
Reports:
report_id, subscription_id (+ other report details)
For a use case example:
Bob registers and subscribes to multiple reports. Subscription ID's 1, 2 and 3.
The administrator has published 1 report, which is relevant to all 3 subscription_ids, 1 report which is only relevant to subscription_id 1, one for 2, etc. or any possible combination of the available id's.
My initial thoughts:
New table User_Subscriptions:
id (PK), user_id, subscription_id
New table Report_Subscriptions:
id (PK), report_id, subscription_id
Opinions, please?
Thanks in advance
EDIT: An example SQL statement:
Code: Select all
SELECT articles.*
FROM articles
WHERE articles.id IN (
SELECT articles_subscriptions.article_id
FROM articles_subscriptions
LEFT JOIN articles
ON articles_subscriptions.article_id=articles.id
WHERE articles_subscriptions.subscription_id IN (
SELECT user_subscriptions.subscription_id
FROM user_subscriptions
WHERE user_subscriptions.user_id = 1)
GROUP BY articles_subscriptions.article_id);