I'm following solution #1 of this answer. After a while, there will be some redundant rows in the table. Suppose this table:
+------+------------------+
| user | cookie |
+------+------------------+
| 1 | ojer0f934mf2... |
| 2 | ko4398f43043... |
| 2 | 34fjkg3j438t... |
| 3 | 0243hfd348i4... |
+------+------------------+
when user 1 removes his browser's cookies, still this row exists in the database:
| 1 | ojer0f934mf2... |
And when he opens my website, he needs to log in again. So here is the table:
+------+------------------+
| user | cookie |
+------+------------------+
| 1 | ojer0f934mf2... | -- now this row is useless anymore
| 2 | ko4398f43043... |
| 2 | 34fjkg3j438t... |
| 3 | 0243hfd348i4... |
| 1 | 0243hfd348i4... |
+------+------------------+
Surely in future, the number of such useless rows will increase. Well how can I manage it? Actually I can create an EVENT to clean that table up (per day) by removing redundant rows. But here is the problem: How can I detect them? How can I specify a row is useless?
EDIT: I can add a new column and store the timestamp, and then remove all old-timestamp rows by an EVENT (or a cron jobs). But that isn't a perfect solution. I like a cookie be valid until user signs out (so I don't like any limitation (or expire time) for cookies).
Now I'm looking for a solution to determine redundant rows (not old rows).
When a user logs out, explicitly delete the row in the table that corresponds to their current cookie.
For security you should be storing a random value within the cookie, at a secure hash of the cookie within the database tables (e.g. SHA-2).
For dealing with users that no longer use a particular browser, store another field for Last Used Date. Update this date as soon as you detect the cookie for the row being used to access your application.
Then you will want a scheduled task to run every so often that cleans up sessions that have not been recently used.
Add another column with a timestamp. Then run a regular cron job to delete any rows with a timestamp older than a certain threshold (say, 1 day for instance).
You can either put in a timestamp and update it every time you see that user, which means they get "logged out" x amount of time after they last used the site (but that means more database transfer); or you can just put in a timestamp when you create the session the first time and never touch it again, which will "log them out" x amount of time after they last logged in (less convenient for the user perhaps, but it means less database transfer).
Create code that checks for the user already existing in the cookie table.
If the user id exists in the user column - just update it with the new cookie information. If the user id doesn't exist, create a new record.
Or you can check for existing records, delete them, and then insert the new one.
It's not clear what redundant means in this case. There's no way to distinguish between these two rows:
+------+------------------+
| user | cookie |
+------+------------------+
| 1 | ojer0f934mf2... | -- now this row is useless anymore
| 1 | 0243hfd348i4... |
+------+------------------+
If you add a column to make the first row identifiable, then the problem solves itself: before each insert, delete the row it will supersede. No need of cron; at worst each user will have one leftover row in the database that will be removed on next use.
I also recommend a primary key to enforce the no-redundant-rows rule.
