A database is a repository in which data is stored. Databases can exist in spreadsheets, filing systems, bits of paper or anywhere information can be recorded. Our interest is online databases stored on a computer. Online databases are accessible on the internet and interacted with by software.
Online databases are a critical element of the modern internet. They are used by dynamic websites and web applications for all sorts of purposes. Just a handful of examples include:
Unless you intend to build an utterly basic static website, a database is going to come into the equation.
Online databases are built and managed with database management systems (DBMS). There are dozens of alternatives to choose from, each with pros and cons. The system you choose depends on several factors including:
The most popular DBMS is MySQL and is a good choice if you’re unsure of what to use, as it has been around for decades and is ‘battle tested’ mature software.
In the modern web, most databases have either a relational structure or an object oriented structure. Relational databases have been around for decades and are by far the most common of the two. Object oriented databases are a more recent development and allow data to be retrieved much faster, but the data is less organised.
Relational databases store data in two or more tables which are related to each other. Each table has columns which specify the type of information to be stored and rows containing entries in the data table. Below is a simple example to illustrate this; a relational database of guitarists and their guitars. Guitarists are stored in one table and their guitars are stored in another with an id
number used to establish a relationship between the two. Hence the term relational.
id | first_name | last_name |
---|---|---|
1 | James | Jones |
2 | Slash |
id | user_id | guitar |
---|---|---|
1 | 1 | Parker Fly |
2 | 1 | Ibanez Iceman |
3 | 1 | Ibanez Talman |
4 | 2 | Gibson Les Paul |
5 | 2 | B.C. Rich Mockingbird |
Data is clearly delineated into its own areas. We can set restrictions in the columns to increase data integrity. This confers high data integrity into the relational database.
Most relational database management systems use a programming language called Structured Query Language to query the database and make changes. The most popular relational database system and indeed the most popular online database system is MySQL.
Other popular relational database systems include:
All the examples above use SQL to query the databases.
Object oriented databases store data in objects analogous to the objects used in scripting languages. These databases are often called NoSQL to contrast relational databases which are mostly operated in SQL.
The most popular object oriented database management system is MongoDB. In MongoDB the equivalent expression of the data in the above relational example would be:
{ “_id” : ObjectId("54c955492b7c8eb21818bd09"), “firstname” : “James”, “lastname” : “Jones”, “guitars” : [ “Parker Fly”, “Ibanez Iceman”, “Ibanez Talman” ] }, { “_id” : ObjectId("751bc19da876a7a7cd143508"), “firstname” : “Slash”, “guitars” : [ “Gibson Les Paul”, “B.C. Rich Mockingbird” ] }
We can retrieve the guitars for a certain guitarist much faster in a NoSQL database than in an SQL database because the SQL database must perform a computationally expensive operation called a JOIN to establish the relation between two tables, while the NoSQL database can simply grab the whole entry for the guitarist. The tradeoff is that the SQL database is more organised and less prone to errors and corruption in the data.
This Digital Ocean article further explains the difference between relational and object based databases.