Do I need a unique identifier column or identity? Or another type.. 1

[ericnet]

I have a table called ‘Sessions’ where I store some information about every client that visits my website. In that table I have a column called ‘Client_cookie’ where I want to store an identifier that must be unique for every client (but not for every new row. Since a client can have many sessions), and this column isn’t the PK. How can I achieve this?

This is the table:

-Sessions Table-
Session_id (bigint) IDENTITY (1, 1) NOT NULL
User_num (if user logs in) Is the user id in the DB for registered users
Client_cookie (permanent cookie that identifies the machine)
Session_type (Registered or Anonymous) Registered when user logs in during the session, and Anonymous if user doesn’ t log in
IP_address
User_Agent
Is_Carwler (‘True’ or ‘False’)
Browser_name (i.e. IE)
Browser_version (i.e. 6.0)
Javascript_enabled (‘True’ or ‘False’)
Cookies_enabled (‘True’ or ‘False’)
UrlReferrer


By the way, which is the difference between the Primary Key and the Identity? Is the same concept?

Thank you,
Cesar

 

[r937]

the unique identifier for every client could be a GUID -- use the NEWID() function

a primary key is often an identity column, but an identity column does not need to be the primary key -- no, they are not the same concept

r937.com | rudy.ca

 

[ericnet]

Ok cool!, then the performance would be:

Only use newid() function when @known_client received parameter is null. If @known_client received parameter value is not null, then I will store that value in the ‘Client_cookie’ column (uniqueidentifier). The @known_client parameter value is set in the web application, and is the current value of the permanent client cookie called “client”, sent by the client to web app. That cookie’s value was initially generated in the DB by newid() function, then was sent to web application so that store that value in the cookie, and placed in client’s browser to use it in future sessions, and so on..

What do you think?

Thank you

 

[r937]

that sounds okay to me, but i've never used cookies quite that way

you mentioned that a user might log in -- why wouldn't you use the user_id for the same purpose?

r937.com | rudy.ca

 

[ericnet]

If user logs in during the whole visit, I store user id in User_num column, and also the unique identifier contained in the cookie. But if the user doesn’ t log in during the visit, null value is stored in user_num column because I don’t know if the visit was of the same registered user or another user of the same machine. But storing the unique identifier value contained in the client cookie I know if the visit comes from the same machine of the previous visit or not.

If I have understood it well, you suggested store in the cookie the user id when logs in, and use only User_num column as the ‘unique identifier’ for every client. If I do this, a user may log in from a machine only once, and habitually use another machine, then I can receive visits from both machines that aren’t from the same registered user.
This is why I use ‘Client_cookie’ column, to know if the visit comes from the same machine or not. With User_num column (nullable) I know for a specified machine which visits are logged in (and who are) and which visits are ‘anonymous’.

This makes sense for you?

 

[r937]

yes, it sounds pretty good, actually



r937.com | rudy.ca

 

[ericnet]

Fine!, thank you for your aid and your opinion