Codeigniter: how to track activity w/o using multiple db rows?

Question

I need to track the completion of tasks for users.

I started by having a row called "first_login", "profile_complete", "other_thing_complete", etc...

I'd like to combine these all into one row "activity_state".

I just don't know how to properly store data in this field, and how to retrieve it.

What kind of field should it be, and how should I read/write to it?

Answer 1

I'm not sure I fully understand the requirements - and I'm sure others here will have a better answer... but here is my go.

I assume you have a users table already.

What if you added an activity table and a users_activities table.

activity
-------------
id
name

users_activities
------------------
id
activity_id
user_id

So whenever a user completes an activity, you just add a record to the users_activities table.

Answer 2

If you only have a small number of "activities" that you'd like to track, you could have a table with a BOOL column for each activity you want to track.

activity
--------
INT user_id
BOOL first_login (default 0)
BOOL profile_complete (default 0)
BOOL other_thing_complete (default 0)

This could even be a part of your existing users table. Each column would default as 0/false; when an activity occurs, you simply update it to be true. For example:

UPDATE activity SET first_login = 1 WHERE user_id = 23

Answer 3

You seem to be confused about the meaning of "row". It seems that you first created a table with the columns "first_login", "profile_complete", "other_thing_complete", etc. Each user would have their own row in that table, with the columns that you have specified.

You then seem to have determined that you want to store all of those data elements in a single column called "activity_state", presumably because you realized that you would have to update the table every time you identified a new task that you wanted to track.

If you're going to do this - store all of your data elements in a single column - you should store it as a serialized object.

That said, the method suggested by someoneinomaha is the best way to do this. It is completely extensible. You don't have to worry about parsing the field. Most importantly, with your method, you will have to perform full table scans any time you're searching the table on any of the values you are tracking.

Answer 4

Never store serialised objects, accessing them is slow (retrieval, unserializing) and searching is even harder.

If it's a relateively small number of tasks and they are boolean you could store them as bit masks (that may be the wrong term).

1 = first_login
2 = profile_complete
4 = other_thing_complete
8 = other_thing2_complete
16 = other_thing3_complete
...

Then the value 10 means profile_complete+other_thing2_complete. See http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html or searching these.

mysql> select 10&2;
+------+
| 10&2 |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select 10&8;
+------+
| 10&8 |
+------+
|    8 |
+------+
1 row in set (0.01 sec)

mysql> select 10&1;
+------+
| 10&1 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select 10&4;
+------+
| 10&4 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select 10&16;
+-------+
| 10&16 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

As you can see only the first two queries returned a non-zero value, meaning that if you search for records where the field anded with 8 is non-zero you get all records where other_thing2_complete is complete etc. To set other_thing2_complete to true you simply add 8. To set other_thing2_complete to false you simply subtract 8.

Never done this myself, so you may run into problems I've not foreseen. But if all the activities you are keeping track of are boolean, then this should do the trick and allow for the best searching/updating.