DB structure for an autodeploy multi-application

Question

I want to create an application similar to basecamp or mailchimp. The customer registers him self and then sets up the application for themself automatically. The application will be developed using cakephp.

My question is what is the best DB structure?

• All customer separated by customer id in one table.
• Every customer with own DB + DB User.
• Use for every one an SQLite file in his folder.

Answer 1

There can be different approaches to the implementation and each depends on the nature of your application, like what functionality is provided to each user, what per-user data is involved and relationships those data hold, how much per-user data is involved etc.

Approach 1: single application database; multiple tables as per application's functionality/structure but the tables hold data for all users. For example, comments, permissions, categories etc.

pros: simple architecture, easy and quick retrievals and inserts

cons: the database operations might get expensive if the tables grow too large in size or involve complex indexes

Approach 2: single application database; multiple tables as per applications's functionality/structure; each user has its own tables set identified by perhaps the user_id. For example, for user_id = 1, the tables might be comments_1, permissions_1, categories_1 etc.

pros: again simple architecture; easy to identify which tables to query for a particular user; since tables will contain data only for a specific user, there be at least one less WHERE clause (where user_id = xx); smaller tables and therefore quicker retrievals; fewer chances for locks conflicts during busy hours

cons: requires more maintenance; adding newer functionality that requires a new column or a table to be added, will need schema changes to all the users table-set;

Approach 3: multiple application databases per user

pros: 100% isolation of data between users; easy to tweak the DB schema should customized functionality be required per user; easy to split databases across multiple servers for load balancing purposes;

cons: complex architecture; requires more maintenance; trickier to store common or shared data - the data might either be replicated to every user database OR a common database can be maintained.

I think if the schema is efficiently designed such that a balance is maintained between quicker SELECTs/INSERTs and amount of data per table, the first approach should work nicely for 100-10000 users. However, it will need much database tuning and smart indexes.

From approach 2 and 3, both work fine but from my perspective, approach 3 is better as it gives you more flexibility. The implementation might need some time but it is not difficult to

Also, SQLite doesn't seem to be appropriate for an implementation like this. I will suggest a relational database like MySQL.

Hope the above provides some insight into the implementation and helps you some in deciding what works best for your application.

Answer 2

If you're going to get big (scaleable) then SQLite is probably not your best bet. A true RDBMS is far more efficient. That being said, if you're truely going to scale Cake may not be the most efficient option either. Those are decisions for you to make based on your business model. It's good to have aspirations, but it's rare to become a 10,000 pound gorilla...pun intended.

My company has an application that does marketing automation for dozens of clients that uses a common DB for common functions and a separate DB for unique data. Yes, it works, and it's actually pretty efficient and does a good job separating data so the DB doesn't get out of hand....in fact, the shared db has tables with millions of records. That being said, keeping track of your connection STINKS and is more often than not the cause of our errors. Drop just one session or instantiate something wrong and BOOM! It's toast. I often find myself having to fully qualify my queries to make things work, which just adds to the stress. I don't think I'd do it this way again.

Also, from a sheer volume standpoint, having to find a database amongst thousands wouldn't be my idea of a good afternoon either. I dislike having to jump through 50 to find the data I need for troubleshooting.

With a single DB, one connection just works. From a Dev standpoint, it's much easier. It's hard for me to say performance-wise what the benefits are because our app suffers most from a terribly inefficient framework (legacy Symfony)