I would value input regarding a database design question. Our company is developing a new application and the purpose is take several of our current applications and combine them into one. Each customer may only use certain functionalities of the application. Most customers will not use all the functionality of the application.
The most important aspect of the new software must be that one customer absolutely cannot see another customer's data. This error has happened in the past due to training issues because of the central administration of user rights within the application.
The president would like to see separate databases for each customer because he feels this is the only way to ensure that the data from one customer cannot be seen by another. The most customers we will probably have at one time may be 20.
Another option would be separate databases for each module. As I mentioned every customer will pick certain functionalities that they will use and the functionalities can be grouped into modules. Currently the grouping of these functionalities is in separate applications and each application has it's own database. One could think of a group of functionalities as a module and the customer could then decide what modules they would like to use. Each module could be a database, but with this design we would have more than one customer's data in each module. There would be 5 modules.
Another requirement is that authentication must be central. Meaning there is administration needed where one person may need to see more than one customer's data.
Another issue is maintenance. There will be times where we will need to release new code to the application or to the databases. There could be cases where a customer does not use a current module, so they do not agree to the code upgrade. Therefore, we may need to keep track of different versions of the entire application if going to the customer database driven design.
To sum up the following are my options.
1. Separate database per customer. All functionality for the application and all modules will be included in each customer database, even if not used.
2. Separate database per module. More than one customer will be in each module and will be kept separate using unique ids. We will not
I'm interested to know what others have done in this situation.
Thanks for your help and please let me know if you need more information.