This small project is looking for a suggestion for the best way of implementing a scenario in database.
I have a asp.net C# website. I want to trace the movement of the user from page to page and store it in my SQL database. Currently I am creating a session variable which will be appending the name of the each page, every time a user browse through my site. Refer the following.
Say I have 5 pages.
If the user browse in the following sequence, 1, 3, 4, 2, 3, 5, my session variable looks like the following.
String usermovement = "user_donald->mypage_1->mypage_3->mypage_4->mypage_2->mypage_3->mypage_5
Once the session is expiring, this session variable is stored SQL database as a single string of text. Later when I want to trace the flow of user visited page, I read the database entry and split the sting based on the "->"
Even though it works, it is not the efficient way of storage in sql because it is hard for querying something of following sort.
select all users who moved from 'mypage_3 to mypage_4'.
With the current implemetation, I have to read the whole database with 100,000+ users string, parse it, order it and analyse it, which take a really long time.
Given this situation, what is the best way of achieving it? what is the right architecture to implement?
Thanks in advance.