Good data is the cornerstone of any efficient operation — it uncovers valuable insights, informs decision-making and improves processes. It also records and tabulates activities and serves as a reference of record for all operations. But as companies collect more data, one of the biggest challenges they face is organizing and making sense of it all.
This is where a database comes in.
Whether you’re a frontline employee, a manager or a senior executive, learning about databases can help ensure that you have the right setup to fit your needs.
In this article, we’ll explain what a database is and how it works. We’ll also cover the different types of databases available and how they’re used. Finally, we’ll look at the most popular database management systems on the market.
[CTA_MODULE]
What is a database?
A database is an organized collection of data stored in a computer. The most common types store data in rows and columns in a series of tables. This makes storing, managing and retrieving the data you need easier.
Databases power nearly everything you do online and even offline to a certain extent. When you browse a social media platform like Facebook, data in your feed is pulled from a massive database. When you go grocery shopping, data about your purchases are entered into a database where they can be accessed and analyzed for insights.
The type of data stored in a database depends on the application that’s using it. For example, a database for an online store might store:
- Customer data (names, addresses, etc.)
- Product data (colors, prices, etc.)
- Business data (sales, inventory, etc.)
- Market data (competition, weather, etc.)
We can classify processing this data in two ways:
- Transactional: Transactional data is data your company creates during regular operations — purchases, returns, inventory, etc. This data is typically captured using Online Transaction Processing (OLTP) systems.
- Analytical: Analytical data is data that informs decision-making. It helps your company find new insights and make more accurate forecasts. This data is typically stored in Online Analytical Processing (OLAP) systems.
Transactional databases are meant to record activities in real-time, record-by-record and form the backbone for digital applications of all kinds. Meanwhile, analytical databases are used to analyze data by aggregating and summarizing huge numbers of records at a time.
No matter what type of data you’re working with, it’s important that you choose the right type of setup to fit your needs. For example, you’ll use an analytical database to store data like sales performance and inventory levels to run queries and create reports.
Learn more about the differences between OLTP and OLAP systems here.
Components of a database
A typical database is made up of five major components: Hardware, software, data, procedure and database access language.
Let’s take a look at these components in more detail.
Hardware
Hardware refers to the physical devices that house a database. It includes computers, servers, hard drives, processors and networking devices.
Software
Database software lets you create, edit and manage files contained in your database. It handles reporting, multi-access control and security. Database software also includes the operating system and the network software used to share the data.
Data
Data is the actual information that you’re storing in a database. There are three types of data you can store:
- Structured: Structured data follows a well-defined structure; it’s formatted and easily searchable. Examples include product names, prices and weights.
- Semi-structured: Semi-structured data doesn’t follow a strict format or conform to a set data model. However, it still has some structural elements to it like tags and metadata.
- Unstructured: Unstructured data can’t be easily arranged or formatted to fit conventional data models. Examples include video, audio and images.
Data access language
Data access language is the programming language that you use to access, update, delete and retrieve data in a database. The most common database language is Structured Query Language (SQL), which was originally developed in the 1970s by IBM.
Procedures
Finally, procedures are a set of rules or instructions that you set for a database. These define how specific actions should be carried out.
What are databases used for?
As more companies shift their processes to digital platforms, databases increasingly play a vital role in handling mission-critical data.
Here are some of the different ways that companies use databases.
Build applications
No matter what type of application you’re building, it needs a database. Take a simple calendar app — you’ll need a database to store data like events, start and end dates, notes and more.
Record business transactions
Companies deploy transactional databases to record transactions in real time. These types of databases are typically row stores, meaning that data is stored in rows instead of columns.
Here’s a simple example of a transactional database:
Businesses like retail stores, banks and restaurants use these types of databases as they offer a flexible solution for storing and managing data.
Manage customer data
As your company grows, relying on spreadsheets to track customer information just isn’t practical. Ninety-one percent of companies with 10 or more employees now use customer relationship management (CRM) software.
CRM systems serve as a database that companies can use to manage and organize customer-related data. It’s all organized in one place, making it easier for teams to find the data they need without needing to switch between spreadsheets.
Support business intelligence
Business intelligence helps companies gain valuable insights from their data and make informed decisions. Examples include analyzing performance metrics to find ways to optimize processes.
To support these initiatives, companies rely on analytical databases specifically built to aggregate and process large volumes of data. They allow analysts to run queries and create detailed reports.
Relational vs. non-relational databases: What's the difference?
There are two main types of databases: relational and non-relational.
Understanding the strengths and weaknesses of each will help you choose the right one for your company.
Relational databases
A relational database is also known as a SQL database. Relational database systems were first conceived and named in 1970 by Edgar F. Codd at IBM. They use SQL to manage data and perform queries. The data in a relational database is organized according to a schema — a “blueprint” that describes how it will store data.
Relational databases are organized in rows and columns in a series of tables. The data within these tables relate to each other, hence why it’s called relational! For example, one table might contain customer information, and another table might contain related information like purchase orders.
Information from multiple tables in a relational database are linked through “primary keys” — a unique value that identifies specific rows in a table. When a primary key is added to another table to link information, it’s called a “foreign key.”
The primary key for this table is “CustomerID:”
| CustomerID | Name |
|---|---|
| 1 | John Smith |
| 2 | Jane Smith |
Here’s another table that organizes invoices:
| InvoiceNo | CustomerID | Total |
|---|---|---|
| 1 | 1 | $100 |
| 2 | 1 | $150 |
| 3 | 2 | $200 |
The second table references data in the first table two times. The “CustomerID” key is what makes this relationship possible.
Of course, this is a rather basic example. Companies often deploy relational databases with hundreds or even thousands of tables that are all linked by primary and foreign keys.
Popular relational databases include:
Advantages of relational databases:
- ACID compliant: Atomicity, Consistency, Isolation and Durability (ACID) compliance guarantees transactional consistency. If one part of a transaction fails, the whole transaction fails, and the database reverts to a previous iteration.
- Normalization: Normalization reduces data redundancy and improves data integrity because it enables you to efficiently organize your data. This reduces storage costs.
- Data accuracy: The use of primary and foreign keys prevents duplicate records. This ensures a high level of data accuracy compared to other types of databases.
- Reliability: Relational databases use SQL, which is a widely used and established programming language with tons of support.
Disadvantages of relational databases:
- High cost: Setting up a relational database is costly. You’ll need to purchase the hardware and software. You’ll also need to hire a technician to set up and maintain the system, which is expensive, especially for small businesses.
- Lack of scalability: Data in a relational database is stored in rows and columns, which takes up a lot of physical memory. Scaling vertically (adding more resources to a server) is possible, but it also makes your setup more expensive.
- Performance issues: Adding more data to a relational database increases its complexity, which can lead to slower response times.
- Data limitations: Relational databases are great for storing structured data. But they’re not ideal if you need to store unstructured data like images and text files.
Non-relational databases
Non-relational databases are also known as NoSQL databases. Unlike relational databases that store data in tables, non-relational databases don’t have a common structured schema. Instead, data is stored in a non-tabular format.
The advantage here is that you don’t need to define a data model upfront. If you’re working with datasets that aren’t clearly defined, meaning they’re not ordered or structured, non-relational databases offer a more flexible data storage solution than relational databases.
There are four types of NoSQL databases: document, graph, key-value and column.
Here’s a closer look at each:
- Document: Document-oriented databases have a flexible schema. They store data as a JSON document — a standard text-based format for storing different data types. Information about an object and its related metadata are stored in a single document.
- Graph: Graph databases (or semantic databases) have a graph structure. They store data in nodes and display their relationship in edges. This type of database offers a flexible way to store data because you can keep adding new nodes and edges.
- Key-value: Key-value databases are one of the simplest NoSQL databases. They map keys to values which can be data types like numbers, strings or complex objects. They fetch data by retrieving the value associated with a unique key.
- Column: Column databases are similar to relational databases in that they store data in tables, columns and rows. However, the names and format of the columns can vary for each row. This type of database is good for big data and storing analytical data, as it works well for storing large data sets.
Advantages of non-relational databases:
- Scalable: Non-relational databases are highly scalable. Instead of scaling up, you can more easily scale horizontally by adding more servers to meet your needs. You can scale horizontally with relational databases too, but it’s harder (and more expensive) to do.
- Flexible data structure: Relational databases limit the type of data you can store. With non-relational databases, you can store a variety of data types — text, emails, images, video and even audio.
- High-speed ingestion: With the right setup, non-relational databases are capable of ingesting large volumes of data from multiple sources. Teams can access and extract value from their data more quickly.
Disadvantages of non-relational databases:
- Standardization challenges: One downside of non-relational databases is the lack of standardization. There isn’t a standard that defines their roles and rules. The query language also varies widely for different non-relational databases.
- No ACID compliance: ACID compliance ensures transactional consistency. However, not all non-relational databases have ACID support, so this can affect the integrity of your data.
- Rise of dark data: While non-relational databases make it easy to ingest large volumes of data, it can also lead to dark data — unused data that companies store but fail to derive any value from. This can lead to data quality issues and increase storage costs.
Relational and non-relational databases have their strengths and weaknesses. Which one you choose will depend on the type of data you’re working with and its intended purpose.
What is a database management system (DBMS)?
A database management system (DBMS) is software used to manage databases. It provides a user interface to store, access and manipulate data.
With a DBMS, users can define the database’s structure, configure user accounts, monitor performance, manage backups, restore databases to a previous iteration and more.
Database administrators can use a DBMS to limit what users can access. This prevents users from accessing data they’re not authorized to view.
Here’s a look at the common types of DBMS.
Distributed
A distributed DBMS lets you manage a distributed database. It makes multiple databases appear and function as a single database. Any changes made to one database are also made to others in the network.
Hierarchical
A hierarchical database stores data in a tree-like or parent-child structure. The data is stored as records or nodes which are connected through links. A hierarchical DBMS lets you create and manage this type of database.
Network
A network DBMS lets you manage network databases, which is a model that allows records to be linked to multiple primary and secondary records. Interconnected data points within the network model support one-to-many and many-to-many relationships.
Relational
A relational database management system (RDBMS) lets you store, manage and retrieve data in a relational database. It forms the basis for modern database systems like Microsoft SQL Server, Oracle and MySQL.
Object-oriented
An object-oriented DBMS stores data in the form of objects. It works with an object-oriented programming language like Java to store and retrieve object-oriented data. It provides an object identifier, which makes it easy for users to quickly find the objects they need.
5 popular database management systems
Here we’ll look at the most popular DBMS worldwide. Many of these support both SQL and NoSQL data formats.
While these are the most popular database management systems, the one you choose will depend on factors like the type of data you need to store, its intended purpose and more.
Let’s take a look.
1. Oracle Database
Oracle Database is a widely-used relational database management system developed and run by the Oracle Corporation. It supports SQL to interact with the database. While the company has traditionally offered RDBMS solutions, it also offers a NoSQL cloud data solution that lets developers build applications using various database models.
Pros
- High performance: Features like Real Application Clusters (RAC) let users scale database performance across multiple servers and increase data availability.
- Flexibility: Oracle Database gives you flexible deployment options, allowing you to deploy your database on-premise or in the cloud.
Cons
- High cost: Oracle Database is on the high-end side in terms of cost, making it harder for smaller organizations to afford their solution.
- Learning curve: Oracle Database has a high learning curve. You’ll likely need to hire a certified engineer to set up and configure your database.
Use case
Oracle Database offers a great solution for online transaction processing (OLTP) and data warehousing.
2. MySQL
MySQL is another widely used RDBMS. It was initially created and developed by MySQL AB before it was acquired by Sun Microsystems (which is now Oracle Corporation). Even though MySQL is open-source, it continues to receive frequent security and feature updates.
Pros
- Free installation: The Community Edition is available as a free download, and it’s supported by a huge community of developers.
- Cloud compatibility: MySQL is supported by popular cloud providers, such as Amazon Web Services (AWS), Google Cloud Platform (GCP) and Microsoft Azure, making it an attractive option for companies that want to bring their database to the cloud.
Cons
- Partial support: Technical support is only available with the paid versions of MySQL, meaning you’ll have to troubleshoot your own issues if you’re on the Community Edition.
- Limited features: Certain features, like the ability to make incremental and differential backups, are only available with the paid versions of MySQL.
Ideal for
MySQL is ideal for building small, web-based applications.
3. Microsoft SQL Server
Microsoft SQL Server (or SQL Server) is a commercial RDBMS designed and developed by Microsoft. It can be deployed on-premise or in the cloud. Developers use the Transact-SQL (T-SQL) language to interact with the SQL Server databases and manage or query their data.
Pros
- Various editions: There are five editions of Microsoft SQL Server with different features available to suit your business needs. The Standard edition provides basic data management, while the Express edition lets you build small data-driven applications.
- Excellent support: With Microsoft Premier support, you get access to a team of specialists who can help you sort any issue out.
Cons
- High cost: Microsoft SQL Server is one of the more expensive DBMS solutions. The Enterprise edition costs $13,748 and is sold as a 2 core pack.
- Complicated licensing: Licenses for Microsoft SQL server can be difficult to understand. Complicating matters even further is that licenses tend to change from one to another.
Ideal for
Microsoft SQL Server is a good option if you’re looking for enterprise-level capabilities.
4. PostgreSQL
PostgreSQL is a powerful, open-source DBMS that supports both relational and non-relational data formats. It’s compatible with most operating systems, including Linux. It works with both on-site and cloud-based servers. With its massive community, PostgreSQL continues to receive regular updates and new features.
Pros
- Support for a range of data types: Out of the box, PostgreSQL supports a range of data types like arrays, JSON, XML, H-Store and more.
- Highly ACID compliant: PostgreSQL ranks as one of the best ACID-compliant databases, above MySQL and Oracle.
Cons
- Lack of documentation: PostgreSQL has support from a large community, but its documentation isn’t as extensive as other database engines. This can be a problem if you run into issues.
- Low reading speed: Depending on the size of the database, you may experience some slowdowns with read-only operations when using PostgreSQL.
Ideal for
PostgreSQL is a great choice for Online Transaction Processing (OLTP) because of its strong analytical capabilities and high ACID compliance.
5. MongoDB
MongoDB is an open-source, document-based DBMS designed for applications that use unstructured data. It can also work with structured data, although it wasn’t initially built for this purpose. MongoDB connects databases with applications via database drivers.
Pros
- Highly scalable: MongoDB is a scalable DBMS with horizontal scaling built-in, making it capable of high-volume data storage.
- NoSQL support: MongoDB supports both JSON and NoSQL data. Its flexible data scheme allows you to choose a data model that meets your needs.
Cons
- Lack of security: Some settings on MongoDB aren’t secure. For example, user authentication isn’t enabled by default. Some security features are only available on the Enterprise edition.
- Slow performance: MongoDB wasn’t initially designed for relational data, so performance may take a hit with these data models.
Ideal for
MongoDB is a great option if you need a NoSQL document database for applications that don’t need a strict structure.
Choosing a database management system will depend on various factors. Here are some things to consider:
- Data type: Do you need to store unstructured or structured data?
- Scalability: How much data do you need to store?
- Deployment: Will you deploy a database on-premise or in the cloud?
- Integration: Does the system integrate with your applications?
- Security: What measures are in place to secure your data?
- Hardware compatibility: Does the DBMS work with your setup?
- Technical support: What kind of support do you need?
- Costs: How much can you afford to spend?
Centralize your data with Fivetran's powerful connectors
At their core, databases are designed to store and retrieve data. But perhaps their greatest value is the insights that you can derive from querying them.
Whether you’re looking to build a transactional database to record activities or an analytical database for business intelligence, you’ll need a way to pull data from different sources and load it into a destination of your choice. Fivetran offers powerful data source connectors that enable you to effortlessly centralize your data and derive valuable insights.
There’s a good chance that your company is already storing transactional data like sales data in a database. Check out our “guide to effective transactional database analytics” to learn about the kind of analytics that are performed with these types of databases, common integration challenges and how Fivetran can help you address them.
[CTA_MODULE]
.svg)
.svg)
.svg)