Normalisation second opinion request.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Normalisation second opinion request.

Post 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.
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Re: Normalisation second opinion request.

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Yes, that's standard many-to-many link table approach.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

ah yes, forgot about the ability to PK both columns, will use that.

Thanks.
Post Reply