1

I have identified an issue in the design of a system and I am trying to find the right language to describe the cause of the issue so I can report it to managers, and get the issue fixed.

The problem is we have a web service syncing data by copying changes (based on a datetime of last edit column) in a student enrollments table from one system to another system. But because the sources system deletes rows when a student drops a subject, drops are not being synced.

To be able to detect the drops the system needs to maintain a state of what it has copied to the second system so it can recognize when rows have been dropped or needs to validate that they tables are in sync, but I am not sure how to describe this and reference a specific best practice or methodology to backup my argument.

Does anyone know the name of the best practices or data integrity rules that would prevent this type of logical error in the design?

user802599
  • 463
  • 2
  • 7
  • 14

1 Answers1

0

It's an issue of data integrity. You have two systems that should reflect the same data, and they don't.

Conceptually, a transaction in one system isn't truly complete until it has been applied to both systems. There are several ways to do this: replication is probably the most comprehensive, but comes with a certain amount of both system and human overhead.

The method you're using involves asynchronously applying the transactions. You finish in one system, and catch up the other system periodically. The problem you've identified is that a certain type of transaction is not being caught, and thus is never being applied to the remote system.

This means that, as it stands, the database system as a whole (taking both systems involved as one system, for this table at least), fails to be ACID compliant.

It's not Atomic; a delete transaction in the primary system fails to complete ever, thus the transaction is never completed, and never will be.

It's not Consistent (covered adequately above).

You can probably make an argument for not being Isolated or Durable, if you want to.

And, note that the individual systems may be fine; it's the fact that you've created a hybrid system, and the problem lies there.

There are a number of fixes. I know one's already in the works, but I will mention one that worked for my company: we used a delete trigger to record the deleted records, and made removing deleted records a part of the sync process.

The key is in the fact that you've got a hybrid, home-grown database system. There's nothing wrong with that (for small amounts of data, replication or log shipping entire DBs to get one table are overkill), but it is possible to miss a facet when building such a system, and your system is going to have fewer eyes on it than Oracle, MS SQL Server, or MySQL.

Hope that's of some assistance.

RDFozz
  • 11,731
  • 4
  • 25
  • 38