Guidance about developing a public application with lots of db's

[eluser]kevinprince[/eluser]
Hey Guys,

Im starting to develop a new version of an existing CRM project for multi company use. The current version is specific to the business I built it for, but there is large demand for a similar solution within the specific industry.

One of my main thoughts I have had is how to handle version migrations with databases. I want to stick each customer on there own database, so that there data is kept separate. This means when I rollout an update to the application, I need to be able to do it to lots of db's at the same time.

Hope this is clear any advice appreciated.

[eluser]thurting[/eluser]
If the schema will be copied exactly across all of your clients, it may be a better idea to create one and set up a client table; e.g. table client (id name). You could then make sure to reference client.id as a foreign key constraint in all tables that contain data related to client instances; e.g. table customers (id client.id name). This is how I would do it. You don't really need to copy the schema for each client.

[eluser]kevinprince[/eluser]
Hey,

In theory that works, however were not just dealing with client data, there is also going to be ledgers, products, and event records. This means as the application expands, i could end up with 1000s of tables in a single db (obviously i would be doing some sort of splitting ie 10 clients to a single db).


The problem remains the same...

[eluser]Sean Murphy[/eluser]
Why would you have 1000s of tables in the DB? Do you create separate tables for each client?

I agree with thurting, it makes more sense to add another table, or level of abstraction for clients, rather than create a new DB for each client.