There is a regular Users (UserID, UserName, Email, Password) table which handles user registrations.
Which other tables should be added in order to handle paid membership with 2 types of paid subscriptions - Monthly and Yearly.
views:
25answers:
2
+1
A:
How about adding a membership field to the users table with one of three values- null, monthly, or yearly?
Beth
2010-08-19 19:40:29
And how do you know whether it was paid or not, when it expires etc.
Grief Coder
2010-08-19 19:44:15
Well, whatever other fields you need, you can add, it just doesn't have to be in another table.The relationship between a user and the user's membership details is one-to-one, unless you expect users to have simultaneous memberships. Then, you'd need a separate userMembership table.
Beth
2010-08-19 19:46:38
I fill like it should be one-to-many instead - this way at least admin well be able to see purchased subscriptions, which expired which didn't. For example user already has 1 month subscription but he wants to purchase 1 year subscription without waiting until his current subscription expires.. A good example is Xbox.com website where you can buy Gold membership.
Grief Coder
2010-08-19 19:53:19
admin can see everything in all tables, so one-to-many doesn't matter. If you want one user to be able to have many simultaneous memberships, then you need another table. Otherwise, you can store the startDate, expDate (or membershipLength, in months, for example) in the user table.Start with it however you want, work with it awhile, then change it if you decide later it wasn't the right way to go.
Beth
2010-08-19 19:59:05
I'd say add another membership table, so you have room for expansion later even if you don't use it to its fullest now. If you plan for the future it'll be easier when your needs change. A table like that will also enable you to look at spending patterns etc. etc.
JNK
2010-08-19 20:11:07
+2
A:
I think you may want to distinguish between membership details and transactions. I like the idea of adding a membership_type column (should be a tinyint column with a separate lookup table), then having a membership_expiration column as well. If you want to track each membership purchase, you can have a separate transaction table that tracks purchases. This also gives you the ability to expand to other types of purchases or transactions in the future without modifying the data model.
bluefooted
2010-08-20 02:45:43
Sounds good. Probably one more column needed - membership_rate (1 month | 3 months | 1 year) so next time payment is due website could decide which rate to apply
Grief Coder
2010-08-26 17:43:52