I have a SQL Server Express database for use with a program that uses plugins. The users of this system are pretty reliable, as they'd be a highly specialized group of people who wouldn't be writing any deliberately malicious plugins (the only people they'd hurt would be themselves). Still, I'd like to make it easier for people not to make mistakes.

In the database, there are a series of core tables that should only be written to by some core models, should never be altered, and should never have deletions (except during testing). Additionally, there are some number of other tables for use by the plugins. That set of tables can grow, but normally, they are only used for reading and writing. The plugins might also need to read from the core tables under some circumstances.

This got me thinking that I should split the DB in twain, with one DB holding the core tables and the other DB holding the other tables. There could then be one connection for the core models, which would be read/write on both databases, while there would be a second connection that would be read/write on the extra tables and read only on the core tables.

The one issues with that is that all changes are done in a transaction. The transaction gets passed around to all who need to participate, and transactions come from connections. So, there would be cases where the core models would need to write something to the core tables, while in the same transaction, plugins would need to write to the extra tables. If the connection that got passed around was based on the core connection, then it would have a login with read/write permission to the core tables, so plugins would be able to write to the core tables, which is not desirable. It's not horrible because of the trust in the users, but it does allow people to do bad things by mistake.

One alternative would be to use the plugin credentials for the connection and transaction, but that would mean that the core models wouldn't be able to write to the core tables, which they would need to do, so that alternative wouldn't work.

The only other alternative that I see would be to start two transactions, one for the core models, and one for the plugins. There's a good means in the program to do that, so that wouldn't be an organizational problem. If either transaction decided to rollback, then the other would rollback as well. Since the core would only be writing to the core tables, and the plugins would only be writing to the extra tables, it seems like deadlocks should be avoidable.

What I'm wondering is whether or not there is a better way to handle this, and if there's something obvious I'm overlooking? Starting a connection would start two transactions. If one rolled back, the other would roll back. If one committed, the other committed. One would only write to one DB (though it might read from the other DB), the other would write to the other DB, and wouldn't interact with the other DB in any way.

It still means two transactions, though. Is there a better way?