Creating db of a large application the right way

Hi, my question is about creating right db architecture. Application will be served in sas model, which means that every client should have its own instance of the application.

In case of yiiframework I asume its always one base instance. But how do You deal with creating new account in case of db? Do You create new database for each client or do You model database in traditional manner (I mean all client s are managed in the same big database).

Thanks for sharing Your experience and thoughts…

I would say: It depends, if you except "large" amount of data, per user. You should use one db per user.

If you need “security” you should also create one db per user. And even separate each directory. Well, I mean, space isnt such expensive that you could mind some more mb per user :)

For ease of use, one db is sure the easiest way to go.

I don’t have an experience in creating large solution in such model, so these were my initial ideas. As You write, db per client/user is common approach in real world apps.

But correct me if I’m wrong, do You create whole new instance of an application in new directory with preconfigured new db?

You can use one db for many user with better performance by using InnoDB cluster index feature. Try to read on http://www.scribd.com/doc/7820041/Optimizing-Large-Databases-Using-InnoDB-Clustered-Indexes

hello,

Are you referring to Software as Service model? (http://en.wikipedia.org/wiki/Software_as_a_service)

I’ve seen DB tables with almost a million records and the app was still performing fine. Of course you need caching and a well thought index structure on the tables etc.

I think it’s always a good problem to have too many users accessing your site :)

–iM

Having a database per user is going to be a BIG ballache. You’ll be faced with a number of challenging problems around synchronizing reference tables, and managing who owns what DB. Don’t forget, KISS. :P