I would like to understand why CI's session table structure has these three primary keys: session_id, ip_address and user_agent.
CREATE TABLE IF NOT EXISTS `ci_sessions` (
session_id varchar(40) DEFAULT '0' NOT NULL,
ip_address varchar(45) DEFAULT '0' NOT NULL,
user_agent varchar(120) NOT NULL,
last_activity int(10) unsigned DEFAULT 0 NOT NULL,
user_data text NOT NULL,
PRIMARY KEY (session_id, ip_address, user_agent),
KEY `last_activity_idx` (`last_activity`)
);
Please explain the most you can, also, I would like to hear suggestions to improve this structure. Why are ip_address and user_agent primary_keys, not just indexes? What's the difference?
Another info, this table adds a row to every user's access to the system, so, it is very bloated.
Edit: Another question that come to mind. Why would I care about user agent match?
The idea here is that each session will be unique. How does it identify a session? By the three values in the primary key: session_id, ip_address, and user_agent.
If you think about it, this makes sense:
session_id changes, then (obviously) you're dealing with a different (new) session.ip_addess changes, then somebody's logging in from a different PC - this will be a new session.user_agent value changes, then somebody's using a different browser - again, this will be a new session.So imagine that only the session_id is the primary key: changing either ip_address or user_agent would simply update the existing row for the session_id. If that were the case, knowing only the session_id would make it possible for me to continue the same session on another PC or with a different browser, which might be a security concern.
You also wrote "this table adds a row to every user's access to the system, so, it is very bloated". I'm not sure if you mean every time user A accesses the system it adds a row (which is false on my application, I just tested it) or if you mean it adds a row for each user accessing the system (which is true, and the way it's supposed to work - each user using the system has a session). Maybe you could clarify that last comment.
"primary keys" is an oxymoron. A table cannot ever have more than one "primary key". And as written up, there is only one primary key - it's just a COMPOSITE key that contains 3 separate fields.
That means
(42, 127.0.0.1, "Chrome")
(42, 127.0.0.1, "Firefox")
are two entirely different sessions as far as CI is concerned, even though the IP and session ID numbers are duplicates. The 3-way tuple is unique, but individual components can be duplicated.