Should I use a single or multiple database setup for a multi-client application?
I am working on a PHP application that intends to ease company workflow and project management, let's say something like Basecamp and GoPlan.
I am not sure on what the best approach is, database-wise. Should I use a single database and add client-specific columns to each of the tables, or should I create a database for each new client? An important factor is automation: I want it to be dead simple to create a new client (and perhaps opening the possibility to signing up for yourself).
Possible cons I can think of using one database:
- Lack of extensibility
- Security problems (although bugs shouldn't be there in the first place)
What are your thoughts on this? Do you have any ideas what solution the above companies are most likely to have chosen?
Answer
I usually add ClientID to all tables and go with one database. But since the database is usually hard to scale I will also make it possible to run on different database instances for some or all clients.
That way you can have a bunch of small clients in one database and the big ones on separate servers.
A key factor for maintainability though, is that you keep the schema identical in all databases. There will be headache enough to manage the versioning without introducing client specific schemas.