Is SQL structuring (in mysql database) effective in a real world model?

Question

I was hoping to get feedback on the example of the mysql structure for a web application in a real environment from people who used to use complex mysql in real situations.

example ~

education management application. 80,000 users. each user gets his own database containing tables for -Messages -uploads -appraisals -info

and other tables for other functions

what interests me, and any information will be appreciated in such a situation

Is this database model effective? (basically, like 80,000 databases) or is (and I have this itchy feeling) the best way to do this? - Which of the dedicated servers will this require? 80,000 databases, each of which contains 10-15 tables containing TONS tables, with all 80,000 people accessing the site 20-30 times a day for 10-20 sessions.

Answer 1

Get started.

Now!

Jokes aside, do not do this. Do not create one database for each user. This is hell for administration, support and request. What if you need to know which users logged in yesterday? Will you query each database?

The structure you need is the same, only the amount of data changes. You just have one database, see how it works, and then optimize / tune.

I really like to quote this quote, but in your case it is fully applied:

Premature optimization is the root of all evil (Donald Knuth)

Do not try to optimize your decision before , you know where your bottlenecks will be.

Just set up your database as best as you can. Worry about your limitations, PC, FK, Indices. Do database-design homework. Then run your data and software. Only then will you see where he works and where he hurts. At this point you are optimizing.

Attack your enemy only when you know who he is.

Answer 2

it can be efficient with a suitable storage engine to support this model. Most recent no-sql data stores (hadoop, bigtable, mongodb, etc.) are great for this kind of scenario.

If you think about it, user data is a great way to share data warehouses on isolated islands (most interactions between different user databases do not have to be transactional, and very few if there is an exchange of write requests)

Basically, I would think that no-sql does not give you any advantages for data relationships inside the user data tree itself, so the relational and non-relational performance characteristics of the storage should not matter much

With that said, traditional relational databases like mysql are not designed for mass management, so in this regard you might want to consider another data warehouse (or hire the dba rock star)

Answer 3

Both Adrian and Lursher have already given a lot of details.

Not knowing how much data and circuitry is hard to get into design. From the very beginning, with active 80K users, the load does not seem huge, even if they access the data hundreds of times a day. I got the feeling that you can create a normalized scheme for the OLTP environment with a user table and work with other tables there. Again, this requirement will drive the design. For example, what should be the response time for a user request - second, second?