103.4 - Relational and Non-Relational Databases Explained
There are two types of databases. Relational Databases and Non-Relational Databases.
Spreadsheets in general are thought of as being a good tool for Non-Relational Database applications. With the addition of script modules to spreadsheets in the mid 2000's spreadsheets can be used by skilled programmers as a tool for simple relational database applications too -- and this subtle feature has huge implications for the use of spreadsheets for small business database applications like Point of Sale systems. That said, very, very few people have in fact used them for such to this point in time as more folks were enamored with the low cost accessibility and functionality that was afforded by web based relational databases.
In order to understand non-relational databases, it is in fact easier to explain relational databases first, as they have more structure. After seeing the structure of a relational database, it's easier to identify non-relational databases and it's easier to recognize the strengths and weakness of each.
Spread sheets, when used for database applications are "Non-Relational Databases".
The sections below include:
Spreadsheets in general are thought of as being a good tool for Non-Relational Database applications. With the addition of script modules to spreadsheets in the mid 2000's spreadsheets can be used by skilled programmers as a tool for simple relational database applications too -- and this subtle feature has huge implications for the use of spreadsheets for small business database applications like Point of Sale systems. That said, very, very few people have in fact used them for such to this point in time as more folks were enamored with the low cost accessibility and functionality that was afforded by web based relational databases.
In order to understand non-relational databases, it is in fact easier to explain relational databases first, as they have more structure. After seeing the structure of a relational database, it's easier to identify non-relational databases and it's easier to recognize the strengths and weakness of each.
- Relational Databases use alpha-numeric keys referred to as "primary keys" and "foreign keys" to associate records from different data tables with each other to obtain data storage efficiency and exceptional data accuracy due to little to no redundant data. Relational Databases can be much more powerful and much more efficient than non-relational database, however, that power comes at a financial and cognitive cost. Oracle, Microsoft Sql Server, MySql and MS Access are the most common examples of relational database software solutions. Separating true database functionality from additional "features" included in these software solutions is critical to recognizing the simplicity of a database -- and it is critical to recognizing where the "database" functionality ends and where "tools for custom development" pick up. As soon as you start using database tools for custom development instead of scripting tools outside of a database, the ability to move an application from one database software platform to another becomes far more difficult.
- Non-Relational Databases are simple data lists that are not intrinsically connected to other data sets. Each table is not intrinsically linked to another. Each record is not intrinsically linked to another. The problem/drawback with non-relational databases is duplicative data and data integrity. Duplicative data can not be avoided with non-relational databases if you have parent-child relationships with records, and data integrity becomes the responsibility of the user more so than in a relational database. On a medium to large scale, the preference has been for relational databases, and they are great small scale too, BUT non-relational databases can be designed and maintained by individuals with far less skill and with far greater ease, so for individual needs, they are often times a better solution if you know how to use spreadsheets for such a task.
Spread sheets, when used for database applications are "Non-Relational Databases".
The sections below include:
- Relational Database - A mockup for a Baseline DB Example
- Non-relational Database -- For Comparison to the Relational DB above...
1) Relational Database - A Mockup for a Baseline DB Example
I've used Tabs in GSheets to present data as it might be conceptually stored and viewed in a relational database.
You will be presented with 3 tables:
The relationships are as follows: A customer places an order, and an order has line items on it. The key to relational databases is the use of a unique keys (typically numeric) to identify and link records in different data tables.
As you will see, the customer ID defined in the customers table is referenced in the Orders table, and the Order ID defined in the orders table is reference in the order details table. The way to figure out who an order detail belongs to is to climb from the detail, to the order, to the customer using the unique IDs.
You will be presented with 3 tables:
- Customers Table
- Orders Table
- Order Details Table
The relationships are as follows: A customer places an order, and an order has line items on it. The key to relational databases is the use of a unique keys (typically numeric) to identify and link records in different data tables.
As you will see, the customer ID defined in the customers table is referenced in the Orders table, and the Order ID defined in the orders table is reference in the order details table. The way to figure out who an order detail belongs to is to climb from the detail, to the order, to the customer using the unique IDs.
Generally speaking, nothing that can be calculated is stored in a relational database. So, for example, the extended cost of Order Detail ID 2345 is $60 (2x30), BUT that value is not calculated and stored. It is only calculated when it needs to be presented to a user. (this is good practice, but there is no "law" saying you can't store the value)
While this type of system is very efficient from a data storage perspective, you can see that the data itself is tough to use in raw form.
While this type of system is very efficient from a data storage perspective, you can see that the data itself is tough to use in raw form.
2) Non-relational Database -- For Comparison to the Relational DB above...
Below is the data as it might be stored by a small business person who decided to use GSheets as their database for customers and orders.
As you will see, there is a lot of duplication of data (like the customer name and dates). When you get down to line item details, it all looks very busy, BUT the average business user can create and use a Non-relational database with very little training, and that is a HUGE benefit that can NOT be overlooked.
Likewise, with the networking inherent to Google Accounts, a database in a Google Sheet makes for an amazingly powerful system as compared to what we have had with non-networked Microsoft Excel for 30 years.
The ability to use GSheets as a networked non-relational database is one of THE GIFTS of the Digital Age to small business people. While it looks a little cumbersome here, when you see what we can do to create forms for data submission and transaction recall, and reports for viewing the data outside of its raw state, things will start to make more sense with regards to uses we haven't seen enough of to date.
As you will see, there is a lot of duplication of data (like the customer name and dates). When you get down to line item details, it all looks very busy, BUT the average business user can create and use a Non-relational database with very little training, and that is a HUGE benefit that can NOT be overlooked.
Likewise, with the networking inherent to Google Accounts, a database in a Google Sheet makes for an amazingly powerful system as compared to what we have had with non-networked Microsoft Excel for 30 years.
The ability to use GSheets as a networked non-relational database is one of THE GIFTS of the Digital Age to small business people. While it looks a little cumbersome here, when you see what we can do to create forms for data submission and transaction recall, and reports for viewing the data outside of its raw state, things will start to make more sense with regards to uses we haven't seen enough of to date.
Someone who has been spoiled by the seeming simplicity of our Business websites may look at these sheets and wonder why someone might want to use this type of system, and the answer is CONTROL. If the other systems do EXACTLY what you want and need, and if they will continue to do so as the software vendor grows and changes, that is a great solution. Unfortunately, the life cycle of software is to bloat and become overly graphical to the point that you need a hieroglyphics chart for each application you use for translation... and NONE OF THAT is part of building and owning your own web based gSheets systems, and for many this is a wonderful relief.
When you see this theory put into action the potential benefits of doing this in your own Google Drive when the needs fit will become a little clearer.
When you see this theory put into action the potential benefits of doing this in your own Google Drive when the needs fit will become a little clearer.