Simply speaking, a database is any structure in which data is to be arranged along with tools to store, manipulate and retrieve the data.
Several such pre-configured “data arrangements” are available as products, each developed in its time to solve data-related challenges. Let’s mention a couple.
Relational Database: In a relational database data is arranged in tables with relations between them. Using the SQL language, one can create the database tables, insert data into them and retrieve data, among other things. Relational Databases Management Systems (RDBMS) have grown to be very mature products, serving the data requirements of almost all of the information systems in the world. For more details about Relational Databases and SQL – read my Blog Post: Understanding Relational Databases: The Basics.
Graph Database: In Graph Database, first introduced in 2006, data is arranged in a Spaghetti-like scheme, in which entities (nodes) are connected between them by meaningful relationships (edges). For example, a customer can be connected to a Sales Order in a “placed” relationship, in essence telling us that the customer has placed this order. Graph Databases are ideal for related entities with frequent changes, such as describing Facebook’s map of friends.
Key-Value Databases: The basic structure here is that of pairs of key-value, for example: a phone directory listing can be thought of as a list of pairs where the key is the person’s name and the value is his phone number. The value can be a more complex structure, such as a document or an array of attributes.
Taking the above definition of a Database, you can clearly see how some useful data arrangements can be found in a Worksheet (or several Worksheets) to serve as a Database.
In fact, the magic and power of Excel stems from its versatility of functions. It can serve as a Database, a real-time calculation grid, a visualization board, a platform for developing computer programs and basically as a client, or user interface, in information systems.
Create a table in Excel to store your customers – and you have a Database.
Add another table with a Vlookup to your customers table – and you have a more sophisticated Database: linked tables.
As you recall, in my definition of a Database I mentioned that each Database product was developed to solve some data-related challenge(s).
Let’s see some of the challenges for which Excel falls short as a Database:
As you can see, some of the most needed traits of a Database serving a robust, scalable information system, are missing in Excel.
This is why for most business applications Excel wouldn’t be considered as a viable Database.
Well, we can choose a suitable Database.
As a relational Database is the suitable choice for most business applications, there are plenty of great RDBMS products to choose from, some are completely free.
Here is a typical architecture I employed for one of my customers:
Excel can serve as a great user interface communicating with a central Database. We enjoy the benefits of a robust, scalable relational Database along-side Excel built-in functionality, visualization features and programming flexibility.
If you want to learn how this is done right, even if you know nothing about relational Databases, you may want to check-out my course: Beyond Excel Boundaries with Databases & SQL.
Join today to the Excel VBA Inner Circle with Mor Sagmon.
The first to enroll as founding members will enjoy exceptional, life-long benefits and prices.
Click the button below to learn more and join.