Styjun

will start by saying I am new to database design but intend to move across to SQLite for one of my projects with python to make reading data more efficient. Currently, I have a tree-like structure of my data stored for a game which gets recorded hourly. I have a tree of folders in the following format: Username/Year/Month/date/time.csv

My aim to scrap that completely and after some discussion over on reddit came up with some basic design but still not sure if it is the best. I will post the ER diagram I have drawn here:

So some clarification on each of these:

• server gives the server the user was logged into (at the time of the recording)


• UserExperience will record all seen skills in which experience was gained in over the hour (and how much experience). So an aggregate for the hour


• This is one i'm really unsure on - UserActivites. This could be variable text of some events that may have happened over the hour ("I levelled X skill to level Y", "I killed Z many of boss A", "I completed quest B") and many more that are added and removed overtime, so not something I can exactly make a primary-key. I'm not really sure what can be done here.

Any help would be really appreciated :D feel free to ask for any clarification. Also not entirely sure how I would do queries to get statistics of some set of users between 2 dates but that is a separate problem

Thanks for any responses!

So the activity (in UserActivites) is always one that relates to a (over the time maybe growing) Activities lookup table. The text representation of the activity is in the Activities table and you are just linking to it from your UserActivities table - seems ok so far. You have already a foreign key pointing from UserActivities.ActivityID to Activities.ActivityID (and the same for Username to UserStatus)?

The datetime is just a point in time - no need to put it into some sort of combined index (as the primary key is). You might index the datetime in the future for a better query performance though.

What I would do is:

• throw away the combined primary key on UserActivities


• create a non-meaningful, auto-incrementing integer primary key in the UserActivities table (ID INTEGER PRIMARY KEY)

Things you should consider:

• create an index on Username and another one on ActivityID in UserActivities; it's not necessary, but recommended that child key columns of foreign keys have an index (https://sqlite.org/foreignkeys.html#fk_indexes)

Your data design looks good that way. You mentioned "and removed overtime" concerning the user activities (if I understood correctly). If so I would create a boolean column in UserActivities for deleted records. That way you can analyze activities that have been removed. I would never delete records once they have been created in the database.