DB Design / Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DB Design / Query help

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-27 : 03:27:28

Hi,

I'll try to make this short but theres alot to go over :) - I've been working on this for quite sometime becuase I want to make sure I get it perfect as messing up could create quite alot of problems. What I'm attempting to come up with is the perfect solution for a membership level system for the site I am working on that allows users to purchase different levels of access with either recurring billing or just one time billing. I have what I think is a decent solution so far, but I believe its presenting some problems when I try to attempt certain queries on it.

Here are the tables in question.

tblMemberships

   
purchasedFor purchasedBy datestart dateend membershiptype 

219188       219188      2004-08-26  2006-01-01   20
219190       219190      2004-08-26  2006-01-01   20
219189       219189      2004-08-26  2006-01-01   20
219191       219191      2004-08-26  2006-01-01   30
219187       219187      2004-08-26  2006-01-01   20

I insert a row for each user in the user Table into this table when they purchase a membership. They also have a default lower access membership. The login SPROC selects the highest active account and sets their access level to that.

Users can subscribe to different access levels for memberships as well as different lengths of time. I got some good help on design on this thread [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21641[/url]. I was debating on how to store the enddate of a users membership.

The system I have put in place with the previous design is working so far, but the problem lies in some queries I am trying to run.

The query below (BOTTOM) selects all the accounts that have signed up for their respective memberships. The thing it does not distinguish against is how long the membership was purchased for. Currently there is 1 month, 3 month, and 24 month and I don't want them grouped all the same.

I think computing the date difference would get too ugly.

What Im thinking is, if I broke out another table with just and put just the identity in tblmemberships instead of the access level.

identity / membershiplevel / durationmonths / durationdays

This would add a few joins, but I think it would make it easier to query? Also I dont know if it makes sense to store durationdays and months? Perhaps just days?

Any tips GREATLY appreciated. I hope my description is pretty clear I spent an hour typing it :) Please let me know if theres anything I can make more clear.

Thanks again everyone
mike123

ALTER PROCEDURE dbo.select_AffiliateReports_All
(
@dateStart smalldatetime,
@dateEnd smalldatetime
)

AS SET NOCOUNT ON

select affilid,
sum(case when membershiptype = 0 then total else 0 end) as FreeSales,
sum(case when membershiptype = 10 then total else 0 end) as Basic,
sum(case when membershiptype = 20 then total else 0 end) as GrandFathered,
sum(case when membershiptype = 30 then total else 0 end) as Premium
from
(select isnull(ud.affilID, 0) as affilid,
isnull(m.MembershipType, 0) as membershiptype,
count(isnull(ud.affilID, 0)) as total
FROM tblUserDetails ud

LEFT JOIN tblMemberShips m ON ud.userID = m.purchasedFor

--WHERE m.DateStart > @dateStart AND m.dateStart < @dateEnd

GROUP BY IsNull(ud.affilID, 0), m.MembershipType) as tbla
group by affilid
order by affilid

GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-27 : 22:17:12

Define a month. Is this a 30 day slot of time for example, or is it from 08/01/04 to 09/01/04 for example? Will you have cases where they buy 30,60,90 days in stead of 1,3,6,24 months, etc? If not, you don't need to include the days in the other table, you just need to include the number of months. You can then use Datediff(mm,dateStart,@MembershipLength) for example to get the expiration date. It's good for all kinds of easy calculations like that. You need to decide what your business model needs to support long-term though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-28 : 19:06:45

Hi Derrick,

I will be only selling memberships by the month. (1 month, 3 months, 12 months etc..) My reason behind thinking about having a days column as well was for situations such as an unhappy customer. I would like to be able to have the option to give them a few free days or for other unexpected situations, basically just to have some flexibility.

What do you think about my querying problem? Do you think I should break out the table ? Does it make sense?

Thanks again for your help :)

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-30 : 03:26:25

anyone elses help is greatly appreciated.

really looking forword to being able to solve this

thanks again guys
mike123

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-30 : 07:03:50

Hi,

Consider this simplistic solution:
Add a computed column that calcs the datediff in months.
When you query you can filter on this column to distinguish how long the membership was purchased for.

create table #t(
	purchasedFor int,
	purchasedBy int,
	datestart datetime,
	dateend datetime,
	membershiptype int,
	duration_months as datediff(month,datestart,dateend) )

insert #t(purchasedFor, purchasedBy, datestart, dateend, membershiptype)
select 219188, 219188, '2004-08-26', '2006-01-01', 20 union all
select 219190, 219190, '2004-08-26', '2006-01-01', 20 union all
select 219189, 219189, '2004-08-26', '2006-01-01', 20 union all
select 219191, 219191, '2004-08-26', '2006-01-01', 30 union all
select 219187, 219187, '2004-08-26', '2006-01-01', 20

select * from #t

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-30 : 07:58:42

You could also have scheduled_date_end and date_end. This would allow you to extend on special circumstances. Be default, the two dates would match. You could extend the date_end though whenever you wanted to. That would also be nice for historic tracking purposes.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-30 : 17:06:57

RockMoose - I tried it out..looks interesting .. and it works great except for when I have incosistent data suchs as

select 219190, 219190, '2004-08-26', '2006-01-01', 20 union all
select 219189, 219189, '2004-08-26', '2006-01-01', 20 union all
select 219191, 219191, '2004-08-26', '2006-01-01', 30 union all
select 219191, 219191, '2004-08-26', '2006-01-02', 30 union all

the months returned column isnt very accurate as it doesnt account for the days. perhaps if I incorporated derricks scheduled_date_end and date_end idea it would work better..

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-30 : 17:18:57

quote:
Originally posted by derrickleggett

You could also have scheduled_date_end and date_end. This would allow you to extend on special circumstances. Be default, the two dates would match. You could extend the date_end though whenever you wanted to. That would also be nice for historic tracking purposes.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Hi Derrick,

I like your idea. I was trying to accomplish this with a "extraDays" int column but I think a date column may be better. I think its basically the same effect tho.

What I am most up in the air about is the table design, whether or not to make it 2 tables. If I made it one table I think it would be easier to query?

Instead of having

sum(case when membershiptype = 0 then total else 0 end) as FreeSales,
sum(case when membershiptype = 10 then total else 0 end) as Basic,
sum(case when membershiptype = 20 then total else 0 end) as GrandFathered,
sum(case when membershiptype = 30 then total else 0 end) as Premium

which does not accomodate for the duration of the membership ( I need to monitor by duration of premium account, not just the fact a premium was sold)

I could have a identity for each membership duration and accesslevel. Something like this:

sum(case when membershipID = 1 then total else 0 end) as FreeSales,
sum(case when membershipID = 2 then total else 0 end) as Basic,
sum(case when membershipID = 3 then total else 0 end) as GrandFathered,
sum(case when membershipID = 4 then total else 0 end) as Premium_1month,
sum(case when membershipID = 5 then total else 0 end) as Premium_1year

What do you think of this idea? Does this seem like the obvious best solution?

Thanks again for your help.

mike123

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-30 : 17:58:07

One or Two tables ?

This is my take:
Since the membershiplevels are a defined set of levels with fixed durations,
and this set is not dependent on the actual memberships.
Normalization dictates 2 tables.

Furthermore I would prefer to store the "extraDays" as a column in membership table not the date_end,
What is the information that you track: extra_days given?, or the new end_date ?.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources