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?

1
  • The issue is being fixed but is an issue that I have seen come up before and I have tried to design to avoid by recording the transactional changes instead of just using a date modified column and been told not to do that because it over complicates things and just to follow the internal developer guide lines. I would like to be able to read up on this more so I can sound like I know what I am talking about before trying to get this changed. So the issue I am trying to get fixed is really that our internal developer guidelines (or my interpretation of them) allow for this type of error.
    – user802599
    Commented Jun 4, 2017 at 23:01

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.