Page 1 of 1

Normalisation second opinion request.

Posted: Mon Jul 02, 2007 5:48 am
by Jenk
Hello,

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);
Where 1 would be substituted for any user ID. EDIT2: Now returns article data.

Re: Normalisation second opinion request.

Posted: Mon Jul 02, 2007 3:26 pm
by boo
Jenk wrote: New table User_Subscriptions:
id (PK), user_id, subscription_id

New table Report_Subscriptions:
id (PK), report_id, subscription_id
I would say that this would be the best way to handle the new relationships. Only thing I would change is take off the ID on each table and make the (PK) the user_id and subscription_id on the User_Subscriptions

and report_id, subscription_id on the Report_Subscriptions

Posted: Mon Jul 02, 2007 5:38 pm
by califdon
Yes, that's standard many-to-many link table approach.

Posted: Mon Jul 02, 2007 7:44 pm
by Jenk
ah yes, forgot about the ability to PK both columns, will use that.

Thanks.