SQL Table data for annual memberships

Question

SQL Table data for annual memberships

bkirk

SSCrazy

Points: 2824
More actions
September 22, 2009 at 4:49 pm

#135169

I already have a 'USERS' table which records my customers details via a web insert form using identity seeds for UserID.
I need to update this table and website to record users under and annual subscription.
For example:
1) Customer Registers on web page - data is inserted into 'Users' table
2) Customer Pays for subscription (eg. $40 flat fee for 1 year) - Data is updated to 'Paid' with return codes from Paypal
I was thinking of just inserting new date fields into my Users table:
- Date Created
- Date Paid
- Expiry Date of Subscription
And then setting up a Stored Procedure via an SQL Agent Job to run daily which compares the current date to the Expiry date and then sets the User to 'Inactive' status when the 1 year subscription runs out.
Can someone tell me if this is a good design or suggest a better one. Remember i need to track memberships as Active/Inactive based on the 1 year paid subscription and also allow for future renewals to the same membership once expired, plus a payment tracking system which may need to be in another linked table?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

"Slowly Changing Dimension Type 2". I couldn't give you a better explanation that what's already on Wikipedia. Take a look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I see them as two seperate entites. I see a user table and a subscriptions table. The user table would have a surrogate key (UserId?) and the username and perhaps a hashed password, or something in that same vein.. The subscriptions table would have a foreign key relationship to the user table in addition to some other field to make each record unique, perhaps Subscription Id.. Nevermind, here is the definitions:

CREATE TABLE dbo.WebUser (

UserId int IDENTITY(1,1) NOT NULL,

UserName varchar(30) NOT NULL,

HashedPassword varchar(128) NOT NULL,

CONSTRAINT PK_WebUser PRIMARY KEY CLUSTERED ( UserId ) )

GO

CREATE TABLE dbo.WebSubscription (

SubscriptionId int IDENTITY(1,1) NOT NULL,

UserId int NOT NULL,

SubscriptionExpireDt datetime NOT NULL,

PayPalTrxId varchar(30) NOT NULL,

CONSTRAINT PK_WebSubscription PRIMARY KEY CLUSTERED( SubscriptionId, UserId ) )

GO

ALTER TABLE dbo.WebSubscription ADD CONSTRAINT FK_WebSubscription_WebUser

FOREIGN KEY(UserId) REFERENCES dbo.WebUser (UserId)

GO

When you do your login you join the WebSubscription table in and decide if their subscription is valid. Also, I only added one PayPal field, there will probably be more, but I just wanted it to show I had thought of that..

CEWII

bkirk SSCrazy Points: 2824 More actions · Answer 3

I agree with your separation of tables for Users and Subscriptions.

At least this way it will allow multiple yearly subscriptions based on expiry dates and paid amounts.

Do you think i need a Trigger or Stored Proc via an overnight Agent Job to change the User Status from Active to Inactive based on Expiry Date?

Or do you think i should simply update my code on the website to always validate a current membership based on current date compared to Expiry date in the paid subscription row?

I would think my webpage programming might be easier throughout simlpy referring to an Active/Inactive User profile, rather than inner joins on the subscriptions table.

I'm guessing the easiest way would be to authenticate upon Registration or Login and store the Active Paid user in a session throughout the site.

Thanks for your input. Any further comments are alway appreciated?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Lowell's idea is good in that it leads to a more normalized database... which I absolutely agree with. To wit, the UserName column should be removed from the WebUser table... use a separate table for UserNames (which helps the ladies easily change their name if they get married/divorced).

You may also want to add a SubscriptionStartDt to the proposed WebSubscription table to make it an SCD2 table. That way, you can also track "loyalty" and several other things in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Do you think i need a Trigger or Stored Proc via an overnight Agent Job to change the User Status from Active to Inactive based on Expiry Date?

I don't see a reason to do this you can get up to the second information with the inner join or an additional query in the login sproc.

Or do you think i should simply update my code on the website to always validate a current membership based on current date compared to Expiry date in the paid subscription row?

Yes

I would think my webpage programming might be easier throughout simlpy referring to an Active/Inactive User profile, rather than inner joins on the subscriptions table.

You should do this in a sproc where you do little more than pass in the username and password or hashed password and the sproc gives back the information. The reason for this is tied to SQL Injection and prevention. Also as an additional thought the lookup of the subscription may be something we do after login. The reason being that we might want to show a different set of pages trying to get the user to re-subscribe.. Either way the lookup of the subscription as part of the login is going to have marginal impact on the login process.

I'm guessing the easiest way would be to authenticate upon Registration or Login and store the Active Paid user in a session throughout the site.

This is more of a web-server question. Once they login the session should probably show them logged in. But be sure NOT to put anything in the URL because the user could fake you out by adjusting the URL to say something like ?Authenticate=True?SomeOtherParam=1 and then when their subscription expired they could still continue to go on using it. This method would likely be called security through obscurity..

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Jeff Moden (9/22/2009)
Lowell's idea is good in that it leads to a more normalized database... which I absolutely agree with. To wit, the UserName column should be removed from the WebUser table... use a separate table for UserNames (which helps the ladies easily change their name if they get married/divorced).
You may also want to add a SubscriptionStartDt to the proposed WebSubscription table to make it an SCD2 table. That way, you can also track "loyalty" and several other things in the future.

I have to disagree wih the username one.. The username is not the key to the table, it should however have a unique constraint (which my script DOESN'T have) on that field. The field tying users and subscriptions together is the UserId which is numeric and I would call it a surrogate key.. So if a lady were to get married (and the new username she wants to use isn't already used) the app can change the username and the subscription information is still tied to it..

As far as the start date, that is completely acceptable in my view, I knew that I left a fair number of fields out of the subscriptions table, and this could be one. As far as loyalty, I think a count of the records for the userid shows loyalty, but the poster might have a different view..

As an additional though I might even add a LastLoginDt to the WebUsers table, that is updated each time the login sproc is called..

CEWII

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

First... sorry... I meant "Elliot"... not "Lowell". I've been looking at a lot of his posts.

I agree with you if there's no "User" table other than the WebUser table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bkirk SSCrazy Points: 2824 More actions · Answer 8

All excellent suggestions and i will take all advice for the separate User and Subscriptions tables.

To keep the post short i did not mention all other fields i use in both tables, but as we are discussing it i have many more columns and other tables containing user data, etc...

I already have LastLoginDates, Start & End Subscription dates, active/inactive users, and more.

Also with Stored Procs i do authenticate correctly to avoid any injections using both front end web form validations and backend integrity checks.

Thanks for all your input.