I'm building a website and I'll be charging my customers to use it. I'm thinking of having 6 monthly or yearly licence options and there'll be a few different variations of licences available each granting different types of access to the site.
Also I'll have a few optional extras that you can add on to the purchase of their licence. These would be once-off purchases more like products rather than licences. They're more add-ons for the website that enable more features. Once purchased you get to keep it. So everything's based on the site there's no shipping to worry about.
This will be displayed in some sort of shopping cart fashion where they can choose what they want then sent through to Paypal for processing.
Just wondering how I would model this correctly in my database? So far I've come up with:
Basically I've tried to put licences and products in the same table and used a flag column (licence_or_product) to distinguish between the two. This would help in the display on the website about whether it's a product or licence.
Is it worth having the licence *offering_start_date* and *offering_end_date* fields? I'm trying to get it so that I can expire that offering if I want to make a new licence offering with a different price. That way customers who have already purchased the licence at that price it always stays at that price. If I want to change the price, I'll set the offering end date to today, then make a new licence_products row with a lower price but end_date in the future. Is that the right way to do it? Or do you just edit the price of the item directly? I'm just thinking if the price changes then the customer wants a refund for that licence, how do you track how much they paid for that licence? The amount_paid field would tell me how much the whole order cost but not the original price of the licence which has now changed. Do I need a history of all the product price changes?
Other thing I don't know how to do, lets say they've purchased a licence to use the site for a year. How do I pull out which is the most current licence? Lets say I've started them off with a 30day trial licence, then they purchase a yearly licence. Do I invalidate the trial licence immediately? I think I only want one valid licence at a time. So if they buy another licence before the old one expires I'm not sure how my program should handle that.
Any help much appreciated. Thank-you!
P.S. Here's the SQLyog database schema and SQL if you want to have a go at reworking it a little.
I think you should create the following 3 tables:
1. customer (client or whatever make sense to you),
2. license and
3. customer_license.
And I suggest the following fields for the customer_license table:
This way you can track all details of the customer with the license you offered them: what you offered? when you offered? when does it expire? price offered? the license is active?
Good luck
