Is there a way to create subdatabases as a kind of subfolders in sql server?

Question

I am creating an application where there is main DB and where other data is stored in secondary databases. The secondary databases follow a "plugin" approach. I use SQL Server.

A simple installation of the application will just have the mainDB, while as an option one can activate more "plug-ins" and for every plug-in there will be a new database.

Now why I made this choice is because I have to work with an exisiting legacy system and this is the smartest thing I could figure to implement the plugin system.

MainDB and Plugins DB have exactly the same schema (basically Plugins DB have some "special content", some important data that one can use as a kind of template - think to a letter template for example - in the application). Plugin DBs are so used in readonly mode, they are "repository of content". The "smart" thing is that the main application can also be used by "plugin writers", they just write a DB inserting content, and by making a backup of the database they creaetd a potential plugin (this is why all DBs has the same schema).

Those plugins DB are downloaded from internet as there is a content upgrade available, every time the full PlugIn DB is destroyed and a new one with the same name is creaetd. This is for simplicity and even because the size of this DBs is generally small.

Now this works, anyway I would prefer to organize the DBs in a kind of Tree structure, so that I can force the PlugIn DBs to be "sub-DBs" of the main application DB.

As a workaround I am thinking of using naming rules, like:

ApplicationDB (for the main application DB)

ApplicationDB_PlugIn_N (for the N-th plugin DB)

When I search for plugin 1 I try to connect to ApplicationDB_PlugIn_1, if I don't find the DB i raise an error. This situation can happen for example if som DBA renamed ApplicationDB_Plugin_1.

So since those Plugin DBs are really dependant on ApplicationDB only I was trying to "do the subfolder trick".

Can anyone suggest a way to do this? Can you comment on this self-made plugin approach I decribed above?

ADDED INFO (AFTER STARTING THE BOUNTY):

In the MainDB I plan to store the connection info to all the plugin DBs. Basically it is the database name, since I deigned the sytem in a way that even if I use multiple sql server logins to access the MainDB, behind the scenes a single user (typically "sa" or another user with admin privileges).

So basically if I need to query multiple databases I will use the database name to distinguish between plugins, I don't need to explicitly create fileds called PluginID in the database tables.

So somehow it works like this, in the main DB I store the plugin DB names. So I know the name of the plugins, so if I want to query all the GUNS from all plugins i will do something like this:

select * from ApplicationDB_Plugin_1.dbo.weapons where weapon_type = 'gun'
union
select * from ApplicationDB_Plugin_2.dbo.weapons where weapon_type = 'gun'
union
select * from ApplicationDB_Plugin_3.dbo.weapons where weapon_type = 'gun'

so the "trick" is using the dbname to distinguish between plugins. Now this work, but it seems a little "dirty" to me. My question is "IS THERE A BETTER APPROACH YOU CAN ENVISION?"

Answer 1

You can use schemas:

CREATE TABLE mytable (id INT)
GO

CREATE SCHEMA plugin_schema
CREATE TABLE mytable (id INT)
GO

SELECT  *
FROM    dbo.mytable -- selects from the first one

SELECT  *
FROM    plugin_schema.mytable  -- selects from the second one

Answer 2

If the schemas are identical, can you add something to the existing tables so you can tell that some data is from plug-in-1 while other data is from plug-in-2? You might find a need in the future to do 'cross-plug-in queries'?

Answer 3

I can understand how you arrived at your current solution. Keeping the databases separate makes it clear which data belongs to each plugin, and keeps concerns separate.

Although it offers clarity through a simple organization, this approach has some fairly significant downsides:

• Connecting to a different db for each plugin is not going to be face. The database server will have a run a query N times to query N plugins, since it has to run each query against a separate database for each plugin.

• Referential integrity is not easily enforcable across several databases (if at all) so it's quite likely your data may become inconsistent.

• reduced flexibility and "dynamism": creating a new database quite a heavy operation, and so adding a new plugin becomes quite a heavy operation.

• Finally, dealing with schema changes will be difficult - if you have a plugin that hasn't been updated to the latest schema, then it cannot be used in this scheme since all databases are assumed to have the same structure.

I would suggest a hybrid approach: each plugin continues to maintain it's own database which can be downloaded, and loaded at runtime, but rather than keeping the application and plugins in separate databases, the plugin data and application ddata are copied to a composite database. The composite database can be built at startup, or when the set of plugins changes, or as a new plugin version becomes available. This is workable since you mention each plugin database is only read, and not updated. (Rebuilding the database can be done so that the application data in the composite database is preserved.)

When the application data and the data from the plugins are integrated into one database you avoid the problems above:

• the database server executes just one query rather than one per plugin

• referential integrity is enforceable since all the data is maintained in one database.

• finally, and most importantly, in my view - managing schema changes becomes possible. If there are plugins that haven't implemented your latest schema changes, the merging process can adapt data stored using the old schema while it is copying the plugin data to the composite dataabase (which always uses the latet schema.) For example, while copying a plugin using the old schema, default values can be added for new columns, changes in structure can be accomodated, columns/rows can be deleted etc. The same "schema upgrade" code can also be given to plugin developers to allow them to upgrade their plugin schema.

The merge process could also build an "installed_plugins" table listing all the plugins that are copied to the composite database. The table contains metadata about each plugin such as date of plugin update, time added, unique plugin id etc.

So, what does the composite database look like:

1. plugin data stored as separate tables, using a view to combine them, The view would be a union of a corresponding table from each plugin, plus the a column for the plugin id. This will work, but is not likely to be efficient.
2. plugin data for a given table type are all stored together. Referential integrity is much easier to maintain and queries are going to be faster, since a common index can be used across all plugins.

My preference is for the second option. But with all plugin data stored together, how to know which plugin each row comes from? Again there are two options:

1. Add an extra column to the table with the plugin id/name. The advantage is that it's fast. The disadvantage is that it changes the base schema.
2. For each table, have a companion table that maps a row ID to the plugin ID where the data was sourced from. This will be a little slower to query, but keeps the schema of your core data tables free from changes.

For simplicity, my preference would be for 1, adding an ID column. Since the tables in the composite database are built by a script or program, either scheme is simple to implement, and so it's mostly about preference or performance needs or whether it's important that the merge database uses the same schema for the core data tables as the original application database.

I feel that merging the data together is the right approach, for less pain, easier maintainance, flexibility and greater performance. But if you still have strong motives for keeping the data in separate tables, then at least keep them in the same database, and use either table name prefix, or better, schema names to keep the plugin data namespaces separate.

Good luck, which ever way you choose!