Pros/Cons for a Data Structure covering multiple databases?

Question

I'm about to extend a system containing two separate systems. Or domains, if we see the system as a domain level.

Today

One domain is used internally. The other is public, which means it is more exposed to whatever can happen "out there". Services of some kind (few options apply) perform operations between the two separate systems. The public and internal system have separated database storages, and are not related to each other. Let's call the domains client and store.

Tomorrow

I'm into a new application that will have lots of more user-specific information at the client domain. The design may be comparable to a 'private database' that has a facebook-account-link as a key. This compares to a database that held user actions, but had no user specific data (such as address, date of birth, email and so on), just a key to the Facebook-server. On the other side, the STORE database (compared to Facebook-server in this case) doesn't know anything about the user actions.

Goal

Approximately 85-90% of the queries will be within their own domain (client if client, store if store). Though, there are some situations where the client need to get data from the STORE database and the STORE needs to create/handle users in the CLIENT database.

Questions out from my head

• Is this a good way to go?
• Pros/cons? Orphans? Database Performance? Problematic .NET coding?
• Principles of the choice for the boundaries of data?

.. and other things you may know or think of

An example of structure

..dbStore.Users

• userid (PK)
• name
• dateOfBirth
• emailAddress -> Wallet (another table)

..dbStore.Wallet

• walletid (PK)
• userid
• createddate -> BankAccount (another table, not typed)

..dbClient.users

• storeUserId (unique, non-inc) <-- corresponds to dbStore.User, no restraint
• accessLevel
• lastLoggedIn
• serviceXxEnabled
• udpAccessKey

..dbClient.action

• actionid (PK)
• isSomething
• someDate
• anythingElse

..dbClient.installation

• installationid (PK)
• userid

We won't collect bank-account information, it just to give a picture of the data structure and its importance in keeping it in separate systems. I hope this Q is comprehensible enough.

Answer 1

Let's think of this from the perspective of potentially splitting up one database into two, in order to highlight the differences.

The main disadvantages of a split are:

• Inability to use DRI on common columns. Hardly a deal-breaker but you do need to start designing your application(s) around the possibility that an ID might not exist where you expect it to, and you need to work on good synchronization solutions.

• Inability to perform efficient joins. This is actually only partially true; in some cases, if both databases reside on the same server, you can perform cross-database queries - however, even in these cases, the code can be quite awkward to write, as you can no longer rely on externalized connection strings and so on.

• Distributed transactions are much more costly than single-database/single-connection transactions. In a Microsoft environment you'll need to invoke the DTC and that adds about 10x overhead.

• If you use a Dependency Injection framework, there's a pretty good chance it's been written so far to assume that there will only ever be one IDbConnection, ISessionFactory, etc. Using two database might require a lot of refactoring. Not an issue long-term, but entails an up-front cost.

• A bit more cognitive stress for developers as they now have to remember which database each table is in, and/or refer to some documentation for it.

• Backup/restores can be more complicated, as you will likely need to restore both databases if one fails, in order to keep them in sync. Which also means that your backup plan will also need to be designed to minimize the possibility of inconsistencies (unless your application is very fault-tolerant in this regard).

That's the bad. There are also some advantages:

• You can move one database to a different server in order to reduce processor/memory contention.

• You can move one database onto a different drive or SAN in order to reduce I/O contention.

• Potential elimination of the "single point of failure" problem. Each system should, at least in theory, be able to keep functioning reasonably well if the other one fails.

• Easier to focus team efforts and have independent dev/test/release cycles for each system, if you're working on a larger team.

• Potentially improved security; if one database gets compromised, private data in the other database can remain private. (Many security architectures are split into several parts for exactly this reason.)

I'd be remiss if I didn't also mention some of the alternative solutions, or compromises if you will:

• Different schemas in the same database. This gives you logical separation of concerns without forcing physical separation. In fact schemas can be used to run multiple instances of the same application within the same database, and you can share common tables (e.g. configuration tables) between them.

• Filegroups and files. A lot of SQL developers seem to forget that these exist; if one of your main concerns is the physical storage then it's possible physically segregate the tables for each system while still keeping them in the same logical database.

• Pub/sub is an alternative to the specific performance problem of big long distributed transactions, although it will require some additional development and support to maintain, both up-front and over time.

Generally speaking, I try to keep as much transactional data as possible in a single database because of the localized performance advantages it provides; there are many ways to scale up and scale out while still having a single logical database. But ultimately it's an architectural decision that depends on your access patterns and business requirements.

Answer 2

If it will remain 2 systems then changes to the implementation of one of them should not effect the other. That requires a clear separation and to do that I would:

1. Only allow access to each database by the system that owns it.
2. Access the other system through clearly defined interfaces.