What would be a good Database strategy to manage these two product options?
- by bemused
I have a site that allows users to purchase "items" (imagine it as an Advertisement, or a download). There are 2 ways to purchase. Either a subscription, 70 items within 1 month (use them or lose them--at the end of the month your count is 0) or purchase each item individually as you need it. So the user could subscribe and get 70/month or pay for 10 and use them when they want until the 10 are gone.
Maybe it's the late hour, but I can't isolate a solution I like and thought some users here would surely have stumbled upon something similar. One I can imagine is webhosts. They sell hosting for monthy fees and sell counts of things like you get 5 free domains with our reseller account. or something like a movie download site, you can subscribe and get 100 movies each month, or pay for a one-time package of 10 movies.
so is this a web of tables and where would be a good cross between the product a user has purchased and how many they have left?
products
productID, productType=subscription, consumable, subscription&consumable
subscriptions
SubscriptionID, subscriptionStartDate, subscriptionEndDate,
consumables
consumableID, consumableName
UserProducts
userID,productID,productType ,consumptionLimit,consumedCount
(if subscription check against dates), otherwise just check that consumedCount is < than limit.
Usually I can layout my data in a way that I know it will work the way I expect, but this one feels a little questionable to me. Like there is a hidden detail that is going to creep up later. That's why I decided to ask for help if someone in the vast expanse can enlighten me with their wisdom and experience and clue me in to a satisfying strategy.
Thank you.
