February 9, 2011 at 8:20 am
I'd go with Jack's design. Don't bother with denormalizing the expiration or last payment date into the users table. Just query the payments table to get that.
If you need to significantly improve performance on that, partitioning the payments table based on the age of the payment would accomplish that quite well, or use an indexed view on the last payment for each user ID and query that instead. However, if there's an index on user ID and payment date, neither of those is likely to be needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 10:41 am
GSquared (2/9/2011)
I'd go with Jack's design. Don't bother with denormalizing the expiration or last payment date into the users table. Just query the payments table to get that.If you need to significantly improve performance on that, partitioning the payments table based on the age of the payment would accomplish that quite well, or use an indexed view on the last payment for each user ID and query that instead. However, if there's an index on user ID and payment date, neither of those is likely to be needed.
I would be inclined to think that that index on user ID and payment date would be a good thing. And maybe have an expiry date function rather than a view, depending on how the application works.
Tom
February 9, 2011 at 10:55 am
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.
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
February 9, 2011 at 11:51 am
Jack Corbett (2/9/2011)
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.
Then that's something we have in common - I don't like raw SQL coming in from an application so always want SPs.
Of course sometimes views can be useful even so - if there's a view that half a dozen SPs use instead of reading 3 tables that saves some code.
Tom
February 9, 2011 at 12:14 pm
Jack Corbett (2/9/2011)
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.
As a performance tuning item, an indexed view, called from a proc, can have advantages over querying the table directly. That's what I meant by using an indexed view, not directly calling one from the application.
SQL 2008 filtered indexes accomplish pretty much the same thing, of course.
The point was that performance will almost certainly not be a problem if the right options are used on accessing the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 12:30 pm
GSquared (2/9/2011)
Jack Corbett (2/9/2011)
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.As a performance tuning item, an indexed view, called from a proc, can have advantages over querying the table directly. That's what I meant by using an indexed view, not directly calling one from the application.
SQL 2008 filtered indexes accomplish pretty much the same thing, of course.
The point was that performance will almost certainly not be a problem if the right options are used on accessing the data.
Based on my understanding you can't use the MAX() function in an indexed view.
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
February 9, 2011 at 6:09 pm
Jack,
You are awesome. And thanks to all other contributors. Great information and i now have a clear direction to go along with efficiency in how to structure the design and processes.
Much better idea to get rid of the Expiry date field and just work off the latest successful payment date via an Indexed View. This saves work and allows an efficient way of authentication from my front end and at the same time allows me to display full payment history to the user.
Great work and excellent forum discussion!
Thanks.
February 10, 2011 at 6:02 am
Jack Corbett (2/9/2011)
GSquared (2/9/2011)
Jack Corbett (2/9/2011)
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.As a performance tuning item, an indexed view, called from a proc, can have advantages over querying the table directly. That's what I meant by using an indexed view, not directly calling one from the application.
SQL 2008 filtered indexes accomplish pretty much the same thing, of course.
The point was that performance will almost certainly not be a problem if the right options are used on accessing the data.
Based on my understanding you can't use the MAX() function in an indexed view.
You are correct and it has not changed in SQL 2k8.
I looked it up yesterday. Tried to reply yesterday, but I lost the ability to properly connect to the SSC website for some reason.
EDIT: Glad we could help you out bkirk.
February 11, 2011 at 6:45 am
Jack Corbett (2/9/2011)
GSquared (2/9/2011)
Jack Corbett (2/9/2011)
I probably wouldn't use a view because I like to use SP's for all data access so the query would just go in the SP as is, but I know some people like views for this type of thing.As a performance tuning item, an indexed view, called from a proc, can have advantages over querying the table directly. That's what I meant by using an indexed view, not directly calling one from the application.
SQL 2008 filtered indexes accomplish pretty much the same thing, of course.
The point was that performance will almost certainly not be a problem if the right options are used on accessing the data.
Based on my understanding you can't use the MAX() function in an indexed view.
You're right. I forgot that, while they can use a bunch of the aggregate functions, they can't use Min or Max.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 3:32 pm
my 2 cents.... Jack's idea of parent/child user/payment is the way I'd go. This will retain payment history which someone is sure to ask for. however, you still might want the expire date for the reason that people will most likely pay before the actual expire date. You don't want to be calculating the expire date as last payment date + 12 months, you want to calculate either expire date + 12 months, or for expired accounts, current date + 12 months.
February 17, 2011 at 7:57 am
Uripedes Pants (2/11/2011)
my 2 cents.... Jack's idea of parent/child user/payment is the way I'd go. This will retain payment history which someone is sure to ask for. however, you still might want the expire date for the reason that people will most likely pay before the actual expire date. You don't want to be calculating the expire date as last payment date + 12 months, you want to calculate either expire date + 12 months, or for expired accounts, current date + 12 months.
Actually, I'd have a separate membership table. Customer ID, Payment ID, Start Date, Duration.
That allows you to get membership history (Bob's been a member since the stone age, but Doug just signed up last year), and can plot out gaps (Sue had a membership in 2009 that lapsed in October, and signed up for a new membership in Jan 2010), and so on. Those are things you'll end up wanting to report against. Plus, in most companies, someone in sales/marketing will someday have the idea that a discount should be offered to people who have had continuous memberships for X years, and so on.
Also allows for business-rule changes on memberships, if someone wants to allow for 5-year or 6-month versions, instead of just 12-month memberships being universal.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply