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??