How to Implement Landing Page Hit Tracking in ASP.NET (Part 2)

In Part 1 of this series, we created our database and two tables. In Part 2, we'll create the classes and wire them up to a page...

Step 2: Create/open website project and add references

Step 1: Create the tables

Step 2: Create/open website project and add references

For Step 2, you can either open one of your existing website projects (VB or C# - I'll use C# for this post) or you can create a new one. Either way you need to reference the Microsoft Enterprise Library to use the code in this series. Make references to:

• Microsoft.Practices.EnterpriseLibrary.Common
• Microsoft.Practices.EnterpriseLibrary.Data

Step 3: Create a test page

Create a test page and compile your project - just to make sure everything works... It's a good idea to compile periodically - just to make sure you have a good clean foundation for the next step. Go ahead and test the page as well and fix any errors reported.

Step 4: Create the LandingPageHit table and supporting objects

Now that you have your LandingPage table and your User table, you need to understand how these relate to one another:

• One LandingPage can be seen by many Users
• One User can be associated with at most one LandingPage

Let's take our landing page: http://www.learnitfirst.com/VideoTutorials/E-Learning/3/Sql-Server-Training-Videobooks.aspx?an=TechUrbia&source=blog. If 2,000 people hit that page, do we want that page listed 2,000 times in our database?

Noooooooo - that would be a terrible mistake! We would have lots and lots of duplicate data - the bane of Data Modelers everywhere. So the solution is to track that unique AbsoluteUri only once and associated "hits" in a separate table:

CREATE TABLE dbo.LandingPageHit(

    LandingPageHitId uniqueidentifier NOT NULL DEFAULT (newid()),

    LandingPageId int NOT NULL 

        CONSTRAINT FK_LandingPageHit_LandingPage 

        FOREIGN KEY(LandingPageId)

        REFERENCES dbo.LandingPage (LandingPageId),

    AddDate datetime NOT NULL DEFAULT GETDATE(),

    UserId int NULL

        CONSTRAINT FK_LandingPageHit_Users 

        FOREIGN KEY(UserId)

        REFERENCES dbo.Users (UserId),

    IpAddress varchar(128) NULL,

    ReferringUrl nvarchar(1024) NULL,

    CONSTRAINT PK_LandingPageHit PRIMARY KEY (LandingPageHitId)

)

Ignore the "uniqueidentifier" bit for a few moments and, instead, let's focus on the relationships between our three tables:

The logic of this approach is:

1. We only audit unauthenticated hits
2. When an unauthenticated user hits a page for the first time, we ask the question, "Does an entry already exist in the "LandingPage" table for this AbsoluteUri?"
1. If "Yes", then retrieve that LandingPageId and track this hit in the LandingPageHit table
2. If "No", add this as a LandingPage, retrieve the system-generated LandingPageId, and then track this hit in the LandingPageHit table
3. Return the newly-generated LandingPageHitId to the web app

The AbsoluteUri is the real "natural" key to the LandingPage table - we only want it listed once. To force this at the database level, create a unique index:

CREATE UNIQUE INDEX [LandingPage.AbsoluteUri must be unique] ON LandingPage(AbsoluteUri)

Now, if anyone tries to insert multiple rows with the same AbsoluteUri, it will fail. No dupes! From now on, there is one record in the LandingPage table and it's (hopefully) many "hits" are stored in the LandingPageHit table.

Design Decisions: The LandingPageHit table

Remember that we are working with unauthenticated users - people who, most likely, do not have an account on our system yet. We want to track which page they enter on and, if they later become a "User", then we want to be able to associate that user account with a specific landing page. How do we do this?

We have a column called "UserId" in the LandingPageHit table. Important info:

• This column is nullable because, when an unauthenticated user hits the page, we have no associated UserId for them.
• A user receives a UserId when they register for an account. That UserId is always present and available to us in ASP.NET once they are authenticated
• This column is a foreign key (child) to the Users.UserId column (the parent)

When an unauthenticated hit comes in, we log the hit with a NULL UserId. Key concept: Once the user creates an account, we circle back and UPDATE this record to be the new UserId.

On to Part 3!