I have a database design issue, one that has rattled my brain.
What is the project?
I am designing a platform where different people register a group, each group is allowed to register their own members, it is important to keep these groups away from each other, as each are to their own, each group would have access to 65 or more tables with possibilities of 1000 of rows of data in each table, in their own database, each database is a copy of an empty master database.
Where i am stuck.
I do not know if this is wise cause i could end up with a 1,000 – 30,000 databases. I need to either create a database with a unique name (scenario above) or create a database with a unique environment variable, where each group has it's own environment variable.
Where things get messy.
Assigning each group to a unique database allows me to keep things simple, allows me to access all the data within the group relatively easy (vs searching through 1000 of groups data in one database). a simple but not too important, but still relevant eg. Each group can create things like their own invoices, so if all groups have unique databases then the invoice numbers would follow logically (users perspective), but if all groups are within the same database invoice numbers would not follow logically (users perspective).
The Question Then
Should I, or would it be wise, to create multiple database to keep my system efficient, bear in mind that there is no need to cross over from one group to another.
Best Answer
I suggest:
This should provide you with the convenience of a single database, combined with something like the performance you would expect from separate databases for each group.
Based on the comments on the related question on StackOverflow, you are using MySQL - you can find the official MySQL 5.5 Reference Manual section on partitioning here.
There is also a related StackOverflow question here, and a related blog entry here.