An Introduction To Databases

Something a little different this time around. Lately, both at work and in my personal projects and learnings, I’ve been dealing with a wide variety of databases. Databases in general are a core component in application infrastructure. Despite that, they get far less focus in the blogosphere than other topics like UI, UX, APIs, and the like. I get it. Databases aren’t sexy. They aren’t cool. They’re just the core that supports the entire application. Nothing important.

So what is a database? At the most basic level, a database is a digital storage system that holds all of the information that your application generates and uses. Database systems come in a variety of flavors, but they’re generally broken down into two main categories: Relational (RDBMS) and Non-Relational (NoSQL or Document DB).

Relational vs Non-Relational

So what is the difference between the two main types of databases? The biggest difference is in how they organize the data that is being stored.

With relational databases, the data is broken apart into tables of data based generally on the type of data being stored. Examples might be customers or products or colors. Further, the structure of the data is rigidly defined. You cannot add data to a record for which there is no place in the structure to place that data element.

Then the database defines relationships between the various tables. For example, a relationship would exist between a customer record in the customer table and one or more sales orders in an orders table. These relationships between the tables are what define the database as relational.

In these relational databases, generally, only the latest, or current, version of a record is kept. Unless some kind of archive is created, when a record is updated, the previous version goes away.

Examples of relational databases include Microsoft SQL Server, Oracle, PostgreSQL, and MySQL.

For non-relational databases, all related data is stored together in a single block in the storage. There aren’t separate, but related tables of data. Instead, all data is stored together in a single structure and all related data is kept together. For example, a single block of data will contain the customer record along with any sales order records that are tied to that customer.

Most modern NoSQL or document DB databases store this data in a JSON formatted structure, but it can vary. And the data structure can change from record to record. For example, in relational databases, every customer record has the exact same structure, even if those fields are blank. But in a NoSQL database, one customer might have fields that another customer does not. There is no rigid structure for any of the data, allowing your data to be much more free-form in nature.

Additionally, some of these NoSQL databases don’t even allow you to update existing records. When data needs to be updated, a new record is generated and the existing record is deleted or flagged as obsolete.

Examples of these NoSQL databases include DocumentDB, MongoDB, CouchDB, and CosmosDB.

SQL

The other biggest difference between SQL and NoSQL database systems is the method by which data is queried and updated. For relational databases, the general standard is to use a language called SQL. SQL (pronounced “Sequel” or “Es-Q-El”) stands for Structured Query Language. SQL is a language developed many, many years ago as a structured method for interrogating and updating data in a database.

The structure is fairly straightforward. You have a command, the columns of data involved, and the tables of data involved, with some additional options for when you are updating or adding data or when you need to group, filter, or sort the data. ‘

A query in its simplest form is known as a SELECT statement. For example:

SELECT * FROM Customer

This statement returns all records and all columns of data from the Customer table in the database. The generally accepted standard is that commands should be in ALL CAPS, but it’s not required.

SELECT FirstName, LastName, CustomerID FROM Customer WHERE State = 'Ohio'

In this example, adding some filtering, we’re just getting 3 columns of data from the Customer table for those customers who are in Ohio. It’s all pretty straightforward and picking up the basics of SQL is pretty easy.

The best part of SQL is that while each of the vendors (Microsoft, Oracle, IBM, etc) has some minor variations in how SQL is implemented in their systems, most of it is the same across any system that implements the language. If you learn SQL, you can work with MSSQL, or Oracle, or MySQL with little to no transition time between them.

NoSQL

The term NoSQL for the document DB style databases generally comes from the fact that most of those systems don’t use SQL to access and update the data in their data stores. While some of them do actually support using SQL, it’s generally not the primary method for accessing and updating that data.

The exact methods implemented in each system can vary and herein lies one of the weaknesses of NoSQL systems. You generally have to learn a new approach to working with each system. While there are some similarities, learning each NoSQL system is on par with learning a new development language. While there will be similarities and common core concepts, the syntax can vary widely from one to another.

Which Is Better?

**buzz** Wrong answer! Thanks for playing.

While some people would love to debate the issue until they’re literally blue in the face, the truth is that each one has its strengths and weaknesses. It all comes down to the data itself. Relational databases are better for some types of data and Non-relational databases are better for other types of data. And some data would be suited just fine to either system.

Hybrid solutions are the order of the data now. Solutions that break their data apart and take advantage of the strengths of each type of system are becoming the norm.

Generally speaking, for those data pieces that are well suited to a highly structured organizational pattern, then a relational database is typically better suited. For instance, things like a customer, an address, a product listing, a sales order, and so forth.

For pieces of data that are more unorganized, or where the structure might change frequently, then a Non-relational database would be better suited. Also, the larger the dataset involved, the more likely a NoSQL solution would be better suited. Non-relational solutions typically excel at analyzing and manipulating massive amounts of data very quickly.

Conclusion

There’s not one answer to the Relational vs Non-relational database question. It’s a matter of what works for your particular team and application environment. Each has its benefits and each has its restrictions. And no one solution works for everything.

The post An Introduction To Databases first appeared on Barret Codes.