Software > Gsheets for Web Based Database Applications
We need to give you a short and simple view of a "typical" web based database application structure before you can understand the pros and cons of using Google Sheets for similar needs.
By the time we get to the bottom of this you will be able to see how these Google Sheets "could be built out" to much more closely mimic "typical" website database applications, but for most micro/small business users, that just adds complexity that is not needed.
There are 2 large sections to this information
By the time we get to the bottom of this you will be able to see how these Google Sheets "could be built out" to much more closely mimic "typical" website database applications, but for most micro/small business users, that just adds complexity that is not needed.
There are 2 large sections to this information
- Section 1: 4 Key Components to a Website Database Application
- Section 2: 4 Key Components mapped to Spreadsheets as Databases
- Section 3: Spreadsheet Pros and Cons as compared to Database Applications
- Section 4: Extend Spreadsheets to mimic Website Database Applications inside of Google Sheets?
- Section 5: Our Design Methodology and Philosophy
Section 1:
4 Key Components to a Website Database Application
We are going to break out conversation on this down into four key components
- A database software
- A "data table" configured for your needs in your database software
- Forms for submitting requests for adding updating and delete data from a user
- Intermediate scripts that handle the communication between the forms and the database
- Reporting functionality that enables the users to view all or parts of the data table in a desirable manner
1 - Database Software
For starters, think of database software as a glass for consuming liquids. It has no inherent value other than holding a liquid.
The four more commonly recognized and used database software systems in web based applications includes:
When these were created in the 1980s and 1990s (2000s for MySQL), only Microsoft Access offered a method of creating forms and reports within their software, and that made that software a database system and application software in one wrapper and that was/is confusing for many.
While all of these offer all kinds of "things" that extend them beyond just a glass for holding water, lets pretend we don't want to use any of that inherent functionality. Most folks who have designed database applications long enough realize that they don't typically want to use "added functionality" in a database solution as that makes it less portable to the other solutions if a change down the road is desired. Likewise, most DB software vendors want to train people to use their "added functionality" as that helps keep customers from leaving...
The four more commonly recognized and used database software systems in web based applications includes:
- Oracle
- Microsoft SQL Server
- MySQL
- Microsoft Access
When these were created in the 1980s and 1990s (2000s for MySQL), only Microsoft Access offered a method of creating forms and reports within their software, and that made that software a database system and application software in one wrapper and that was/is confusing for many.
While all of these offer all kinds of "things" that extend them beyond just a glass for holding water, lets pretend we don't want to use any of that inherent functionality. Most folks who have designed database applications long enough realize that they don't typically want to use "added functionality" in a database solution as that makes it less portable to the other solutions if a change down the road is desired. Likewise, most DB software vendors want to train people to use their "added functionality" as that helps keep customers from leaving...
2 - The Data Table
Data is ultimately stored as 1's and 0's in silica or potentially in crystals (in the future). We don't need to go that low from an understanding perspective at this time.
For most of us, stored data (1's and 0's) are presented to us in tabular form, like a spread sheet grid. We'll refer to that as a Data Table.
In addition to the Data Table view, there is a two dimensional array view which is less friendly to the eye but great for sharing data. A Comma Separated Value file is an example of that.
In the database world, you can not add, update or delete data in a Data Table View or in an two dimensional Array view. Anything that actually presents data outside of 1s and 0s or command line information is in fact a mask over the data that is otherwise 1's and 0's, so when you can edit data in a tabular form, you are already looking at a "form" that is on top of the data.
Most database software systems did/do not offer great interfaces for updating raw data in tabular form and none that I am aware of offer the ability to do mass updates of row and column data via the GUI (Graphical User Interface). Mass updates of data in database systems typically has to be done with SQL (Sequential Query Language) or something comparable.
For novice database users it is confusing as heck as to try to understand why they can't just log into a database and modify table data, especially in bulk...
One the economic benefit of database software that does not allow easy tabular data updates is data slavery. Yes... it is true, unfortunately. Our controlling types have resorted to enslaving our 1s and 0s too, and generally speaking they benefit a lot more than we do.
For most of us, stored data (1's and 0's) are presented to us in tabular form, like a spread sheet grid. We'll refer to that as a Data Table.
In addition to the Data Table view, there is a two dimensional array view which is less friendly to the eye but great for sharing data. A Comma Separated Value file is an example of that.
In the database world, you can not add, update or delete data in a Data Table View or in an two dimensional Array view. Anything that actually presents data outside of 1s and 0s or command line information is in fact a mask over the data that is otherwise 1's and 0's, so when you can edit data in a tabular form, you are already looking at a "form" that is on top of the data.
Most database software systems did/do not offer great interfaces for updating raw data in tabular form and none that I am aware of offer the ability to do mass updates of row and column data via the GUI (Graphical User Interface). Mass updates of data in database systems typically has to be done with SQL (Sequential Query Language) or something comparable.
For novice database users it is confusing as heck as to try to understand why they can't just log into a database and modify table data, especially in bulk...
- One reason is because data has no inherent structure or requirements other than what a developer puts on it. As such, if I want numbers only in a field, and if anything other than that will break my scripts, if you are allowed to enter text into a data field where I expected numbers, my software application would break.
- One reason is for security and integrity. If someone could go in and do massive data updates in tabular form, anyone with a lower skill set and nefarious interest who could gain access go do massive damage
- One reason is to control permissions to data.
- There are many other reasons...
One the economic benefit of database software that does not allow easy tabular data updates is data slavery. Yes... it is true, unfortunately. Our controlling types have resorted to enslaving our 1s and 0s too, and generally speaking they benefit a lot more than we do.
3 - Forms for Add/Update/Delete Functions
Since we can NOT easily access data in a data table to update it in web based database applications, "Forms" are used to enable adding, updating, and delete of data, both singularly and in bulk.
Add Form -- You need to add a credit card to your account with a favorite online vendor, so you click a link that allows you to add a data record. You type your data and then you 1) click process or 2) indicate you are ready for processing via another action (new since the mid 2000s) and that information is then captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Update Form -- You need to update a credit card on file with a favorite online vendor, so you click a link that allows you to update part or all of that data record. You submit data and then you 1) click process or 2) indicate you are ready for processing via another action (new since the mid 2000s) and that updated information is captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Delete "Form" -- You need to update a credit card on file with a favorite online vendor. In the olden days, they'd typically take you to the Update Form and at the bottom there would be a check mark that said "delete" or there would be two submit buttons, one that said "update" and one that said "delete", and if the checkbox was checked or the delete button was clicked, it would delete the record (with or without a warning, at the programmers discretion). These days the delete functionality is often times a link next to the data somewhere. No matter, when that delete functionality is called, the command to delete is captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Important Takeaways Regarding Forms
Add Form -- You need to add a credit card to your account with a favorite online vendor, so you click a link that allows you to add a data record. You type your data and then you 1) click process or 2) indicate you are ready for processing via another action (new since the mid 2000s) and that information is then captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Update Form -- You need to update a credit card on file with a favorite online vendor, so you click a link that allows you to update part or all of that data record. You submit data and then you 1) click process or 2) indicate you are ready for processing via another action (new since the mid 2000s) and that updated information is captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Delete "Form" -- You need to update a credit card on file with a favorite online vendor. In the olden days, they'd typically take you to the Update Form and at the bottom there would be a check mark that said "delete" or there would be two submit buttons, one that said "update" and one that said "delete", and if the checkbox was checked or the delete button was clicked, it would delete the record (with or without a warning, at the programmers discretion). These days the delete functionality is often times a link next to the data somewhere. No matter, when that delete functionality is called, the command to delete is captured by an intermediate script that calls the database and it uses communication lingo to provide the new information to the database.
Important Takeaways Regarding Forms
- You need to understand there is a "form processing" event that transpires with every database add, update or delete even if there is no obvious "form" present on the web page. All database programmers recognize forms and form processing event lingo as industry talk. You also need to realize there is an intermediate script that is required for connecting form data to a database.
4 - Intermediate Scripts
With forms, you need to understand that there is always an "intermediate script" that connects the form to the database, and that script is designed and controlled by a human being. And that script can be designed to force you to submit data the way they want it submitted with no room for deviation. While generally this is a good thing and a required thing to maintain data integrity, many look at is an opportunity to control others in an overly invasive way.
5 - Reporting Functionality
Reporting functionality is in fact a form similar but different to an add, update, delete form -- that posts to an intermediate script in a nearly identical manner as with the add, update, delete functionality. The only difference this time is that the form is requesting data for viewing purposes only. Upon receipt of that data, that intermediate script is then responsible for taking the data which is in a two dimensional array form, and it converts that most often times to a grid view of some sort.
Section 2:
4 Key Components mapped to Spreadsheets as Databases
This section provides and apples to apples comparison of the framework presented in Section 1
1 - Database Software
When we use spreadsheets as database software the requirement for forms and "intermediate script control" is tossed out the window by default, but so is some other functionality related to databases that would be nice to have, so we'll need to recreate that stuff if we find it desirable. Report generation is typically much easier with a little education.
2 - The Data Table
How do we create a Data Table in a spreadsheet? We add some column names to some columns. Very straight forward.
For what its worth, some controls and formatting can be put on cells to more closely mimic some of the controls provided by intermediate scripts and the database field controls themselves.
For what its worth, some controls and formatting can be put on cells to more closely mimic some of the controls provided by intermediate scripts and the database field controls themselves.
3 - Forms for Add/Update/Delete Functions
Question: Do we need an "add form" to create a record in a spreadsheet grid?
Question: Do we need an "update form" to create a record in a spreadsheet grid?
Question: Do we need a "delete form" to delete a record in a spreadsheet grid?
Question: Are bulk data updates easier in a spreadsheet than when using a typical database?
Question: Can an errant keystroke or a few wipe out all of your data?
- No. We just type the data into the grid. Thus we don't need a form, a submit button nor intermediate script
Question: Do we need an "update form" to create a record in a spreadsheet grid?
- No. We just select the data in the grid we want to change and we change it. No submit buttons and no intermediate script
Question: Do we need a "delete form" to delete a record in a spreadsheet grid?
- No. We just highlight a row and delete it.
Question: Are bulk data updates easier in a spreadsheet than when using a typical database?
- Heck yeah. And this is one HUGE advantage of using spreadsheets as databases. The GUI for spreadsheets is designed to accommodate all types of mass data updates visually, where as true database software is very, very limited in this area and bulk updates require a lot of coding.
Question: Can an errant keystroke or a few wipe out all of your data?
- YES. And this is why you need to be educated on spreadsheets before using them as a database, and you need to know your recovery options for when something unintended happens that you can't easily undo with Ctrl-Z.
4 - Intermediate Scripts
There are no forms, so there are no intermediate scripts.
5 - Reporting Functionality
Spreadsheets typically have strong filtering functionality built into the GUI data table management tools. Thus, no forms or intermediate scripts are needed for reporting. Columns can also be easily hidden for added functinoality. Presenting columns in a different order in a report can also be achieved via views (I think).
Fact: Often times database data is exported from database software in raw form via CSV files so users can create their own reporting, so in that sense, spreadsheets are in fact one of the preferred methods for custom user reporting in the data management world.
Fact: Often times database data is exported from database software in raw form via CSV files so users can create their own reporting, so in that sense, spreadsheets are in fact one of the preferred methods for custom user reporting in the data management world.
Section 3:
Spreadsheet Pros and Cons
First and foremost, while we are talking about Spreadsheets, this comparison only holds for Google Sheets and any other vendor now or in the future who designs fully web based spreadsheets that operate over top of a cloud based database as opposed to via distinct user files like Microsoft Excel.
You can just use a data table for all Add/Update/Delete functionality.
Pros
|
Cons
|
Section 4: Extend Spreadsheets to mimic Website Database Applications inside of Google Sheets?
Many of our data entry "needs" reflect one-to-many relationships.
Sales Order
The Typical spreadsheet workaround
But what if we could mimic Website Database Applications inside of Google Sheets?
But what if we could create a "form" that looks like something we'd see in an online application with "intermediate code" that in fact parsed form data and pasted it into two data tables much like that process transpires in a web based database application?
Sales Order
- A single sales order has multiple line items. That is a one to many relationship. This type of situation is typically handled with a "header record" that contains information that applies to the order and all line items like tranasction date, buyer name and buyer email address. Detail records are for a sales order might be 2 bottle of X and 3 bottles of y (two records, one for x and one for y). This type of situation requires the splitting of form data and a a unique ID which connects the header record to the detail records.
The Typical spreadsheet workaround
- A typical spreadsheet workaround to this is to simple list all of the detail records with the header information as well, so there is only one data table and it has a lot of duplicate information. It's not pretty, and it's not efficient but it works just fine.
But what if we could mimic Website Database Applications inside of Google Sheets?
But what if we could create a "form" that looks like something we'd see in an online application with "intermediate code" that in fact parsed form data and pasted it into two data tables much like that process transpires in a web based database application?
- The answer is we can and we did, and it works quite well. Form processing It is a good bit slower than what you've come to expect from millisecond form processing -- it may take a few seconds to process a form -- but it works pretty darn well and it has the added benefit of being totally self contained in the users Google Sheet, and the user stays fully in control of their data processing environment.