CODEWITHSUNDEEP
javamysqldatabase
java seeker
java seeker

Reputation: 1266

Database design for webpage tracker

I want to design a page tracker database table, but I am facing few issues with it.

create table pageTracker(
 ID bigint(20) NOT NULL,
 TrackerID bigint(20) NOT NULL,
 SessionID varchar(100) NOT NULL,
 pageViews bigint,
 pageVisits bigint,
 primary key(ID)
);

If I update pageviews and pageVisits corresponding to specific SessionID I can not query pageViews and pageVisits within specific time interval.

create table pageTracker(
     ID bigint(20) NOT NULL,
     TrackerID bigint(20) NOT NULL,
     SessionID varchar(100) NOT NULL,
     pageViews bigint,
     pageVisits bigint,
     time TimeStamp,
     primary key(ID)
    );

But if I add extra column time, if I want to insert each pageViews and pageVisits as new entry for specific time it creates huge number of entry in the table.

Is there any efficient way to do it?

Upvotes: 0

Views: 64

Answers (1)

Nikhil Joshi
Nikhil Joshi

Reputation: 817

I am assuming that you want to update pageViews and pageVisits everytime against a SessionID. In this case first insert will have say:

Session ID = 23R4E11, pageViews = 1, pageVisits = 1

Now if same user revisits same page, you will update existing row as:

Session ID = 23R4E11, pageViews = 2, pageVisits = 1

In this case to maintain all the updates, you can create one more table called as pageTrackerHistory and then write trigger which can insert entry in pageTrackerHistory table whenever update is made on pageTracker table.

By doing this your operational table pageTracker contains minimal rows and pageTrackerHistory table contains huge audit records.

Hope this will give you some direction. :-)

Upvotes: 1

Related Questions