Database Design

Published: 2022-02-26

Collecting presence data from multiple sources and presenting it to the user

Presence: History and Current

Storing and retrieving history with efficient API requests

I would like to see the last 14 days or so of player history to see what days my firends usually meet and what games they have been playing recently. Querying a database history table to find the latest entry is expensive and a more complicated than it sounds. Instead, when updating the database I’ll update a player table with the current status and store the history in its own table so I can retrieve that separately when needed.

I’ll add a last modified date on the player table entryies so the application knows if they need to be refreshed.

To merge or not to merge

Sometimes players are offline in one system and online in another and they can also show different game names or game status and they could have different avatars across each system. I will save all the data in the database and let the front end decide how to display it in the browser.

Linking and Filtering the players

There are many players across both systems but I only want to see the players I connect with regularly. Steam have persistent IDs I can use to filter and store the data but Discord removed the IDs from their widget so I only have the name. If a Discord user changes their name they will fall out of the filter criteria and I will have to manually update the configuration with the new name.

Where to save configuration data

I use to hard code all the IDs or use a config file but its a pain to update, especially now the Discord Id is gone. I also don’t want it in my source control in case I decide to make the repo public. It will take a long time to create a robust configuration system so I will start off with environment variables and upgrade over time to a better system. In future I may have to encrypt the data in the dabase, some info here

When data is unavailable

For scalability I decided I should store player data from each system in a separate table. I may decide to add Epic Games or Origin presence information in future and a common Player table would get quite messy. More importantly, a common Player table will have one LastModifiedDate. If a system went down and another did not, the lastmodified would update and the last status from the downed system would be stuck.

Player

• id
• Name
• DiscordUser
• SteamUser

DiscordUser

• username
• status
• Game
• Server
• Channel
• Avatar

SteamUser

• steamid
• personastate
• personaname
• avatarmedium
• lastlogoff
• gameextrainfo
• gameid