MySql user subscription tracking

Need ideas on the best way to track which subscriptions a user has

[brian7742]

Hello,

I can't think up a good way of designing the database for this.

I have a website that is going to have several newsletters, which will increase and decrease from time to time. I've thought about just adding an extra column to the user database for every subscription, but I think this could get crazy with 50-100 different newsletters.

I've thought about adding the user IDs to the newsletter's database, but I am not sure about that either.

Any ideas on the best way to go about this?

Thanks

[Frank_Rizzo]

Users
-----
userid
name
details
etc

1, A Smith, London
2, B Jones, Paris

Newsletters
-----------
newsid
name
details

1, Widgets Weekly, newsletter for widget fans
2, Extreme Widgets, newsletter for extreme widgets

Subscriptions
-------------
id
userid
newsid

1,1,1
2,1,2

Three tables. They should be self explanatory.

The Subscription table will hold records of users and their subscriptions. When a user subscribes to a new newsletter you add a new record

INSERT INTO subscriptions (userid, newsid) VALUES ($userid, $newsid)

When a user wishes to unsubscribe from a newsletter

DELETE FROM subscriptions where userid = $userid and newsid = $newsid

To answer your other question on the other thread. To find out if a user has any subscription:

SELECT COUNT(*) from subscriptions where userid = $userid

If the value is > 0 then they have at least one subscription

[rocknbil]

edit: LOL @ FrankRizzo, simul-post . . . you know what they say, great minds and all . . . .

I've thought about just adding an extra column to the user database for every subscription, but I think this could get crazy with 50-100 different newsletters.

Correct, and you'll have to modify the database every.time.you add one.

I'd approach it something like this:

users:
record_id¦user_id¦first_name¦last_name.....

newsletters
record_id¦nl_id¦title¦letter_content.....

subscriptions
record_id¦user_id¦nl_id¦start_date¦end_date¦auto_renew¦fee_paid¦renew_fee

select
users.first_name,users.last_name,newsletters.title,
subscriptions.start_date,subscriptions.end_date,
subscriptions.fee_paid
from users,subscriptions
where users.user_id=subscriptions.user_id
order by subscriptions.end_date desc;

.... will list the user's subs by the most recent.

This will allow unlimited addition of newsletters without modifying the database at all.

Note the added fields in subscriptions, you will likely need to add more. In this example, if you have a fee structure and guarantee the renewal price will not change, you can raise your prices later and always refer to the user's record for the renewal so their subscription fee doesn't change.

[brian7742]

ahhhhh perfect. A separate table, I don't know why that didn't cross my mind haha.

Thanks a lot for the help guys