Multiple database system design

Question

I am designing a few different systems that revolve around a core system used to manage users, groups, associations between users, group memberships, user profiles and some other things.

System A is a task management system with tasks, assignments, attachments, etc...

System B us the user system.

System C is associated with some business workflow type of things (still figuring this one out)

Essentially I’m trying to determine how best to handle the fact that a table in database A will need to reference a a table in database B.

For example: in the task system, a task can be assigned to a user, or a group defined in the user system.

One idea I read about was to have some processes replicate the required data to tables in each system. So, a table in the task system that holds the group ID and it’s description would be updated when necessary from the user system.

Is this approach viable? Has anyone had success with something similar or different?

Answer 1

The most basic relation that must exist is using only Aggregate IDs. That is, the Task Aggregate has only the UserID or GroupID as a property. This has the advantage that is simple but the disadvantage that when displaying data in the UI you would need to join multiple tables from multiple Bounded Contexts. The join can be made using database technology (using the JOIN feature) or in code, by fetching the needed information as a separate query to the remote BC.

In any way the system is not resilient, if any of the services in the other BC fails (or is slow) then a cascade failure occurs. One BC depends on the other BC.

A more complex solution is to use an Anti-corruption layer that imports remote Aggregates as local Value Objects (VOs). That is, the Task Aggregate would have a User Value object as property. This VO is immutable and although it can contain the UserID it is not an entity.

This has the disadvantage that the VOs must be kept in sync with their corresponding Aggregates. There are at least two ways of doing this:

• using a background process that runs periodically
• using domain events - the preferred way

It has the huge advantage that the services in the two BCs are independent of each other in regards to resilience. If one service fails then the downstream services would be unaffected.

One other big advantage is that the services would be faster because no joins are happening, all the data is available locally.

You may want to read about CQRS if not done already.

Answer 2

The fact that tasks and users are stored in database tables is an implementation detail. You don't really want the tables to reference each other or to replicate databases, you just need a logical association between a task and a user.

Each of the three systems you described can be implemented as its own web service, with relations crossing service boundaries only stored in the form of IDs. For example, the task service might store tasks with a group ID, and if you wanted group details (such as list of users), you would call the user service to get that information.