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?