Is it better to use a relational database or document-based database for an app like Wufoo?

Question

I'm working on an application that's similar to Wufoo in that it allows our users to create their own databases and collect/present records with auto generated forms and views.

Since every user is creating a different schema (one user might have a database of their baseball card collection, another might have a database of their recipes) our current approach is using MySQL to create separate databases for every user with its own tables. So in other words, the databases our MySQL server contains look like:

main-web-app-db (our web app containing tables for users account info, billing, etc)
user_1_db (baseball_cards_table)
user_2_db (recipes_table)
....

And so on. If a user wants to set up a new database to keep track of their DVD collection, we'd do a "create database ..." with "create table ...". If they enter some data in and then decide they want to change a column we'd do an "alter table ....".

Now, the further along I get with building this out the more it seems like MySQL is poorly suited to handling this.

1) My first concern is that switching databases every request, first to our main app's database for authentication etc, and then to the user's personal database, is going to be inefficient.

2) The second concern I have is that there's going to be a limit to the number of databases a single MySQL server can host. Pretending for a moment this application had 500,000 user databases, is MySQL designed to operate this way? What if it were a million, or more?

3) Lastly, is this method going to be a nightmare to support and scale? I've never heard of MySQL being used in this way so I do worry about how this affects things like replication and other methods of scaling.

To me, it seems like MySQL wasn't built to be used in this way but what do I know. I've been looking at document-based databases like MongoDB, CouchDB, and Redis as alternatives because it seems like a schema-less approach to this particular problem makes a lot of sense.

Can anyone offer some advice on this?

Answer 1

Since you are leaving the schema up to your users to decide, it doesn't make sense using a relational database that forces you to define a schema.

Use a NoSQL database. Do some more reading on stack overflow.

What is NoSQL, how does it work, and what benefits does it provide?

Pros/Cons of document based database vs relational database

What is the best Document-oriented database?

Answer 2

Creating tables on the fly like you describe is a very bad idea. Supporting schema changes would be a nightmare. Each time someone added or removed a field you would have to run an ALTER TABLE ... command, and if there's data i the table, that's not a quick operation since it basically creates a new table with the new scehma and moves all the data over to the new one. Don't go down that route.

You could implement some kind of key/value-store on top of MySQL without too much work, or use something like Friendly, but going for a proper document database is probably a much simpler way.

MongoDB would be my choice, but there's a lot of things to consider, and others may say that Cassandra would be better. It's very easy to get going with MongoDB, and using it feels quite familiar to using a SQL database. It does indexing more or less identically, and querying is not too different either. The best thing though, is probably that you don't need an ORM, your objects are stored more or less as-is in the database. Reading and writing can be done very close to the metal without requiring a lot of mapping to and from objects.