Database Design challenge: One (Table)-to-Many (Tables) Relationship

I would lean toward multiple databases. Primarily because you can set them up for different recovery, SLA, maintenance, deployment, changes, etc. And also this allows you to move certain databases to faster or larger I/O on different drives without disrupting everyone.

For the master tables that are used by all the applications, no I don't think you should have multiple copies of these. Your applications can query different databases and/or have different connection strings for different tasks (best separated by a middle tier of some sort in most cases). If the app-specific code within a single database needs to have some kind of reference to them, you can use synonyms or three-part names. This is easy to extend if you later need to split out not just across databases but across multiple instances / servers (just add a linked server and make it a four-part name).

Only if the link between these servers is notoriously slow or otherwise unreliable would I consider making replicas of the master tables. I talked about how to do that here and here, even though I don't explicitly cover moving data across servers.

Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals:

• Maintain third normal form
• Maintain referential integrity
• Maintain the constraint that each account belongs to either a corporation or a natural person.
• Preserve the ability to retrieve data simply and directly

The problem is that some of these goals compete with one another.

Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons. This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g. customer_type). This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive. The problem is that this makes data retrieval more difficult, because you always have to branch based on customer_type when you join account to the account holder. This probably means using UNION and having a lot of repetitive SQL in your query.

Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person. This solution also allows you to maintain referential integrity, normalization and mutual exclusivity. It also has the same data retrieval drawback as the sub-typing solution. In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".

Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced. In the sub-typing solution you use keys to enforce the mutual exclusivity. In the two foreign key solution you use a CHECK constraint. I know some people who have an unjustified bias against check constraints. These people would prefer the solution that keeps the constraints in the keys.

"Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's OwnerTypeID column). This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table. (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.) This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one. The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity. SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.

Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another. This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person. This has the advantage of avoiding the need for a partitioning attribute. You could do this with or without a super-type table. Using a super-type table allows you to use declarative constraints to enforce uniqueness. Otherwise this would have to be enforced procedurally. This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table. It still does nothing to avoid complex retrieval logic.

You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple. You have to decide where your trade-offs are going to be.