How do I create a moving snapshot of pass holder information in my database

I have a membership database that I want to create an additional table in to track how our membership changes over time (on a month-by-month basis) for a full 12 months. I'm unsure about the best database design to use.

Our members have subscriptions with a start date and and end date. Each month we have a few new members, ie. the start date for their subscription falls in that month. Similarly, each month we have a few members who leave, ie. the end date for their subscription falls in that month. For the remainder of our members, they are current for the full month, ie. the start date of their subscription falls before the given month and the end date of their subscription falls after the given month.

I have this data in a table I created:

SubscriptionSnaphot
   MemberID int
   SubscriptionType varchar
   StartDate datetime
   EndDate datetime

A member can appear more than once in the data if they have more than one subscription.

I'd like to supplement this table to include an indication of the status of this member for the past 12 months. For example, let's suppose a member joined 9 months ago and held a 6 month pass and then left. Their status for the pass 12 months would be: I,I,I,N,C,C,C,C,C,E,I,I where I=Inactive, N=New, C=Current and E=Expired.

A naive design might simply add 12 columns to my table, one for each of the past 12 months and then update them using some queries.

My questions:

  • What's a good design to represent this moving snapshot
  • What are the corresponding queries to fill the snapshot data (assuming you have the subscription data as outlined above)

I'm not dealing with a large amount of data, nor do I require a fully normalised design. I'm after something that's simple to create and extract data from. I would probably regenerate this data on the first of every month for the past 12 months.

I'm using SQL Server 2008, but I'd prefer a DB agnostic solution if possible.

Answers


I would write a table that is a clone of your Membership table, but stripped of irrelevant information. I'm gonna piggy back on the aspnet_Membership table since it is fairly well know. Let's call it "asp_Membership_Audit".

I would create a table where you could put a unique datestamp (one time). Let's call it "Tageroni" (long name for "Tag") Then I would add a column to my asp_Membership_Audit table that was a FK to the PK for the Tageroni table.

Then, once a month, run a job that throws a row into the Tageroni Table. And then copy your membership table (over to aspnet_Membership_Audit table), with the Tageroni FK. You could put the date stamp in the "audit" table, but I don't like using time stamps as the unique identifier.....a int, bigint or uuid is my preference.

Then you have the data you need to generate your reports. If you come up with something "super clever" now, your need may change. But capturing the audit data completely and correctly, you can create your "reports" with your single column piped values whenever you want.

Here is the concept. My queries at the end of rudimentary at best, but as long as the data is captured, you can create the reports later.

But basically, with the Tageroni table and the clone table, you'll have a perfect snapshot of "What did my data look like on the first of the month of whatever month".......

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_Membership_Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            DROP TABLE [dbo].[aspnet_Membership_Audit]
      END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tageroni]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            DROP TABLE [dbo].[Tageroni]
      END
GO


CREATE TABLE [dbo].[Tageroni] (
      TageroniUUID [uniqueidentifier] not null default NEWSEQUENTIALID() , 
      TageroniName varchar(64) not null , 
      TageroniDateStamp datetime not null
)     
GO


ALTER TABLE dbo.Tageroni ADD CONSTRAINT PK_Tageroni_TageroniUUID
PRIMARY KEY CLUSTERED (TageroniUUID)
GO


ALTER TABLE dbo.Tageroni ADD CONSTRAINT CK_Tageroni_TageroniName_UNIQUE 
UNIQUE (TageroniName)
GO



CREATE TABLE [dbo].[aspnet_Membership_Audit](
    aspnet_Membership_Audit_UUID  [uniqueidentifier]  not null default NEWSEQUENTIALID() , 
    TageroniUUID [uniqueidentifier] NOT NULL,

    /* The 3 columns below are the User, and the "status" flags I'm interested in */
    [UserId] [uniqueidentifier] NOT NULL,
    [IsApproved] [bit] NOT NULL,
    [IsLockedOut] [bit] NOT NULL
    )
GO



ALTER TABLE dbo.aspnet_Membership_Audit ADD CONSTRAINT PK_aspnet_Membership_Audit_UUID
PRIMARY KEY CLUSTERED (aspnet_Membership_Audit_UUID)
GO

ALTER TABLE [dbo].[aspnet_Membership_Audit]  WITH CHECK ADD FOREIGN KEY([TageroniUUID])
REFERENCES [dbo].[Tageroni] ([TageroniUUID])
GO


/* Once a Month, Run something like this */


INSERT INTO dbo.Tageroni ( TageroniUUID , TageroniName , TageroniDateStamp )
select '11111111-1111-1111-1111-111111111111' , 'My First Tag, 2013' , '01/01/2013'
UNION ALL select '22222222-2222-2222-2222-222222222222' , 'My Second Tag, 2013' , '02/01/2013'
UNION ALL select '33333333-3333-3333-3333-333333333333' , 'My Third Tag, 2013' , '03/01/2013'


