Design keeping track of activity

Question

I want to keep track of different activities/actions made by members of a website. For example "post created", "successful login", "file uploaded" etc.

Is it OK to just have a table named for example "actions" and save timestamps , user ID etc and the primary key for the actions such as post_id (table Posts), file_id (table Files)

Is this a valid way of doing it, or is there a better way?

Answer 1

Your proposed design is completely viable.

Couple of things to consider:

• the table most likely will be write (INSERT) heavy
• will grow indefinitely unless archived
• queries most likely fall under OLAP and not OLTP (aggregations over point queries)

Workload

Assuming InnoDB engine you want primary key to be a continuously incrementing id so INSERT won't be random causing too much page split and fragment the tablespace.

Benefits of AUTO_INCREMENT:

• Always incrementing
• Providing fast insert rate

Downsides of AUTO_INCREMENT:

• One extra column
• You probably will never query on PK

Timestamp would be a great candidate for PK if you can keep it UNIQUE since it's most likely will participate in most of your queries and most like as a range. (PK is at the end of every secondary key).

You could also experiment with the an id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY instead. Use whichever gives better performance.

Size

Try to keep the row size as small as possible to avoid sharding/partitioning/archiving as long as possible (eventually you will need to but MySQL is quite good with tens of millions of rows even).

To achieve this use only IDs in this table and have lookup tables where necessary. For example: instead of action VARCHAR(12) use action_id TINYINT UNSIGNED. TINYINT is 1 byte whereas action will be always at least 2 assuming ASCII, latin1 or similar small character set but longer the string becomes the more byte it will use obviously. UTF8 can use up to 4 bytes / character.

Only put index on columns where you really need to. Daily, monthly, etc aggregation tables will serve you better than heavy indexes on this table.

A possible schema

CREATE TABLE my_action_log (
    ts TIMESTAMP(3) PRIMARY KEY,
    action_id TINYINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,   
    post_id INT UNSIGNED NOT NULL,
    ...
    KEY ... # Whatever index you need
) ENGINE=InnoDB