Help figuring out this logic.

And this is why.

>>When the user logs in the second time, LAST_LOGIN_TIME and LAST_LOGIN_TIME_2(time before
              current login)


That is denormalization that is not good for the design and presents problems in itself.

try something like this...

select count(*) from table_name where last_login_time >= start time frame and last_login_time_2 <= end time frame

                                              

1:

Hello Sir,

                Thanks for your input. So you mean to say, instead of keep updating the same row again and again and using some sort of Counter.

               I should, create a record every time, user logs in?

Hi Kadaba,

                 Just this query would do?

Yes. Because there are other requirement that might come down the road, like tracking if a user's IP address changes, or tracking login history in a granular detail that your current schema cannot manage. You are using a single record as a "2 - login rotating queue". Don't do that. Use each login as a new record, just like Oracle auditing does it.

I've designed lots of these systems where auditing is a concern. Trust me, it will make your life easier.

So if you take my advice and fix the table:

>> "How many users logged on to the website in a given frame of time (supllied from Web Interface)".


Would be:

select count(distinct(user_id)) from tracking where login_time between ? and ?

Hello Sir,

              Experience really counts a lot :-)
              Even though I didnt mention, you already picked the requirement. They have a requirement of Histroy as you mentioned. I have to maintain a history back 12 months

The point mrjoltcola is trying to make is that

say use with id 1 logs in
you will update the last_login_time
and when he logs in you will update the last_login_time_2

The second time he logs in you will be updating the same row and you will lose the count..

Sure,
          I will follow your advice,

         Can you do it step by step?  please :)

        First step.. you want me to drop LAST_LOGIN_TIME_2 column?

@ Kadaba,

                Thanks for clearing it up :-)
     
               Actually one of my colleague suggested me to use 2 tables:

             1- First one that I posted above.
             2- Use another table, that would take a snapshot from this table and do processing on number of counts.

This is my present java code:

package com.si.dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.si.dao.*;
 
public class UserData {	
 
	//prod
	String url = "jdbc:oracle:thin:@abc.xyz.com:1522:tl002";
	String user = "user1";
	String password = "pass1";
	String driver = "oracle.jdbc.driver.OracleDriver";
 
 
	 Connection conn= null;
     PreparedStatement pstmt = null;
     Statement stmt = null;
 	 ResultSet rs = null;
	
     DbConn db = null;
	 
	
	
	public String getUserAccessType(String userId) {
		
		String accessType = null;
		
		String qry = "SELECT ACCESS_TYPE FROM SIWEB_USERS WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
		
		String updateQry_2 = "update SIWEB_USERS set LAST_LOGIN_TIME_2 = LAST_LOGIN_TIME WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
		String updateQry = "update SIWEB_USERS set LAST_LOGIN_TIME = sysdate WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
	
		
		System.out.println("qry:" + qry);
		
		try {
			 db = new DbConn();
			 conn = db.getConnection(url, user, password);
				
			stmt = conn.createStatement();
			rs = stmt.executeQuery(qry);
						
			if (rs.next()) {				
				accessType = rs.getString(1);				
			}
			
			//update userinfo added on 083109
			int r = stmt.executeUpdate(updateQry_2);			
			int t = stmt.executeUpdate(updateQry);
 
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
		    	db.closeResources(stmt, conn);
							
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return accessType;
	}
	
	//new method to check valid user
	
   public boolean isValidUser(String userId) {
		
		boolean valid = false;
		
		String qry = "select ID from SIWEB_USERS where lower(ID) = '" + userId.toLowerCase() + "'";
		
	
		
		try {
		    stmt = conn.createStatement();
			rs = stmt.executeQuery(qry);
						
			if (rs.next()) {				
				valid = true;				
			}
 
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
		return valid;
	}
 
	public static void main(String[] args) {
			
		UserData bd = new UserData();		
		//String user_access_type = bd.getUserAccessType("PP1592");
		String user_access_type = bd.getUserAccessType("at7138");
		System.out.println(">>>user access type=" + user_access_type);		
	}
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:

Hi hielo,

                Thanks for guiding me :-)

                Can you please tell me "why I need a separate table?"

>> Actually one of my colleague suggested me to use 2 tables:
Exactly!

>>1- First one that I posted above.
Not EXACTLY like that. That table does NOT need LAST_LOGIN_TIME and LAST_LOGIN_TIME_2

You only need LAST_LOGIN_TIME on the SECOND table.

Read my previous comment for the second table

Hi Kadaba,

                    there is no provision for logout. I guess the default set by our organization is 30mins session timeout.

                   So I can only capture login

>>Can you please tell me "why I need a separate table?"
your requirement is "...a given frame of time". I explained this issue on my first post. Read again - carefully.

Seniors,

               I got the basic logic and its very clear in my mind now. BUT:

               I am confused which approach to follow, like mrjoltcola said, only oen table would do everything.

              Hielo said, I need two tables.

              So please correct me on this one:

             First Table:

            ID, FNAME, LNAME, ACCESS_TYPE, LOGIN TIME

            Second Table:

            Not sure what to put in for second one.
     

oh alright no session listener.. please go ahead with what hielo suggested.

@ hielo,

              You are right about the requirement, but isnt the first approach will fix everything: where we are inserting a record for every login?


              Please forgive me if I am stuck somewhere
             

hielo and I are recommending the same thing. His 2 table recommendation is essentially to track the last login time of the user in an efficient manner, without the need to run a datewarehouse type query to find the last login. All other requirements can be satisfied by the tracking table.

You might put LAST_LOGIN_TIME into your actual USERS table.

Oh now I got it.

Sorry hielo and mrjoltcola :)

2 table approach will make the application little more modular with simple queries?

So table 1:

ID, FNAME, LNAME, ACCESS_TYPE, LAST_LOGIN_TIME

what would go under Table 2?

and then I need to a procedure to update the second table

A typical implementation in my web apps follows something like this:

procedure loginUser(userId integer) is
declare
  loginTime timestamp := systimestamp;
begin

update USERS set last_login_time = loginTime where user_id = userId;
insert into access_log(user_id, login_time) values(userId, loginTime);
end;
/

Update, I edited my post above as I caught a typo right after posting.

Hi charles,

