Approach for comparing versions of hierarchy

Question

I'm looking for a language agnostic approach for the following. We are prototyping this in both relational and graph DBs, so, again I'm open to hearing all opinions with respect to those.

Here is the scenario:
- User views portion of org chart
- User edits org chart so that some people report to new manager
- Users sees view of old and new hierarchies side-by-side
- New hierarchy is persisted so that it can be viewed by other users

What I'm currently thinking is that I'll try to somehow utilize Rails with the gem paperclip, but the first problem I'm running in to is ensuring that the nested Employees are of the same version as the parent at the top of the chart. Again, any specific advice here would be appreciated.

Please post any more questions you've got.

Answer 1

I think this depends on whether (A) your structure is small enough to easily fit in memory, and (B) whether you need to support lots of different parts being edited at once by different people.

With that in mind, I think the simplest solution is to treat the relation-web as a single big unit (an "aggregate root"). It is responsible for recording relationships only, while the individual Person/Employee nodes that it points to live their own lives, and are responsible for recording names, titles, etc.

1. Your "official copy" of a graph has a version-number for optimistic concurrency, a single number (or timestamp.)
2. When the user wants to start making edits, a snapshot is made and placed into another object which belongs just to that user, e.g. a "Reorganization Draft" or something like that.
3. The user edits and plays with their draft through a user-interface.
4. When the user tries to save their draft, check that the version-number graph hasn't been unexpectedly changed. If they are inconsistent, tell the user: "Oops, somebody else made another change. Please start over."
5. If the versions are OK, then take the structure from their draft and apply it to the official-copy in a big transaction, also incrementing the version-number.

Other thoughts:

• The user's "draft" doesn't need to be more than a single row in some table, you can pack all the relationship-data into an XML or JSON field, since at that point it's just something they're playing with, nobody else needs to query on it.
• You might choose to represent the user's draft as a fixed snapshot of the graph plus a series of changes, which would allow you to easily apply the users' edits to the official copy. Bonus: "undo" functionality in the UI.
• If you need to combine editing the graph and editing user-details, then store a second layer of information in the draft for that, and make your saving checks/transaction a little more complex to cover both kinds of changes.
• While you may start with just one graph for an org-chart, this scheme also allows you to store other kinds of webs, such as "who gets their alerts when they are on vacation."