Migrating from a spreadsheet to a database

Limitations of traditional spreadsheets

Spreadsheets are a popular choice for storing tabular data due to their familiar, row-by-column interface and ease of use. However, they can quickly become unwieldy as data grows. Over-reliance on Excel causes a multitude of problems such as data overlap, mistaken entries, conflicts from simultaneous usage, lack of scalability, and difficulty in maintaining data integrity and security.

Switching to a relational database solves these problems by centralizing data management, ensuring data consistency, providing robust data validation, and enabling concurrent access without conflicts.

Visual DB provides a spreadsheet-like interface so that users can migrate from Excel easily, and get all the benefits of a relational database without losing the ease-of-use of spreadsheets.

How Visual DB bridges the gap

Visual DB bridges the gap by marrying the spreadsheet interface with a robust relational database. Each row in a Visual DB sheet is backed by a database record, and stored in a centralized database. This gives you data consistency, integrity, and scalability while maintaining the ease of use of a spreadsheet.

Just like in a spreadsheet, Visual DB instantly recalculates formula fields when dependent fields are updated. Totals instantly update when any value in the column is edited.

When you save, Visual DB makes sure you don't overwrite changes made by others. Visual DB also protects the integrity of a record by not mixing your updates with someone else's. These may seem like basic features, but it is surprising how many of our competitors don't have these basic protections.

Going beyond traditional spreadsheets

Visual DB is not merely a spreadsheet replacement but also offers sophisticated capabilities commonly associated with business intelligence tools. These features include but are not limited to:

• Grouping with unlimited nesting levels
• Aggregation: see summaries at each level
• Sorting on multiple columns
• Advanced filtering

Advanced filtering

Visual DB is very good at helping you locate the record you want to update. It has advanced filtering that supports combining AND/OR conditions. It also has full-text filtering that instantly updates the screen as you type.

Scalability and performance

Visual DB supports the display of up to 100,000 records simultaneously. Don't worry if your database has millions of rows. Visual DB can accommodate databases of any size by using query parameters to fetch subsets of data.

Interoperability

Relational databases often serve as crucial integration hubs linking different business applications together. However, if a database is proprietary and is behind the firewall of another company, its effectiveness as an integration hub is compromised. Visual DB allows users to bring their own databases, ensuring they have complete control and ownership. This ensures interoperability with multiple applications beyond just Visual DB. This flexibility distinguishes Visual DB from its competitors.

Moreover, Visual DB does not demand any specialized types of tables or columns for its operations. It seamlessly integrates with your existing, standard tables and columns, ensuring compatibility and facilitating its use alongside other tools.

Comparison to Airtable

Airtable is a spreadsheet-database hybrid, but it is closer to a spreadsheet than a database. Unlike a database you can't store millions or even hundreds of thousands of records in an Airtable. Airtable does not support traditional database features such as queries, query parameters, transactions or even record integrity. Multiple users can edit a record at the same time and make inconsistent changes. For example in a medication table, one user can edit the pill strength while another is editing the pill count—that can cause the patient to overdose! Or you might suffer a power outage after updating the pill strength but before updating the pill count, leaving the record in a hazardous, inconsistent state.

Visual DB avoids such problems by treating records as indivisible, and making all updates to a record atomically. Visual DB is not doing anything special—it is simply following the rules for leveraging the ACID guarantees that databases inherently provide. If you don't follow those rules you're effectively downgrading the database to a shared spreadsheet, where people may overwrite each other's changes and make conflicting changes! Isn't that what you're trying to get away from?

• Airtable only supports its own built-in database. Visual DB allows you to use your own database. Relational databases often act as integration hubs, with multiple business applications and tools accessing and updating the same data. By using your own database you ensure that the database can be used with multiple applications beyond just Visual DB.
• Like Airtable, Visual DB supports nested groups. However, the grouping feature in Visual DB is more powerful than in Airtable. For example you can group by Employee ID, and display Employee Name as group title, ensuring that two employees who happen to have the same name don't get merged. You can sort groups by any field, so not just alphabetically like in Airtable. You can also sort groups by totals.
• Forms is standout feature of Visual DB. It features automatic guides and snaps for easily laying out fields. Forms supports features such as client-side validation, setting available values using queries, and even the ability to dynamically hide and disable fields based on user input. Airtable has forms too, but it is weak, and it is not a standalone feature.
• Visual DB Reports support slicing and dicing. Lots of chart types are available including column and bar charts, pie, doughnut, treemap, scatter, bubble, line and choropleth. Visual DB provides excellent timeseries analysis with a sliding time window feature.
• Sheets, reports and forms in Visual DB get data through queries. Our query builder has excellent support for query parameters, including multi-valued and cascading parameters. This means your database can have tens of millions of rows, but you can be very precise about which subset of data you want to bring to the client. This too is not supported in Airtable.
• Visual DB supports master-detail forms, where the master record is displayed as a form and detail records are displayed in a grid. Airtable does not support traditional master-detail forms. Visual DB supports transactions, which ensures data integrity when updating master and detail records. Airtable does not support transactions.
• Visual DB supports as-you-type full-text filtering. This is important when you have tens of thousands of records and you want to quickly find the record you wish to update.

Key features

Here are the main features of the spreadsheet aspect of Visual DB:

• BYOD (Bring Your Own Database).
• Your database can be hosted in any cloud provider, including Microsoft Azure, Amazon AWS and Google cloud.
• Supports MySQL, PostgreSQL, Oracle, SQL Server.
• No code. Build sheets and forms with zero coding or SQL knowledge.
• Role-based access control: Decide who can add/remove columns and who can only enter/update data.
• Full-text filtering that instantly updates the screen as you type.
• Advanced filtering that supports combining AND/OR conditions.
• Grouping with unlimited levels of nesting.
• Unlimited number of records per table.
• Use query parameters to determine which subset of rows (out of millions) to display.
• Display columns from lookup tables, not just the main table.
• Instant scrolling of up to 100,000 rows.

Try it today with your own database.