0

I am new to database things and only have a very basic understanding of them.

I need to save historic data of a leaderboard and I am not sure how to do that in a good way.

I will get a list of accountName, characterName and xp.

Options I was thinking of so far:

  1. An extra table for each account where I add their xp as another entry every 10 min (not sure where to put the character name in that option)
  2. A table where I add another table into it every 10 min containing all the data I got for that interval

I am not very sure the first option since there will be about 2000 players I don't know if I want to have 2000 tables (would that be a problem?). But I also don't feel like the second option is a good idea.

Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36
Lars F.
  • 1
  • Databases were invented so you wouldn't have to store your data in 2000 different places. Don't do it that way. – Mark Ransom Feb 20 '21 at 16:26
  • The design of a database is close to a matter of taste.But 2000 tables with same columns is certainly a poor design: put everything into the same table and add a column for the player id. You will certainly add an index on that player id to speed up queries for a player, and optionaly create a view for each player if you need. – Serge Ballesta Feb 20 '21 at 16:28
  • but if i have everything in one table how do i add the history data (xp value every 10 minutes) for each player? – Lars F. Feb 20 '21 at 16:31

2 Answers2

1

It feels like with some basic dimensional modeling techniques you will be able to solve this.

Specifically it sounds like you are in need of a Player Dimension and a Play Fact table...maybe a couple more supporting tables along the way.

It is my pleasure to introduce you to the Guru of Dimensional Modeling (IMHO): Kimball Group - Dimensional Modeling Techniques

My advice - invest a bit of time there, put a few basic dimensional modeling tools in your toolbox, and this build should be quite enjoyable.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36
0

In general you want to have a small number of tables, and the number of rows per table doesn't matter so much. That's the case databases are optimized for. Technically you'd want to strive for a structure that implements the Third normal form.

If you wanted to know which account had the most xp, how would you do it? If each account has a separate table, you'd have to query each table. If there's a single table with all the accounts, it's a trivial single query. Expanding that to say the top 15 is likewise a simple single query.

If you had a history table with a snapshot every 10 minutes, that would get pretty big over time but should still be reasonable by database standards. A snapshot every 10 minutes for 2000 characters over 10 years would result in 1,051,920,000 rows, which might be close to the maximum number of rows in a sqlite table. But if you got to that point I think you might be better off splitting the data into multiple databases rather than multiple tables. How far back do you want easily accessible history?

Mark Ransom
  • 299,747
  • 42
  • 398
  • 622