smart way to structure my SQLite Database

Question

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:

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)
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.

Databases were invented so you wouldn't have to store your data in 2000 different places. Don't do it that way. — Mark Ransom
– Mark Ransom, Commented Feb 20, 2021 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
– Serge Ballesta, Commented Feb 20, 2021 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.
– Lars F., Commented Feb 20, 2021 at 16:31

Dharman · Accepted Answer · 2021-02-20 18:40:51Z

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.

edited Feb 20, 2021 at 18:40

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Feb 20, 2021 at 18:35

Jon Jaussi

1,2963 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Ransom · Accepted Answer · 2021-02-25 04:14:44Z

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?

Collectives™ on Stack Overflow

smart way to structure my SQLite Database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related