PHP Real Time Strategy Game Database Structure?

Question

I'm trying to build a real time strategy browser game in PHP however I'm trying to make the game as real as possible which means there will be allot of individual data to store for each user and one single database doesn't go well with my ideas since I want to store everything about a user and his "empire" that could make the game more realistically

---

Here's an idea about how much data is to be stored:

• Population: for example where each citizen will have it's own row to track his type, skills, stats, assignments etc. and since an "empire" can have few thousands citizens times (*) a few thousands users might result in a bit of an overload for a single table

• Army: for example where each soldier will have it's own row to track his type, skills, stats, assignements etc. and since an army can get a bit large times (*) a few thousands users might result again in a bit of an overload for a single table

• Buildings: .... same story here

• Wealth: .... where every ounce or goods a real empire can hold is tracked

• etc. and you get the idea....

---

So my first idea is to give each user an unique ID ex. 5b325475ef698183bbbcc5352c7f20f5... and have a user folder where to store SQLite databases for individual users in a structure similar to this:

/user_data
|--/5b325475ef698183bbbcc5352c7f20f5
|----/account.db
|----/buildings.db
|----/army.db
|----/population.db
|----/empire.db

And a MySQL database for storing general public details for the fronted which will be the result the accumulated data from the sqlite databases

But then I've heard that an sqlite database is locked while performing actions on it which might not be a good solution for a multiuser site Also the down side of this is that it might be hard to make changes frequently since you have to alter each database for each user (still better than using a single database that might get slow and overloaded over time)

---

And my second idea is to have a different MySQL database for each user again using an unique user ID however I'm thinking I will end up having too many database connections and a really messed up code to maintain and again the down size of this is that it might be hard to make changes frequently since you have to alter each database for each user (again is still better than using a single database that might get slow and overloaded over time)

---

If anyone encountered this kind of problem and found a solution please share it or which one of the ideas posted above might be the most suitable keeping in mind that is a browser game and players will spend most of their time switching pages resulting in a large number of database interactions (that's why a single MySQL database might not be a good solution)

Thank you for your time and patience :D

EDIT: The application will be built using the Yii Framework but if required I'll build a new "framework" from scratch

Answer 1

One database per User is absolute nonsense if you'd like to grow your user numbers. Imagine 10000 people playing your game, you'd have to have dozens of database servers to handle them all and your costs gonna explode for sure.

Relational databases can handle very efficently millions of rows of data, when you do it right. You should make yourself familiar with the principles of database normalisation before you continue your project or you're going to fail with your approaches.