Most efficient way of storing data from multiple accounts
As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts.
The ways I have been looking at are:
1. Create a new database for every account created so that all users are kept in seperate databases.
2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be
Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables.
I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands).
I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems?
Including the ForumID as part of the primary key indexes on each table will work well for that. However if you can get an automated way to create separate tables for each forum from a performance perspective that would be better. When data is being written to a given table it locks that table from other writes and often reads, depending on the configuration. As such if you have 20,000 forums potentially writing to a given single table.... that could be a lot of time the db is unavailable.