SQLite Forum

SQLLite the right choice for storing web game data for many users
Login
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.)*