Subscription Database Design Help!! :idea:

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
shumba
Forum Newbie
Posts: 4
Joined: Tue Jun 06, 2006 11:29 am

Subscription Database Design Help!! :idea:

Post by shumba »

I hope this is the right thread for this.. here goes:

I want to build a subscription service site with Php & Mysql and need some help with the database design. What i want is to have a newsletter subscription service. When a user signs up, they can add family/friends to recieve this newsletter. They will have to pay for each one of these family/friends that they sign up. The subscrion model will be monthly, quarterly or pre pay for a block of consecutive months. Family/Friends subscriptions can be added/removed at anytime and can have different subscription models. For example, Joe signs up and adds his brother and sets to pay for him monthly, then adds his co-worker and sets to pay for her quarterly.

I need help designing a DB structure that will keep track of which family/friends subscriptions are still active to recieve the newsletter and to make sure i stop sending it after their subscription is over. Oh and to save the invoices for each payment.

My initial thought was to have one table to manage the active subscrition in one table. Then i thought maybe i should separate them, one for monthly and one for quarterly and one for pre-paid block of months. HELP!!

Can anyone help??

:oops: Dased and confused.....
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

3 tables mate,

payments, subscription, subscription_type

keep all the payments seperate, have subscription_type to imply hold that a subsciption can be "quarterly" "monthly" or "pre-paid"

stick an end_date column on the subscription table, if the subscription has an end date its not active.
subscription table could also have a start_date there & subscription period on it, just a simple integer say 12 for 12 months, and a column to say how many months have been paid. so if it started on the 03-Mar-2006 and is for 12 months, paid monthly, and the paid_months column is 2 then payment has been made for march and april and due for may and june

make sence? or did i miss read lol
shumba
Forum Newbie
Posts: 4
Joined: Tue Jun 06, 2006 11:29 am

Post by shumba »

Thx for your input nathanr. Sounds good.. but why not just put the subscription_type info in the subscription table?
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

makes it easier to add new subscription types, and also saves hard coding the subscription types into php, with this layout if you want to add in a new subscription_type you just add it to the subscription_type table, and everything on the sites changes in one go, provided you are pulling drop downs from the database instead of hard coding them.. make sence?
shumba
Forum Newbie
Posts: 4
Joined: Tue Jun 06, 2006 11:29 am

Post by shumba »

Totally!!! Cheers mate!!! I really appreciate the input. I'll go with that.

:lol:
Post Reply