How Visual DB protects data integrity

Data integrity is the cornerstone of Relational Database Management Systems (RDBMS). It is one of the main reasons to store data—even small amounts of data—in a database rather than shared spreadsheets. When used properly, databases ensure your data is consistent and reliable, even when simultaneously modified by multiple users. One of the ways databases achieves this is by ensuring your data cannot be partially updated in ways that would corrupt it.

How databases prevent partial updates

A key feature offered by RDBMSs to protect data integrity is transactions. A transaction is a sequence of related data changes to a database that are treated as a single atomic operation. All changes must either complete successfully together or none occur, which prevents data inconsistencies like a purchase order getting created without its order items. This “all-or-nothing” property means that if any part of a transaction fails, all changes are automatically reversed, maintaining the database in a logically consistent state.

Database operations like inserting or updating a single record are automatically atomic. When you update multiple fields in a record, either all fields are updated or none are—there's no possibility of updating only some fields if there's an error. This built-in protection is one of the basic features that makes databases more reliable than spreadsheets.

Consider a wholesale pricing system tracking products with both base price and volume discount. Management decides to adjust pricing for a major client's standing order of industrial components. One sales manager lowers the base price from $50 to $40 per unit to remain competitive, while another simultaneously increases the volume discount from 10% to 20% (thinking the base price is still $50). Without atomic updates, both changes apply, resulting in a final price of $32 per unit ($40 with 20% off) instead of the intended $36 or $40. For an order of 10,000 units, this $4-8 difference per unit means $40,000-80,000 in unintended losses on a single transaction. Protection against such partial updates is especially crucial in systems where data consistency is essential for maintaining profit margins. With atomic updates, the second user would be notified that the record has already been modified, preventing unintended combinations of changes.

Atomic updates are a built-in feature of all RDBMSs, but you must follow some basic rules to benefit from them.

Visual DB follows the rules

The rules for preventing partial updates are pretty simple:

Rule 1: When inserting or updating records, send the entire record to the database in one piece, not field by field.

Rule 2: When inserting or updating records in a one-to-many relationship, use the transaction feature offered by databases so that it can prevent partial updates.

As basic as these rules are, it is surprising to see many of our competitors break these rules in pursuit of a modern user interface. Their products don't have a Save button. As soon as you edit a field they send the update to the database. While this creates a spreadsheet-like experience, it compromises data integrity. Similarly, when updating one-to-many records, they don't use a transaction. Rather they send the detail records field by field.

When you invest in a database solution, you deserve the full benefits of RDBMS technology—particularly its robust data integrity features. That's why we've designed Visual DB to fully leverage atomic transactions and ensure complete record-level consistency.

To protect the integrity of your data, Visual DB implements a proven approach: Changes are held until you click Save, then the entire record is sent to the database as a single unit. This has three benefits: First, we avoid partial updates—the way databases were designed to do. Second, you can review your changes before committing them. And third, we avoid overwriting other users' changes.

When evaluating database solutions, consider how they handle data updates and what that means for your organization's data integrity. Ask yourself:

• How critical is data consistency for your operations?
• What would be the impact of partial or conflicting updates in your system?
• How important is it for users to not overwrite each other's changes?
• Does your team need the ability to review changes before they're committed?

Visual DB's Save-based approach ensures data integrity while giving users control over their changes. We invite you to explore how our commitment to database best practices can help protect your valuable data.