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, preventing partial updates that would leave your records in an invalid state.

How databases prevent partial updates

A key feature offered by RDBMSs to protect data integrity is atomic updates. When you update a record, either all fields are updated together or none are—there's no possibility of updating only some fields if there's an error. This "all-or-nothing" property means that if any part of an update fails, all changes are automatically reversed, maintaining the database in a logically consistent state.

This built-in protection is one of the basic features that makes databases more reliable than spreadsheets. But to benefit from it, applications must follow some basic rules.

Why field-by-field updates are dangerous

Consider an order entry system where a sales rep is entering a new order. They specify 100 units of Product A at a negotiated price of $45 per unit (reduced from the standard $50). In a properly designed system, all order details—quantity, product, and price—are saved together as a single atomic operation.

However, in systems that update field-by-field (like Airtable and similar tools), each field is sent to the database separately as soon as you edit it. Here's what can go wrong:

The sales rep enters the quantity: 100 units (saved to database immediately)
The sales rep selects Product A (saved to database immediately)
The sales rep enters the custom price: $45
Before the price can be saved, the rep's laptop battery dies

The order is now recorded with 100 units of Product A at the standard $50 price—pulled from the product catalog since the custom price was never saved. This is $500 more than the customer agreed to pay. This inconsistent state creates billing disputes, unhappy customers, and requires manual cleanup work to identify and correct.

The same problem occurs with network interruptions, browser crashes, or any other failure that occurs between field updates. Even validation errors can cause issues—if the custom price fails validation, you're left with an order that has quantity and product but no price at all.

With atomic updates, the entire order (quantity, product, and price) is saved together or not at all. If the laptop dies or the network fails before the complete update is sent, the database remains unchanged—no partial order, no inconsistent state.

But what about operations that span multiple records?

Transactions protect related records

Atomic updates protect individual records, but many business operations involve multiple related records. For example, an order typically has a header record (customer, date, shipping address) and multiple detail records (line items with products, quantities, and prices).

A transaction is a sequence of related database changes that are treated as a single atomic operation. All changes must either complete successfully together or none occur. This prevents inconsistencies like an order header getting created without its order items, or having some line items saved while others fail.

For instance, when creating an order with five line items:

Without transactions: If there's an error on line item #3, you might end up with an order header and two line items—an incomplete, invalid order
With transactions: Either all five line items are saved with the header, or nothing is saved

Transactions are a built-in feature of all RDBMSs, but applications must explicitly use them to get this protection.

The rules for preventing partial updates

The rules for preventing partial updates are straightforward:

Rule 1: When inserting or updating records, send all changed fields in a single database operation, not field-by-field.

Rule 2: When inserting or updating records in a one-to-many relationship (like orders and line items), use transactions to ensure all related records are saved together.

Rule 3: Don't save changes until the user explicitly commits them. This gives users a chance to review their changes and ensures that incomplete edits aren't accidentally saved due to network issues or other interruptions.

As basic as these rules are, many of our competitors break them in pursuit of a spreadsheet-like 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 an auto-save experience similar to Google Sheets, it compromises data integrity in several ways:

Field-by-field updates: Each field edit triggers a separate database update, risking partial updates when connections fail
No transactions for related records: Detail records in one-to-many relationships are sent separately, not within a protecting transaction
No review before commit: Users can't verify their changes before they're permanently saved, and incomplete edits can be saved if the user navigates away or loses connectivity

Visual DB follows the rules

Updates are sent atomically—all changed fields in a single operation
Related records use transactions to ensure consistency
Users explicitly save their changes, giving them time to review and ensuring only complete, intended updates are committed
If network connectivity is lost before saving, no partial data is written to the database

This approach ensures your data remains consistent and reliable, even when network connections fail or users lose power mid-edit. Your database reflects reality—complete, valid records—not a collection of partial updates and inconsistent states.

Atomic updates and transactions protect against partial updates from a single user. But what happens when multiple users try to update the same record simultaneously?