Most web development projects require a database. Generally, the database is the critical part of the project’s persistence layer and the place in the system architecture where data is stored for later access. In today’s development landscape, you generally have two options relational and non-relational databases. There is no black and white answer to which database type is best to use, and for many developers, this is often down too personal preference. However, you can educate yourself on the advantages and disadvantages of both types of databases in the relation vs non-relational debate and understand which scenarios are best suited for each database structure.
Popular Relational Databases: MySQL, Oracle Database, PostgreSQL, SQLite and SQLite
Relational databases are typically implemented with SQL – Structured Query Language. As the name suggests, these databases are structured where data is sorted in rows and columns within a table. Each row within this table a separate record or entry of the data in the table with its own unique key to identify it as a unique instance.
Generally, relational databases are made up of multiple unique tables. As the name suggests, in relational databases, different tables are connected or related to other tables. These links or relationships are defined with the help of keys which are the unique identifier included in each row in the table.
The unique key for each row is called the primary key, and with its help, we can create relations with the other tables simply by including it in their rows. When a key is included in another table, it is called a foreign key. By setting up primary and foreign keys, we can relate data across multiple tables using relationships that can be then queried (searched for) with SQL. Relational Database
Relational Database Advantages
- Structure: The data stored in relational databases is highly structured. This makes it easy to read as it appears looks like a table. It is also easy to validate the integrity of the data by limiting the accepted values in each row and column of the table.
- SQL: Is a powerful query language which allows for highly customisable quick and efficient searching of databases. It also allows powerful manipulation and grouping of data with INSERTION, DELETION and GROUP BY functions.
- Security: Relational databases have stricter controls over which tables are confidential and accessible. Each table can be set up with different security rules allowing for tighter control over the security of the database management system’s entirety.
- Portability: For web developers working with REST API’s that may be used across legacy applications, native mobile applications and web applications. SQL offers a solution that should work across and application architecture.
Relational Database Disadvantages
- Complex Architecture: SQL databases need to be planned around a developed business logic before they can be properly set up. This can be costly in terms of time, requires experience and knowledge of database design principles and can be painful to change is set up incorrectly.
- Rigid Data Structure: SQL databases were not designed to work with Objects and other complex and flexible stat types. Relational databases are not intended to work with unstructured data.
Popular non-relational databases: MongoDB, DynamoDB, Redis and Firebase Cloud Database
A non-relational database sometimes referred to as a NoSQL store, is an increasingly popular data storage method for web developers. Non-relational databases are set up to store unstructured data, therefore do not implement the column and row table structure seen in SQL databases. Typically, non-relational databases store data in documents and collections. This data can be semi-structured like a JSON key-value based object.
A simple way to understand non-relational databases is to think of it as a folder system with different files or collections inside the folder. You might have one folder for user details, one for user session details and another for user profile pictures.
Unlike the universal structure of SQL, there is no unified language or system used in relational databases. Each database system has a unique method for storing data. Generally, there are four popular types of non-relational databases.
- Document Stores: In document stores data is stored in “documents’’ such as a JSON, YAML or XML file, documents can be retrieved by a key. Each document inside the document store does not necessarily contain the same fields. However, schemas can ensure data in the documents is valid and of the correct format.
- Key-Value Stores: In a key-value store, each possible key only appears once within the database collection. Each key has an associated value, with this value being retrieved by its related key.
- Column-Wide Stores: Like SQL based relational databases column wide stores use tables, rows and columns to store data. Unlike SQL, there is no strict requirement for each row of data in the same table to contain the same format and data type.
- Graph Store: Graph stores are perfect when data can be mapped with relationally. They are often used to store data that can be mapped to a network structure. Each element within the graph links or relates to other elements within the graph store.
Many developers are quick to dismiss or talk down non-relational databases. Some of the biggest misconceptions around these databases are that they lack the querying, relations and data validation power SQL provides. However, their power lies within their high flexibility with the ability to choose and combine non-relational database types to serve specific roles within a web application’s overall architecture.
Non-Relational Database Advantages
- Flexibility: A non-relational database allows developers to have flexible schemas and requirements around data. They are perfect for setting-up prototypes when the business logic and data schema of a project is not fully refined that can be later scaled and validated once these requirements are finalized.
- Data Consistency: NoSQL databases commonly use JSON based data formats. This allows data to be in a consistent format between the front and back-end layers of your web application architecture.
- Quick to Setup: Most non-relational databases have easy documentation, packages and APIs to integrate with. Almost any developer can quickly set up a database without worrying about understanding the complexities of SQL and start focusing on developing a solution.
Non-Relational Database Disadvantages
- Less Mature: Although the concept of non-relational databases has been around for a while, mass adoption on an enterprise level has been slow. These databases are still an evolving and changing technology with non-relational based solutions consistently looking for improvements in their ability to scale and index data.
- Enterprise Resistance: Generally, non-relational databases are mostly open-source solutions, making them less appealing to enterprise development teams. Furthermore, unlike SQL, they don’t naturally suite well-defined business requirements often developed and refined by these organisations before starting a project.
How to choose a database
There is no golden rule to choosing a database for your web development project. The correct answer is based on the requirements of each individual project itself. The more you can understand the project’s requirements and the less likely those requirements are to change, the more likely you will come to a clear and informed choice about which database is best for your project. Each database has its own pros and cons, and no solution will ever be 100% perfect. However, hopefully, by considering the following questions, come to an informed decision for your next project.
Question the type of data in your database.
- What kind of data is being used in your project?
- Is the data types and requirements and format well defined and understood?
- Is the data fit for a tabular row and column based system or will you need some flexibility?
- Is the validity of the database in your system a high priority?
- Is the data you are storing of project critical value?
- Do natural relationships exist within your potential data set?
Question the amount of data to be stored in your database.
- Is your database going to store high amounts of data?
- Is your data going to need to be quickly quired and indexed?
- Can you easily scale the database storage capabilities at some point in the future?
- How often will your database deals with large transaction (read/write operations) volumes and will it be able to cope with peak and unexpected demands?
Question the resources available to develop your database?
- What are your developers or you most comfortable working with?
- Is there previous experience with a particular database type or structure?
- What lessons have you learnt from previous projects around database architectures?