Max Tables & Design Pattern

Question

I am working on an app right now which has the potential to grow quite large. The whole application runs through a single domain, with customers being given sub-domains, which means that it all, of course, runs through a common code-base.

What I am struggling with is the database design. I am not sure if it would be better to have a column in each table specifying the customer id, or to create a new set of tables (in the same database), or to create a complete new database per customer.

The nice thing about a "flag" in the database specifying the customer id is that everything is in a single location. The downfalls are obvious- Tables can (will) get huge, and maintenance can become a complete nightmare. If growth occurs, splitting this up over several servers is going to be a huge pain.

The nice thing about creating new tables it is easy to do, and also keeps the tables pretty small. And since customers data doesn't need to interact, there aren't any problems there. But again, maintenance might become an issue (Although I do have a migrations library that will do updates on the fly per customer, so that is no big deal). The other issue is I have no idea how many tables can be in a single database. Does anyone know what the limit is, and what the performance issues would be?

The nice thing about creating a new database per customer, is that when I need to scale, I will be able to, quite nicely. There are several sites that make use of this design (wordpress.com, etc). It has been shown to be effective, but also have some downfalls.

So, basically I am just looking for some advice on which direction I should (could) go.

Answer 1

The risk of accidentally sharing data between customers is much smaller with separate database. If you'd like to have all data in one place, for example for reporting, set up a reporting database the customers cannot access.

Separate databases allow you to roll out, and test, a bugfix for just one customer.

There is no limit on the amount of tables in MySQL, you can make an insane amount of them. I'd call anything above a hundred tables per database a maintenance nightmare though.

Answer 2

Are you planning to develop a Cloud App?

I think that you don´t need to make tables or data bases by customer. I recommend you to use a more scalable relational database management system. Personally I don´t know the capabilities of MySQL, but i´m pretty sure that it should support distributed data base model in order to handle the load.

creating tables or databases per customer can lead you to a maintenance nightmare.

I have worked with multi-company databases and every table contains customer ids and to access its data we develop views per customer (for reporting purposes)

Good luck,

Answer 3

Multiple Databases.

Different customers will have different needs, and it will allow you to serve them better.

Furthermore, if a particular customer is hammering the database, you don't want that to negatively affect the site performance for all your other customers. If everything is on one database, you have no damage control mechanism.

Answer 4

You can do whatever you want.

1. If you've got the customer_id in each column, then you've got to write the whole application that way. That's not exactly true as there should be enough to add that column only to some tables, the rest could be done using some simple joins.

2. If you've got one database per user, there won't be any additional code in the application so that could be easier.

3. If you take to first approach there won't be a problem to move to many databases as you can have the customer_id column in all those tables. Of course then there will be the same value in this column in each table, but that's not a problem.

Personally I'd take the simple one customer one database approach. Easier to user more database servers for all customers, more difficult to show a customer data that belongs some other customer.

Answer 5

"Yes, it is an app in the cloud."

"I live on an appartment on the 99th floor of my block."

Answer 6

Single Database Pros

• One database to maintain. One database to rule them all, and in the darkness - bind them...
• One connection string
• Can use Clustering

Separate Database per Customer Pros

• Support for customization on per customer basis
• Security: No chance of customers seeing each others data

Conclusion

The separate database approach would be valid if you plan to support customer customization. Otherwise, I don't see the security as a big issue - if someone gets the db credentials, do you really think they won't see what other databases are on that server?