February 6, 2011 at 8:29 pm
I want to create a simple membership system where a user registers and pays for 12 months membership. Once the 12 months expires i want to run a job to suspend the membership until they pay for a renewal again.
I assume i will have to have the user status and expiry date in my Users table, but....
I was wondering mostly about the table schema design for the payments table to record a payment each 12 months for renewal. How should i set this up in a one-to-many design to record payments each year?
February 7, 2011 at 1:38 am
Not sure about your Payments table, but why do you need to store the membership status of the user? You could just compare today's date with their last renewal to determine if they're a member or not, no additional update job required!
February 7, 2011 at 10:29 am
I agree with Paul's idea. But you'd have to be careful when doing the math on any hour/minute/second dates. Ideally, you'd want to compare midnight of last renewal date with 11:59 p.m. of today (or yesterday) just to make sure that partial days don't screw up the system.
For example: It would be horrible if someone could log into the website at 9:00 a.m. and then, while they're in the middle of their session / attempt to renew, they lose access because of bad math.
February 7, 2011 at 3:47 pm
Thanks for both comments. I agree with your logic for checking login authentication based on current dates compared to the Expiry dates (even minutes, hours, seconds), however....
If i do not create a linked table of payments then i won't be able to record and show the customer their full payment history in the MyAccount section.
I think what you are saying is to keep all references to the 12 month membership against the 1 User table record and simply apply a foreign key to the payments table to track the history.
Then when a successful payment is made i can run an UPDATE statement on the Expiry date and set it forward another 12 months as well as INSERT the payment records with success or failure.
February 8, 2011 at 5:12 am
Okay, we made our recommendations without knowing all the information that you know about the situation (which is always a hazard).
bkirk (2/7/2011)
I think what you are saying is to keep all references to the 12 month membership against the 1 User table record and simply apply a foreign key to the payments table to track the history.
Not quite. He was suggesting to only have one table and to update the Expiration Date or the Last Payment Date everytime a payment was entered.
Given the new information you've added... If I were doing it, I would have a payment table and a client table and a JOIN table that joins the client and the payment information together. This way neither the client table nor the payment table gets cluttered with non-essential data for that table. The JT (many) would join up to Client (one) and Payment (one).
You could, however, have a client table and a payment table, with the relationship being Payment (many) to Client (one). It's entirely up to you and partially depends on if you want faster WRITE or READ access to the tables. It also depends on how normalized you want your data to be.
February 8, 2011 at 5:17 am
Hi I am new member. Hello all my friends.
February 8, 2011 at 7:58 am
Hello, lopeznail09.
Just an FYI, if you want to just say hello, please post in the "Anything NOT about SQL" forum. Only post in this thread (in all SQL related threads) if you have a question, or a suggestion, related to the current discussion.
February 8, 2011 at 3:17 pm
Thanks for the alternatives Brandie.
I think for sake of overbloating my table design i will probably go with the Users (1) to Payments (many) table design and just JOIN on the UserID.
However i'm interested in your joining table so will give this a go to expand the idea a little further. My only question would be how do i write such SQL statements between 3 tables now? Would this be best performed in a Stored Proc within a Transaction for rollback ability? Would it be:
1) INSERT Payments record
2) INSERT Join table record with UserID, PaymentID key joining fields????
3) UPDATE Users table with Expiry date
????
February 9, 2011 at 4:24 am
It would be something like that. But I believe SQL 2K8 has access to more advanced query language (I'm still on 2k5) that would allow you to Insert and Update almost simultaneously. Hang on for a bit and let me see if I can corral some of the 2k8 T-SQL experts into this thread to help answer that question.
February 9, 2011 at 4:32 am
EDIT: duplicate post
-- Gianluca Sartori
February 9, 2011 at 6:34 am
Gianluca Sartori (2/9/2011)
You're looking for the MERGE statement, I guess.EDIT: clicked the wrong button.
As the two inserts and the update are on three different tables, and when the target of a MERGE statement is a view all the updateable view restrictions apply (so all the columns inserted or modified have to be in the same base table, unless INSTEAD OF triggers are used to do inserts and updates) I don't see MERGE helping here - it introduces a new view and a set of INSTEAD OF triggers, which is quite a lot of extra script, for no noticeable gain. Now if SQL Server supported updateable views in a more general fashion perhaps two of those three operations, or even all three of them, could be done with a MERGE without instead of triggers; but I haven't looked hard enough to see whether the view would in theory be updateable - I think it would be, but not sure - and anyway the chance of SQL Server [or any other SQL database] supporting updatable views a la RM2 without hopelessly severe restrictions any time in the near future seem pretty remote, the problem being that as updatability is undecidable any implementation has to compromise [it can only support a recursive subset of the recursively enumerable set of theoretically updatable views] so someone will always be ready to say "it doesn't do enough".
Tom
February 9, 2011 at 6:45 am
Tom.Thomson (2/9/2011)
Gianluca Sartori (2/9/2011)
You're looking for the MERGE statement, I guess.EDIT: clicked the wrong button.
As the two inserts and the update are on three different tables, and when the target of a MERGE statement is a view all the updateable view restrictions apply (so all the columns inserted or modified have to be in the same base table, unless INSTEAD OF triggers are used to do inserts and updates) I don't see MERGE helping here - it introduces a new view and a set of INSTEAD OF triggers, which is quite a lot of extra script, for no noticeable gain. Now if SQL Server supported updateable views in a more general fashion perhaps two of those three operations, or even all three of them, could be done with a MERGE without instead of triggers; but I haven't looked hard enough to see whether the view would in theory be updateable - I think it would be, but not sure - and anyway the chance of SQL Server [or any other SQL database] supporting updatable views a la RM2 without hopelessly severe restrictions any time in the near future seem pretty remote, the problem being that as updatability is undecidable any implementation has to compromise [it can only support a recursive subset of the recursively enumerable set of theoretically updatable views] so someone will always be ready to say "it doesn't do enough".
Whoops, you're right, Tom. I was mislead by Brandie's last post and didn't take the time to read the rest carefully.
My apologies.
-- Gianluca Sartori
February 9, 2011 at 7:19 am
So do you have any alternatives to his thoughts on how he has to process his updates / inserts? Or does he have the order correct?
February 9, 2011 at 7:39 am
I'm going to make a couple of comments:
1. If you use the DATE data type then you won't have to worry about time so a query using WHERE expiry_date <= '1/1/2011' would work fine.
2. I don't think you need the expiry_date in the users table. You can do that to denormalize a bit, but you can also just look at the last payment date in the payments table.
You could do something like this and not have to have an update statement:
CREATE TABLE users
(
userid INT NOT NULL CONSTRAINT PK_users_userid PRIMARY KEY,
username VARCHAR(20) NOT NULL CONSTRAINT UX_users_user_name UNIQUE
);
CREATE TABLE payments
(
payment_id INT NOT NULL CONSTRAINT PK_payments_payment_id PRIMARY KEY,
userid INT NOT NULL CONSTRAINT FK_payments_users FOREIGN KEY REFERENCES users(userid),
payment_amount DECIMAL(10, 4) NOT NULL,
payment_date DATE NOT NULL CONSTRAINT DF_payments_payment_date DEFAULT CURRENT_TIMESTAMP
);
CREATE VIEW users_expiry_date
AS
BEGIN
SELECT
U.userid,
U.username,
DATEADD(Year, 1, MAX(P.payment_date)) AS expiry_date
FROM
users AS U JOIN
payments AS P
ON U.userid = P.userid
GROUP BY
U.userid,
U.username
END
Unless you are talking a huge amount of data I think performance would be acceptable.
If you think you need the expiry_date in the users table then you could do a stored procedure that does the insert and update in one transaction OR use a trigger to do the update. Since you'd be using the primary key of the users table to determine which row(s) to update it should be quick either way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply