Modular Application Database Structure

Question

I am building a modular application. Through configuration you can turn these application modules on and off. I'm trying to determine what database structure (mssql2005) I should use for the tables that hold data for each of the modules. The two options that I've thought of are:

1. Put all the tables into one big database and prefix the tables according to the module.
2. Separate the tables for each module into different databases.

I do have data that is common to all modules, so if I use solution 2 I'm not sure how to manage that common data (such as users).

--

To clarify one thing, These modules would potentially be sold separately and the configuration settings are something that are not controlled by the client. This is why I am even considering breaking them into separate tables.

Answer 1

If you need query across the tables I suggest putting them into one database, otherwise it doesn't make a difference. However, having one database would be much easier to maintain.

Unless you have a requirement for multiple databases, I suggest using just one.

Answer 2

If it were me, I would fully normalize the application first. I would develop a structure that places all common data within shared tables and leave only the data unique to each module in specific module tables.

If the data in the shared tables is truly common to all modules and there are no edge cases that you're trying to lump into these 'common' tables then turning one module on or off should no affect on the functioning of any other module and you now little (if any) duplication of data.

Answer 3

My alternative recommendation to those that you have proposed would be the Schema functionality available in SQL Server 2005.

Please read this link for more info ...

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1184503,00.html

Answer 4

I would have one database. This simplifies management of the solution immensely.

Then I would normalise my data. This simplfies the data integrity issues.

Then I would add tables for each module as required into the base database. I would distribute this base database but I would not distribute the data for the unused modules. Each module would distribute it's own data when it is installed (proably by its installer rather than built into the module itself).

Table prefixes are not going matter much one way or the other and the nice part about sharing the database is you can access each table from any module and have a shared configuration file etc...