/* Run this on Jan 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '11111111-1111-1111-1111-111111111111' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]

/* Run this on Feb 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '22222222-2222-2222-2222-222222222222' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]

/* Run this on March 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '33333333-3333-3333-3333-333333333333' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]


GO


Select derivedJan.UserId , derivedJan.[IsApproved] as JanIsApproved , derivedFeb.[IsApproved] as FebIsApproved , derivedMarch.[IsApproved] as MarIsApproved
From
(select * from [dbo].[aspnet_Membership_Audit] where TageroniUUID = '11111111-1111-1111-1111-111111111111') derivedJan 
join
(select * from [dbo].[aspnet_Membership_Audit] where TageroniUUID = '22222222-2222-2222-2222-222222222222') derivedFeb 
on derivedJan.UserId = derivedFeb.UserId
join
(select * from [dbo].[aspnet_Membership_Audit] where TageroniUUID = '33333333-3333-3333-3333-333333333333') derivedMarch
on derivedJan.UserId = derivedMarch.UserId

EDIT-------------

Here is a "sliding" caveat............

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_Membership_Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            DROP TABLE [dbo].[aspnet_Membership_Audit]
      END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tageroni]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      BEGIN
            DROP TABLE [dbo].[Tageroni]
      END
GO


CREATE TABLE [dbo].[Tageroni] (
      TageroniUUID [uniqueidentifier] not null default NEWSEQUENTIALID() , 
      TageroniName varchar(64) not null , 
      TageroniDateStamp datetime not null ,
      TageroniMonthsIntoThePast int not null
)     
GO


ALTER TABLE dbo.Tageroni ADD CONSTRAINT PK_Tageroni_TageroniUUID
PRIMARY KEY CLUSTERED (TageroniUUID)
GO


ALTER TABLE dbo.Tageroni ADD CONSTRAINT CK_Tageroni_TageroniName_UNIQUE 
UNIQUE (TageroniName)
GO



CREATE TABLE [dbo].[aspnet_Membership_Audit](
    aspnet_Membership_Audit_UUID  [uniqueidentifier]  not null default NEWSEQUENTIALID() , 
    TageroniUUID [uniqueidentifier] NOT NULL,

    /* The 3 columns below are the User, and the "status" flags I'm interested in */
    [UserId] [uniqueidentifier] NOT NULL,
    [IsApproved] [bit] NOT NULL,
    [IsLockedOut] [bit] NOT NULL
    )
GO



ALTER TABLE dbo.aspnet_Membership_Audit ADD CONSTRAINT PK_aspnet_Membership_Audit_UUID
PRIMARY KEY CLUSTERED (aspnet_Membership_Audit_UUID)
GO

ALTER TABLE [dbo].[aspnet_Membership_Audit]  WITH CHECK ADD FOREIGN KEY([TageroniUUID])
REFERENCES [dbo].[Tageroni] ([TageroniUUID])
GO


/* Once a Month, Run something like this */
/* And adjust the TageroniMonthsIntoThePast value to be a "sliding" value */


INSERT INTO dbo.Tageroni ( TageroniUUID , TageroniName , TageroniDateStamp , TageroniMonthsIntoThePast)
select '11111111-1111-1111-1111-111111111111' , 'My First Tag, 2013' , '01/01/2013' , 4
UNION ALL select '22222222-2222-2222-2222-222222222222' , 'My Second Tag, 2013' , '02/01/2013' , 3 
UNION ALL select '33333333-3333-3333-3333-333333333333' , 'My Third Tag, 2013' , '01/01/2013' , 2


/* Run this on Jan 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '11111111-1111-1111-1111-111111111111' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]

/* Run this on Feb 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '22222222-2222-2222-2222-222222222222' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]

/* Run this on March 1, 2013 */
INSERT INTO [dbo].[aspnet_Membership_Audit](    [UserId] ,  TageroniUUID  , [IsApproved] , [IsLockedOut] )
Select '33333333-3333-3333-3333-333333333333' , UserId ,  [IsApproved] ,  [IsLockedOut] 
from [dbo].[aspnet_Membership]


GO


Select TwoMonthsAgoDerived.UserId , TwoMonthsAgoDerived.[IsApproved] as TwoMonthsOldIsApproved , ThreeMonthsAgoDerived.[IsApproved] as ThreeMonthsOldIsApproved , FourMonthsAgoDerived.[IsApproved] as FourMonthsOldIsApproved
From
(select aud.* from [dbo].[aspnet_Membership_Audit] aud join dbo.Tageroni tag on aud.TageroniUUID = tag.TageroniUUID where TageroniMonthsIntoThePast = 2 ) TwoMonthsAgoDerived 
join
(select aud.* from [dbo].[aspnet_Membership_Audit] aud join dbo.Tageroni tag on aud.TageroniUUID = tag.TageroniUUID where TageroniMonthsIntoThePast = 3) ThreeMonthsAgoDerived 
on TwoMonthsAgoDerived.UserId = ThreeMonthsAgoDerived.UserId
join (select aud.* from [dbo].[aspnet_Membership_Audit] aud join dbo.Tageroni tag on aud.TageroniUUID = tag.TageroniUUID where TageroniMonthsIntoThePast =  4) FourMonthsAgoDerived
on TwoMonthsAgoDerived.UserId = FourMonthsAgoDerived.UserId

I ended up creating a second table to hold the snapshot information in conjunction with the original table. The latter was modified to add a RecordID column to act as a primary key and a foreign key in the former. My additional (former) table looks like:

SubScriptionSnapshotData
    SubscriptionRecordID int
    Year smallint
    YearElement smallint
    ElementType varchar(15)
    Status varchar(15)

So each record in this table, points to a record from the original table and notes the moving snapshot information for it. Essentially I can record the year and month of the snapshot and the status of the subscription in the snapshot.

By using (YearElement, ElementType) instead of simply Month, I can create snapshots by month or week or whatever. For example, April is represented as (4, 'Month'). A snapfrom from week 32 could be represented as (32, 'Week'). Status is simply 'New', 'Expired' or 'Current'.


Need Your Help

Windows button on windows tablet in custom shell

windows windows-8 tablet autohotkey

I am developing a custom shell for a windows 8 tablet. I would like to intercept the tablets windows button press event. I am not sure how to do so. I am currently working in c#, but would be wi...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.