HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Proper technique for storing users event data

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
techniquedataforproperusersstoringevent

Problem

I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry standard' method.

Most databases I design have a user table, and then a persons activty is tracked in another table. I understand that the beauty of the database is to have these sorts of efficiencies, but the activity table will gather many many events fairly quickly just from every user using it regularly, thus becoming a huge table fairly quickly with moderate user usage. Is this best practice to just let it grow in this way? Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?

+--------------------+                   +------------------------+
|   UserData         |                   |   Activity             |
+-=------------------+                   +------------------------+
| ID     (auto uint) |  | UserID (uint)          |
| Email    (text)    |                   | Timestamp (time)       |
| additional info... |                   | Type (ID to elsewhere) |
+--------------------+                   | additional info...     | 
                                         +------------------------+


I just would like to know of where I can improve anything, as to help me learn.

Solution

You have made a very good observation. The Activity table will grow fast and large. What I have done in the past is archive off the older data (say older than 14 days) to an ActivityHistory table. Doing so keeps the Activity table to a manageable size and if you need to do research you can always look back at the ActivityHistory table.

Context

StackExchange Database Administrators Q#4040, answer score: 6

Revisions (0)

No revisions yet.