SQLite Forum

SQLLite the right choice for storing web game data for many users
Login

SQLite the right choice for storing web game data for many users

(1.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-02-12 21:25:17 from 1.0 [link] [source]

Hi All

Hope everybody is safe and well in these weird times!

I've been in the process of creating a web game for about 15 years now :) and I've been mulling over a particular design issue for probably just as long, so I thought I'd try and get some pointers from the professionals.

Basically my idea for a web game will potentially contain quite a bit of on-going data for each user (probably thousands of rows over time) and hopefully there might be plenty of users that want to play it, but I think it's one of those games where one user might play it once and that's it and another might play it for months. Either way I'd need to keep their 'account' alive so that they can resume it at any point.

My thinking is that it wouldn't be a good idea to put everything about the game in one DB (such as a SQL Server DB) as there might be tonnes of redundancy in there over time - plus I don't want to be stripping rows out either as this will fragment the DB as well.

Therefore I'd got it in my head that I'd auto-create a DB for each new user to encapsulate everything they do, which in my head, would mean that each user wouldn't be lagged by the others, ie only 5,000 rows in Jim's DB, only 2,500 rows in Bob's DB, etc so queries would be generally faster.

However I don't think SQL Server will allow thousands of DBs on a single web server plus the management of them through the Manager might be onerous to say the least.

So I was thinking that it might be best to use an SQL Server DB for the general login details, user account info, etc basically everything but the game details and then store the game details in a SQLLite DB. These would be singular, easier to manage (I'm assuming you can simply backup a single DB file and have everything in it) and basically throwaway-able if a user account is closed.

Does this sound like a good plan???

Regards

Mac

(Edit by drh: fix the spelling of "SQLite" in the title.)

(2) By Gerry Snyder (GSnyder) on 2021-02-12 20:44:30 in reply to 1.0 [source]

I think it would make more sense to have a single database with the game info for all the players. With appropriate indices queries almost certainly would be more than fast enough.

Gerry Snyder

(3) By Larry Brasfield (larrybr) on 2021-02-12 21:08:56 in reply to 1.0 [link] [source]

I think your separate DBs plan makes sense from a data management perspective. Yet, before I would subscribe to it, I would want to know:

  1. Is the number of simultaneously active players low or high?

  2. Are their privacy issues that must be considered?

  3. Will there be player interactions such that queries involving more than one user will be convenient? (Building queries can be tedious, to be avoided if SQL can express the same thing.)

  4. Is there some user aging/purging scheme to deal with the "ever-growing, never shrinking dataset" issue?

From your thought to use SQL server for account info and SQLite DBs for user game state, it appears the server DBMS need not be involved in game play, only at logon for each active user. It is unclear whether multiple game state DBs need to be ATTACHED in the same SQLite session or if per-active-user sessions can be used. (relates to Q.3)

... would mean that each user wouldn't be lagged by the others ...

I would be careful about just assuming that to be the case. A famous computer scientist taught that "premature optimization of the root of [most] evil". I would be remiss to go along with your assumption if that's all it is.

(4) By anonymous on 2021-02-13 01:58:57 in reply to 1.1 [link] [source]

One thing I would keep in mind is change management. Updating the schema of a single central database is straightforward and easy. The same cannot be said though when it comes to thousands of databases that might even be scattered across machines.

(5) By Ryan Smith (cuz) on 2021-02-14 09:35:18 in reply to 1.1 [link] [source]

To compliment what others have highlighted already, the file system is itself a database of sorts and often not cluttering it improves speed a slight bit and design inset a lot, especially when rather using a tool (such as a DB engine) whose very purpose it is to make data access fast and convenient.

I would most certainly go straight for the single database with well-defined Indexes (there are good and bad ways to index, but that might be a different question thread). There is no evidence that it would be noticeably slower for look-ups than opening individual databases.

What can happen is in the case of very many simultaneous users coupled with very high "write" activity, you could see a slowdown due to write concurrency (while WAL mode handles read concurrency just fine). Moving database accesses to different files for different processes would solve that issue at a very high inset cost in design terms, if it really is an issue. Personally, I would wait for evidence of undue slowdown before considering the multi-file option.

(Edit by drh: fix the spelling of "SQLite" in the title.)

Oh burn! :)

(6) By anonymous on 2021-02-16 20:03:27 in reply to 1.1 [link] [source]

Hi All

Many thanks for your time and wisdom. Very much appreciated.

Rgds

Mac