                Thanks for the pointer. These folks dont really care about session. Its a very basic app.

@ hielo, mrjoltcola, kadaba, Charles,

           Thank you all for your input. I will start with this new table structure now,(Of course you will see tons of questions on that now :-) ), one last help on this stuff:

         There is a part in my team who strongly believes that Using 2 table approach is complete waste, when just 1 table can do everything.
           Can you please provide my some pointers for this argument against them :-) lol

>>There is a part in my team who strongly believes that Using 2 table approach is complete waste, when just 1 table can do everything.
Then perhaps that part of your team is not understanding/interpreting "...a given frame of time" the same way I do. With my approach you would be able to find answer the following questions:
How many times did Joe Login Between Jan 1 and Mar 1?
How many times did Joe Login Between Jan 1 and Jun 1?
How many times did Joe Login Between Jul 1 and Oct 1?
How many times has Joe logged int?
When was the last time Joe Logged in?

With the one table approach you will just be able to answer:
How many times has Joe logged int?
When was the last time Joe Logged in?

Thanks hielo. I will open other questions pertaining to this development. please provide your pointers.

Or:

Where did Joe login from?
Did Joe change IP addresses?
Has Joe logged in from multiple places at the same time?

These are security concerns for auditing tables.

As the "multiple" tables, I think you may be misunderstanding us. Noone is saying you need 2 separate "tracker" tables. A single audit tracker table is sufficient to answer ALL questions above. But adding also a LAST_LOGIN_TIME to the USER table also helps in some queries to skip scanning the audit table for certain things. It makes finding the last login time for a user efficient, because it is in the USER record, which is accessed already and probably pulled into session.

Thank you mrjoltcola,

                 As always you and other experts have always been great help for me here. I have bugging you with dumb questions for an year now :-)

                and Yes you are right, I am confused over 2 tables.

                Sorry :-)

                So Tracker table will have all the columns needed. Correct? (My table already has that mostly, right?, and I can drop last_login_time_2 field from that)

               Now the user table will be populated using a procedure? Right?

               and this table will have ID, Name, Last login Time and the Count. Correct?

             uffff... Sorry for all this trouble. I feel bad

Well I wouldn't just drop the last_login_time2 column, I would rename last_login_time to login_time, so it is clear of intent.

PL/SQL procedure, Java, Perl, whatever, doesn't matter, as long as it is correct. A procedure is a good idea.

Thank you Sir.

Thanks to all of you :)

I will start with it now,,,, :)

Hi Folks,

              sorry for stretching this question for this long. I will close it now, and maybe open a new one.

              So I created these two tables. Is it fine as the first step?

Rather than USER_SNAPSHOT I'd go with USER_LOGIN_HISTORY or SIWEB_USER_LOGINS, something like that. And again, LAST_LOGIN is the wrong name for the field in the history table. Just call it LOGIN_DATE. It is not the last login, it is just a login for that specific time. The "last login" will be the max in that table, or will be LAST_LOGIN from the USER table.

Also, no need to have FNAME and LNAME in the history table. You get that by joining to the user table.

>>Rather than USER_SNAPSHOT I'd go with USER_LOGIN_HISTORY
I agree.

>>Also, no need to have FNAME and LNAME in the history table
Again, I agree. Just to clarify, he is referring to USER_LOGIN_HISTORY.

Additionally, in USER_LOGIN_HISTORY I would get rid of the COUNT column. You can always execute a query for a particular user/ID to get that number/value. So you should just have:

USER_LOGIN_HISTORY
  ID
  LOGIN_DATE

Oh ok... Thank you sir :-)

Will do the necessary modifications as you suggested. I will read on procedures tonight, and try to write one to populate the HISTORY table.
Will post when I get stuck.. again thanks a ton. :)

Hi hielo,

            Sorry I didnt see your comment before posting.

            So I got rid of FNAME, LNAME and COUNT.

            and Now SIWEB_LoGIN_HISTORY has:

            ID
            LOGIN DATE

OK, now go complete your project :)

Thanks :-)

  Will close this question soon, and I really really appreciate all the inputs you folks put in

folks,

       I will close this question...

       Posted another question for step 2 of the problem here :)

     http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_24865074.html