In a previous post, I referred to relational databases. I talked about how WordPress is using MySQL and that MySQL is a relational database management system but I didn’t have a clear picture about what a relational database management system is. With this post, I want to do a deep dive into the world of relational database management systems to get a better understanding of this cornerstone in technological advancement. Yes, in my opinion, it is a cornerstone: without it, we would not be where we are now and we would not be able to take the next big steps in technological developments of the web.
What is a relational database?
A relational database is a digital database, based on the relational data model. The database uses a structure that allows to identify and access data about another piece of data in the database. A lot of times the data in a relational database is organized into structured tables with rows and columns. A very simple database can look like this:
Some very basic technical terms in this:
- Name, Country and Occupation columns are stored “string data types”: data types which elements are allowed to be mutated and of which length can be changed;
- The age column stores integer data types: data types that represent mathematical digits. These integral data types may be of different sizes and may or may not be allowed to contain negative values.
The software systems that are used to maintain relational databases are called Relational Database Management Systems (RDBMS). These software systems allow you to create, update and administer relational databases.
A practical example
To get a more practical grasp of what a Relational Database is I want to give you a practical example. Let’s say that you have an appointment with the dentist and that you need to take your dentist client card with you and your appointment card. You will probably put the appointment in your calendar and additionally you make a note to remind you that you need to bring your client card and your appointment card. I don’t think you will make a copy of your client card and your appointment card and put this in your calendar. Instead, you relate the appointment with the dentist with your client card and your appointment card.
On the day of the appointment, you check your calendar for the time of the appointment and you grab the files you need (the client card and the appointment card). This is also how a relational database works. You retrieve information from the database (in our example the date and time of the appointment) and all the items that relate to the information you require (in our example the client card and the appointment card).
Relational databases and SQL
As you can see a relational data model can be very useful. It was first introduced by Ted Codd of IBM Research in 1970. It immediately attracted a lot of attention because of its simplicity and mathematical foundation. The first commercial implementations of the relation model became available late 1970s/beginning 1980s with Oracle and IBM as the companies that first introduced RDBMS software, based on Structured Query Language/Data System (SQL/DS):
- In 1979 Oracle launched Oracle v2. SQL-based RDBMS
- In 1981 IBM released SQL/DS which was renamed in 1983 to the now known Db2
Both Oracle (currently Oracle Database 21c) and IBM (IBM Db2) are still widely used systems. Of all the current DB engines, the Oracle DB engine is the most used one.
The next step is to ask ourselves what SQL exactly is. It stands for Structured Query Language and it is a programming language that is used to communicate with data that is stored in an RDMBS. SQL is not a very hard programming language. Its syntax is very close to the English language so it’s not very hard to read, write and interpret SQL.
A lot of RDBMSs use SQL (or a variation) to access the data in the tables. SQL is often pronounced in two different ways. Some pronounce it by speaking each letter individually like “S-Q-L” and others use the word “sequel”.
Why use a relational database?
With a relational database, you can create useful information by joining specific tables. These joined tables give you the ability to understand the relationships between the data or how the tables connect. With SQL you can give commands to count, add, group, and combine queries (data records). SQL can also perform basic math, subtotal calculations, and logical transformations so an analyst can order the results by name, date, etc.
A relational database is also more flexible than other types of databases. SQL has a built-in language that allows you to create tables, add columns, rename relations and make other changes. This built-in language is called Data Definition Language (DDL). All the adjustments can be done while the database is running and while queries are being done. This flexibility is a big pro, and it’s one of the reasons why WordPress is using an RDBMS: they can’t shut down a database anytime that something needs to be adjusted and with an RDBMS this is not required.
A relational database is transactional. This means that the entire system is consistent at any given moment: that is a guarantee. This makes it very easy to export and import. This is also the case with WordPress. I can back up my site within a minute without any problems:
Popular RDBMSs
The following Database Management Systems are very popular at the moment:
- MySQL. This is the most popular open-source SQL database. It is often used for web application development and it is often accessed by using PHP. The biggest advantages of MYSQL: it is easy to use, it is inexpensive, it is reliable (up and running since 1995) and it has a big community of developers. Oracle has taken control over MySQL and that is causing a serious lag in the open-source development resulting in the problem that you might run into performance issues when scaling.
- Oracle DB. The source code is closed and Oracle DB works best for very large applications (it’s often used in the banking industry). It uses a strong combination of technology and integrated business applications, including essential functionality that has been specially built for banks. The biggest disadvantage of using Oracle is that it is not free to use. You need to pay quite a lot of money for it to use it.
- SQL Server. This is Microsoft’s own SQL Server. The source code is closed (like Oracle) and mainly big corporations use SQL Server. Like Oracle DB it’s not cheap and it becomes very expensive as you scale your application.
- SQLite. This is a very popular open-source SQL database. You are able to store an entire database in a single file. This means that you can easily store all your data locally so you don’t have to connect to your own database or to a server. SQLite is very popular for databases in cellphones, tablets, PDAs, MP3 players, smartwatches and other electronic gadgets.
Relational databases vs non-relational databases
Non-relational databases are often called NoSQL databases. They don’t use tables as relational databases do. Non-relational databases are often used when large quantities of complex and diverse data need to be organized. If you have a huge database in which each customer has their separate document, containing all of their information (name, address, order history, credit card information, payment history, etc.), it can still be stored in the same document: despite their different formats. To store this in a relational database would be a problem because all the information has to be of the same format.
Now the question is: when do you use the relational database and when do you use the non-relational database. If you answer the below points with a yes you should opt for a relational database in my opinion:
- Data needs to be structured in categories and must be very organized;
- The data has exactly the same format and this will make it easy to navigate through the data;
- Relationships can be easily defined between the data points
If you answer the below points with a yes, you should opt for a non-relational database in my opinion:
- Data is not confined to a structured group: you store data in different formats;
- You perform functions that allow greater flexibility;
- Your data and analysis need to be dynamic. In other words: You want to input a lot of variants
I also think (looking for instance into data mining) that big data requires a relational database. Most of the time these are huge chunks of information that are unsuitable for relational databases.
Final thoughts
It’s amazing to see how all dots are connected once you invest some time in a subject you don’t know anything about. I’m not an expert in (relational) databases but it’s exciting to see how it works and why it is used. All Content Management Systems are built on a relational database so a big part of Web 2.0 is fully dependent on relational databases.
I hope this information was useful and gives a better understanding of how relational databases work. Feel free to contact me if you have any questions or if you have any additional advice/tips about this subject. if you want to keep in the loop if I upload a new post, don’t forget to subscribe to receive a notification by e-mail.
