Are you struggling with the wide database options available? Want to use the best technology for your needs? Strapping in, because we‘re going on an action-packed journey through all the mainstream database management systems on the market!
From traditional stalwarts like Oracle and MySQL to cloud-native dynamos like DynamoDB, there are 6 principal database types in wide use today:
- Relational (SQL)
- Object-Oriented
- Hierarchical
- Network
- Object-Relational
- NoSQL
With so many acronyms and tech buzzwords, the nuances can be dizzying for any data professional! This expert guide breaks it down with easy to digest explanations, examples and visuals so you can become a database expert!
A Brief History of Databases
Let‘s rewind and follow the evolution of modern databases…
In their early days, organizations stored data in primitive punched card or tape-based systems. The earliest recognizable database was the Information Management System (IMS) developed by IBM in the 1960s. IMS organized data hierarchically in parent-child fashion, revolutionizing how businesses could query information.
In 1970, relational databases burst on the scene when IBM researcher Edgar Codd published a groundbreaking paper on the relational data model. This described storing data in a logical table-based structure. Enabling queries across interconnected tables provided greater flexibility and analytical possibilities.
My friend, early tech often evolves to meet changing needs! SQL-based relational databases dominated for decades but faced challenges handling rapidly exploding amounts of unstructured data in recent years. NoSQL databases emerged with highly scalable data models and new ways to scale databases over commodity machines.
Let‘s explain and compare the 6 major DBMS types listed below. For an easy reference, I summarized the key traits in this table:
| DBMS Type | Primary Data Model | Typcial Use Cases | Examples |
|---|---|---|---|
| Relational | Tables with relations | OLTP systems, analytics | Oracle, MySQL |
| Object Database | Complex objects and classes | Multimedia, engineering data | db40 |
| Hierarchical | Tree hierarchy | Manufacturing structures, file systems | IMS |
| Network | Flexible object graph | Financial risk analysis | IDMS |
| Object-Relational | Hybrid relational + OO model | Data science, ML | PostgreSQL |
| NoSQL | Various non-tabular models | Web apps, mobile, IoT data | DynamoDB |
Now let‘s talk about each type more in-depth…
Relational Databases Rule the Enterprise
Relational databases remain the undisputed leaders for structured business data…
The secret lies within the simplicity and flexibility of tables, rows and columns. By establishing mathematical relations across tables using primary and foreign keys, it‘s easy to map complex entities and relationships.
Accessing and combining data using SQL provides exceptional performance for transactional systems while unlocking robust analytics. It‘s no surprise that 95% of Fortune 1000 companies rely on relational databases!
Beneath the Relational Data Model
But how do they pull off such magic behind the scenes? Ingenious use of indexing, data normalization and storage across both memory and drives give outstanding results.
Here‘s what‘s happening under the hood…
Transactions get optimized and stored in memory when possible, while separate dedicated storage handles reporting queries without transaction overhead. By indexing columns and normalizing data structures, queries can touch only required records while avoiding expensive table scans.
This model provides the foundation behind seminal relational technologies like Oracle, SQL Server, MySQL, Postgres and beyond…
The Pillars Supporting Business Innovation
It‘s incredible how relational databases unlocked game changing technological breakthroughs!
- 1970s – First commercial RDBMS enabled Airline Reservations Systems
- 1980s – RDBMS allows Banks to computerize and transition from paper ledgers
- 1990s – Enterprise Resource Planning (ERP) systems built on Oracle databases
- 2000s – eCommerce platforms leverage MySQL to scale transaction workload
They continue fueling waves of business innovation today — just imagine the backbone coordinating logistics for an Amazon order!
Pros, Cons and Anti-Patterns
While indispensable, even relational databases have downsides. Performance can suffer without proper database normalization and indexing. JOIN queries between large tables get costly. Object-relational mapping in code complicates development.
Most challenges happen by forcing a square peg in a round hole! Common anti-patterns include:
- Storing unstructured data like documents or IoT data streams
- Building rapidly iterating applications that require constant changes
- Analytics on massively distributed global data volumes
Modern data architectures often leverage multiple database technologies, allowing architects to use the right tools!
Onwards to other database models…
Object-Oriented Databases
Beyond corporate data lives dynamic multimedia earmarked for object-oriented databases (OODBMS). By modeling data as "objects" containing both data and behaviors, it can represent complex reality better.
The difference becomes apparent when modeling something like a Car. Tables struggle to capture all attributes elegantly. Objects encapsulate the car‘s data and methods easily — the perfect structure for evolution over time.
Class Car {
string model;
string color;
int mileage;
void drive() {
// Update mileage
}
}By hiding internal details behind an interface and allowing specialization of behaviors, effective object-oriented patterns emerge.
Killer Applications
Let‘s look at some sample use cases taking advantage of these capabilities:
- Computer Aided Design – Advanced product modeling
- Engineering Analytics – Simulation systems modeling real-world component behaviors
- Smart Products – Internet connected devices with state and behaviors
- Virtual Worlds – Games allowing user extension of behaviors
OODBs can manage consistency between systems and database naturally as objects exist in both. This persisting of model-specific data is a game changer for these applications!
Examples of Commercial OODBs
While early attempts like ObjectStore fizzled in the 90s, modern OODBMS like db4o excel in specialized domains:
Let‘s highlight two popular options:
db4o – Made open source in 2008, it persists objects in .NET and Java intelligently. Query by example and incremental automated schema evolution allow easy use.
Objectivity/DB – Provides a full-featured object database for critical systems in use by companies like Boeing. It focuses on massive scalability while ensuring ACID guarantees.
There is healthy activity in the ecosystem, with cloud-native options like Realm leading the way.
Together with ORDBMS, object databases fill a key niche where relational model strains.
Don‘t Forget Humble Hierarchical Databases!
While hyped technology comes and goes, proven approaches stand the test of time. The hierarchical model keeps dutifully working where structured master-detail data is required.
By organizing records in a tree hierarchy, they efficiently handle one-to-many relationships like part associations in manufacturing. A root node represents the parent item, while child nodes model components.
This simple, elegant way to model structures makes them ideal for:
- Manufacturing Bill of Materials – Link parts to sub-assemblies
- Supply Chain/Inventory – Link sub-inventory to overall stocks
- Organizational Model – Employees associated to managers
- Document Management – Folders containing documents
Query performance stays blazing fast by exploiting the hierarchical nature.
Vanguard Databases
Let‘s highlight two pioneering platforms that still lead this niche:
IBM IMS – Released in 1968, the granddaddy of all DBMS! Applications traditionally interact using COBOL or Java.
Windows Registry – Almost every PC utilizes this ubiquitous hierarchal database to store configuration and install details in a structured format.
While other approaches generate buzz, hierarchical systems hum along powering critical business systems!
Network Databases Traverse Complex Webs
While relational databases can model some complex data relationships, network topology better represents entities with multiple linkages and dependencies. Think nodes and edges!
Common real-world examples include:
- Network Inventory – Circuits, interfaces and hardware
- Financial Positions – Interconnected trades and risk models
- Healthcare – Patients, doctors and facilities
By providing the flexibility for records to have multiple parents and varying cardinality, better representation of complexity emerges.
This namespace functionality has always existed in seminal DBMS…
Hidden Giants Supporting Networks
Two stalwart database platforms provide robust network data foundations:
Oracle Database – Offers dedicated network data model alongside ubiquitous tables. Providing rock solid persistence, transaction processing and storage in one package.
CA IDMS – Heavily used in large enterprises like governments and financial firms for storing mission critical system entity models and metadata. Reliability reigns supreme.
While hidden from application developers, network capabilities drive critical systems every day!
Object-Relational Databases Combine Capabilities
What happens if you merge capabilities from relational and object oriented models? The object-relational database is born!
These beasts support rich data types like Java or C++ classes natively while providing SQL access. Going further by integrating advanced object concepts like inheritance and functions into schema.
The future emerges when tapping strengths of both approaches…
Two open source flagships lead the pack:
PostgreSQL – Adds classes, inheritance, functions as first-class citizens extending tables. Proven for 20+ years across startups and enterprises.
MySQL – Continually adds object-relational capabilities like geospatial and JSON support. Dominant open source OLTP and analytics option.
There is no need to choose between concepts on opposites sides of computing anymore!
NoSQL Databases Scale New Heights
The meteoric rise of internet giants like Google, Facebook and Amazon substantially strained what existing databases could handle. Scaling systems to handle user bases of billions forced rethinking how storage works.
NoSQL databases were born in the cloud to handle velocity, volume and variety of massive new datasets.
By loosening some constraints of relational models, new systems achieved unforeseen performance, scalability and flexibility. Grammatically, NoSQL = "Not Only" SQL!
Four distinct NoSQL genres exist:
- Key-Value – Simple key-value pair storage (Redis, Aerospike)
- Document – Collections of JSON documents (MongoDB, CouchDB)
- Columnar – Column-oriented storage and processing (Cassandra, HBase)
- Graph – Nodes and edges for ultimate flexibility (Neo4J, ArangoDB)
Combined, these innovations unlocked the next generation of cloud applications!
Databases Supercharging the Web
Let‘s see NoSQL database lifting popular services to new heights:
- Netflix – Recommendation engine uses Cassandra for extreme scalability
- Facebook – Invented the RocksDB column store to manage data ingest at scale
- Uber – MongoDB and other NoSQL integrate microservices handling billions of routes
- Amazon – Everything including DynamoDB key-value store runs their infrastructure
Without horizontal scaling and tunable consistency models the modern web couldn‘t exist!
Comparing SQL and NoSQL Tradeoffs
How do engineers choose between them for applications? Let‘s compare the core technical and operational differences:
| Factor | SQL Databases | NoSQL Databases |
|---|---|---|
| Data Model | Tables with relations | Multiple non-relational models |
| Data Schema | Predefined schema | Dynamic schemas |
| Scale Strategy | Vertical (See OLTP tricks above) | Horizontal partitions |
| Data Consistency | Strong consistency and ACID compliance | Eventual consistency tuneable |
| Joins | SQL performs fast joins across tables | No joins across data partitions |
| Transactions | Fully transactional with rollback | Varies greatly by system |
| Query Language | SQL provides power and flexibility | Specific to data model |
| Ecosystem | Mature with decades of technology stacks and tools | Fairly young with open source stacks |
Weighing the tradeoffs allows matching the right approach to application needs. The two can then complement each other using polyglot persistence!
Learn to Love Multiple Persistence Models!
The key insight for modern systems is there is no one database to rule them all!
Different subsystems have unique technical and business needs around:
- Data models – How real-world concepts get represented
- Scale needs – Data volumes and performance requirements
- Consistency needs – Accuracy, integrity and robustness
- Analytic needs – The types of insights required
Much like microservices break down software, polyglot persistence uses optimized storage for each component.
By combining SQL, NoSQL and other approaches you build powerful modern data architectures!
You‘re a Database Master!
Congratulations for your perseverance through a whirlwind overview of ~50 years of database innovation.
You now grasp how 6 fundamental models sculpt the data foundations for everything from e-commerce to mobile to artificial intelligence.
I hope you feel empowered in the basics behind core database system architectures. For production systems we still just scratched the tip of the iceberg around administration, optimization and implementation best practices.
With these fundamentals I wish you success crafting the data heart of your application to delight users for years to come! Please drop me a note if any questions pop up on your journey.
