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.
| Author |
Topic |
|
cesark
Posting Yak Master
215 Posts |
 Posted - 2006-01-05 : 11:44:52
|
| Hi,I have an Offers table where I store data such as Offer_id, User_num, theDate, Product_num, Offer_title, City, … (Is a table to store published offers by users in my website). Now I want to store statistics related to visits to offers_details web page, so, visits to every published offer. The statistics I want to store are related to actions the user can do in that offers_details web page, such as number of visits, number of times users has clicked a link, number of times users has enlarged an image, etc.. My question is: Which the best place to store this kind of information? Perhaps in a separate table called Offers_Statistics, with a primary key column as identity called Offer_statistic_id and a foreign key column called Offer_number? And then, every time an offer is published is added automatically a row in that statistics table with its correspondent offer number, in order to store statistics produced by users later, if any? Suggestions and/or ideas?Thanks |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 12:30:02
|
| Definitely it should be, seperate table(s)I'd create a table for actions (ActionID & a description)a table to keep the statistics (Offer_statistic_id , [Offer#], ActionID, [Date/time used] ....) |
 |
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-01-05 : 13:17:47
|
Your idea seems very good..  Briefly I will need a complete way to track all users visits to my site, so, follow every user visit (every ASP.NET session) and store in the database some information during the session. Then, I was thinking in this general idea: (Try to adapt my idea to your idea, since I think is the correct direction)One table called ‘Sessions’ to store every user arrival to my site and some general information such as domain name, IP address, browser version, arrival time and leave time.A second table called ‘Visited_Pages’ where I would store every visited page by each session (record) of ‘Sessions’ table. And some information such as web page, arrival time and leave time.And finally a third table (one for every web page I want to collect information) where I would store for every record in ‘Visited Pages’ the information that every user generates during the visit in the page. It’s only a general idea. What do you think?Thanks |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 15:25:27
|
| IF u want to use the User details for analisys or for business purposes, how about keeping them in a different table as webUsers with fieldsUserID : may not be something assigned, but any random user will be given an ID but track him with the help of other factors like IP address .... IP address , ....Isn't it good to have staticstics tables 1. dependent on the user details (like user logs from a particular IP address this # of times, ...)2. dependent on the applications or pages or links invoked by usersBoth of these can be combined to 1 if u r not concerned about who the user is.In any case u r better off having the Actions table |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-01-05 : 16:06:04
|
| [code]IF u want to use the User details for analisys or for business purposes, how about keeping them in a different table as webUsers with fieldsUserID : may not be something assigned, but any random user will be given an ID but track him with the help of other factors like IP address .... IP address , ....[/code]I suppose you mean keep my ‘Sessions’ table and have a ‘webUsers’ table so that keep users more or less identified when they arrive to my website. Then, link ‘webUsers’ table with ‘Sessions’ table through ‘User_id’ column, and in this way I will know who is in every session. (Correct me if this is not exactly your idea)Then, maintain my suggested database design with those three tables (now four with ‘webUsers’), the last one would be what you suggested: “a table to keep the statistics (Offer_statistic_id , [Offer#], ActionID, [Date/time used] ....)” with its correspondent Actions table. This last one table should exist for every web page I want to track statistics, and decide which to fill depending on the page the user is visiting. I don’ t understand what you mean with:[code] Isn't it good to have staticstics tables 1. dependent on the user details (like user logs from a particular IP address this # of times, ...)2. dependent on the applications or pages or links invoked by users [/code] |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 16:25:41
|
quote:
Originally posted by cesark
I suppose you mean keep my ‘Sessions’ table and have a ‘webUsers’ table so that keep users more or less identified when they arrive to my website. Then, link ‘webUsers’ table with ‘Sessions’ table through ‘User_id’ column, and in this way I will know who is in every session. (Correct me if this is not exactly your idea)
Yes u r correct. and that's what I meant in my point 1. with regards to statistics.Basically u can have statistics depending on the users.eg1. User AAA visits the sites 2 times per month, BBB visits sites 3.7 times per month.eg.2 User AAA used the link ppp on 1/1/2006 at 3:14 Also u can disregard the user (if u don't want to keep track of users) and track the links / pages etc that u haveeg1. This link was clicked 4 times on 14th June 2006 at .....eg2. This image was downloaded 9 times on 16th July 2006 |
|
|
|
cesark
Posting Yak Master
215 Posts |
Posted - 2006-01-09 : 06:37:23
|
| I was thinking beyond to the scope of that little part of the statistics, and I would like to implement briefly a system to track the user activity I want to analyse. I mean, store in the DB the sessions of all my users and the actions they made during the visit. For example:A user arrives to my index web page, after 5 seconds visits web_page2, after 2 seconds clicks button_2 in this page, after 6 seconds clicks button_4 in the same page, after 4 seconds clicks link2 and goes to web_page7, and here the session expires because none action is made during 15 minutes. The information I would want to store in this example is: I know the user? : If user has the last cookie, I know who is, and he/she will be in my ‘visitors_users’ table. If doesn’ t has the last cookie, with retrieved information in my web application such as IP address, internet provider and domain name, I can check in my ‘visitors_users’ table if there is some user with these identical data, if so, I also know who is, if not I add a new user with all these associated data and I give him/her a cookie for later sessions. Whatever previous situation is, I add a row to ‘Sessions’ table. In this table I would store the Session_id key field (PK) and User_id (FK). Then, in ‘web_statistics’ table I would store the web_stat_id key field (PK), Session_id (FK), action_id field to store the action the user has made (this means have a separate table with actions defined, and the web page the action belongs to) and the action_time field to store the date/time the action occurred. So, with my example, I would store in ‘web_statistics’ table: Web_stat_id - Session_id - action_id - action_time584 - 52 - 12 - 9/01/2006 10:06:21 (Visits index page) ...596 - 52 - 5 - 9/01/2006 10:06:26 (Visits web_page2)...632 - 52 - 25 - 9/01/2006 10:06:28 (Clicks button_2 in web_page2)...640 - 52 - 9 - 9/01/2006 10:06:34 (Clicks button_4 in web_page2)...655 - 52 - 58 - 9/01/2006 10:06:38 (Clicks link2 and goes to web_page7)...657 - 52 - 15 - 9/01/2006 10:06:38 (Visits web_page7)...867 - 52 - 32 - 9/01/2006 10:06:53 (Expires session)What do you think about this system to store all the statistics along my website? One of my doubts is: With that only statistics table for all web pages (‘web_statistics’), retrieve or query the statistics will not be very slow or will have bad performance because having to deal with thousands or millions (I hope ) of records? For example, trying to query how many visits a page had in a period of time, or how many times a link was clicked in a page during the last month by a specified group of users, etc,.. ?Thank you |
|
|
|
|
|
|
|
|
Storing web statistics in the DB