In the previous lesson we reached a clear conclusion: BiblioRed needs a database. But "database" does not name a single thing. There are families that differ enormously from one another, each with its own way of structuring information, born to solve a specific problem. Choosing badly at this stage is expensive: it means forcing a tool, for years, to do something it was never meant to do.

This lesson is an overview: a map of the territory. We will not go into detail on any family —that belongs to module 2 for relational and module 3 for NoSQL—; the aim is that you know what exists, what shape each option has and by which criteria you choose. At the end we will apply those criteria to BiblioRed and decide, with arguments, which technology we will use in each part of the system.

1. How databases are classified
2. Relational databases (RDBMS)
3. The NoSQL landscape: the four families
4. Historical models: hierarchical and network
5. Other specialized models
6. OLTP versus OLAP
7. General comparison table
8. One code example per family
9. How to choose: decision criteria
10. The decision applied to BiblioRed
11. Common mistakes and tips
12. Exercises
13. Conclusion

Databases can be classified along several axes, and it is best not to mix them up:

• By data model: how the information is structured. This is the main axis (relational, document, key-value, columnar, graph, hierarchical, network, object).
• By workload: what it is used for (OLTP, day-to-day operations, versus OLAP, analysis).
• By deployment architecture: client-server versus embedded; single-node versus distributed; self-managed versus managed cloud service.
• By where the data lives: on disk versus in memory.

Any given database occupies a position on every axis. PostgreSQL is relational, OLTP, client-server and on disk. SQLite is relational, OLTP, embedded and on disk. Redis is key-value, in memory and client-server. These are not mutually exclusive categories.

In this lesson we will mostly travel along the data model axis, and we will close with OLTP/OLAP because it is the distinction that drives the most architectural decisions.

They have been the dominant family since the eighties and they are the backbone of this course.

How they structure data: in tables made of rows and columns. Each table represents an entity, each row an occurrence, and tables are connected through keys (a column in one table pointing to the identifier of another). The schema is defined up front: before anything is stored, you declare which columns exist and of what type.

What they guarantee: strict integrity (the manager rejects data that violates the rules), ACID transactions (all or nothing, even in the face of failures) and a standard, extremely powerful query language, SQL.

What they are good for: any domain where the data has a stable, well-known structure, where the relationships matter and where correctness is not negotiable. Banking, invoicing, inventory management, ERP, bookings... and libraries.

When they hurt: when the schema changes constantly, when the volume demands spreading across hundreds of machines, or when the natural shape of the data is deeply hierarchical and nested.

Representative products: PostgreSQL, MySQL/MariaDB, SQLite, Oracle Database, Microsoft SQL Server, IBM Db2.

In this course, PostgreSQL will be the main reference and SQLite the lightweight alternative for practising. The formal relational model and SQL make up the whole of module 2.

"NoSQL" is an unfortunate name —it is better understood as Not Only SQL— that groups together systems that emerged from 2007 onwards with one trait in common: they give up some guarantee or some rigidity of the relational model in exchange for schema flexibility or the ability to distribute. They are not one model, but four families that differ greatly from each other.

Here we will only look at what they are and what they are for. The detail of each family, their internal structures and their operations are lesson 03-02, and how data is modelled in them, lesson 03-03.

They store self-contained documents, typically in JSON or BSON, grouped into collections. Each document can have different fields from its neighbours, and can contain nested structures (lists, objects inside objects).

• Good for: data with a variable or hierarchical structure that is almost always read whole: product catalogs, profiles, editorial content, reviews.
• Products: MongoDB (our example in module 3), CouchDB, Amazon DocumentDB. PostgreSQL also stores JSON with its jsonb type.

The simplest model possible: a giant dictionary. A unique key points to a value, and the system knows next to nothing about the structure of that value.

• Good for: extremely fast access by a known key. Caches, user sessions, counters, queues, rate limits.
• Products: Redis, Memcached, Amazon DynamoDB (in its simplest use), etcd.

They organize the data by columns instead of by rows, and they are designed to be spread across many nodes. They allow massive writes and reads over enormous ranges.

• Good for: high-volume event streams, telemetry, histories that keep growing and are queried by range.
• Products: Apache Cassandra, HBase, ScyllaDB. (Careful: the term "columnar" is also used for analytical stores such as ClickHouse; the kinship is real but the purpose differs.)

They explicitly model nodes and relationships between them, and both can carry properties. Traversing relationships is the cheap operation.

• Good for: social networks, recommendations, fraud detection, knowledge graphs, route analysis. Anything where the interesting question is "how is A connected to B?".
• Products: Neo4j, Amazon Neptune, ArangoDB.

These two models preceded the relational one and are practically unused in new projects today, but they live on in legacy systems in banking, insurance and public administration. The course will not come back to them, so it is worth knowing what they are.

Hierarchical model (1960s). It organizes data in a tree structure: each record has a single parent and may have several children. Access is by navigating down from the root.

• Advantage: very fast for queries that follow the hierarchy.
• Limitation: a child cannot have two parents, so "many-to-many" relationships force you to duplicate data. In BiblioRed, a book written by two authors already breaks the model.
• Flagship product: IBM IMS.

Network model (CODASYL, 1970s). It generalises the hierarchical one by letting a record have several parents, through explicit pointers.

• Advantage: it represents many-to-many without duplication.
• Limitation: the programmer has to navigate the pointers by hand. Changing the structure forces you to rewrite the programs.
• Flagship product: IDMS.

The problem common to both: there is no data independence. The way you access is welded to the way you store. That is exactly the shortcoming the relational model attacked, as we will see in lesson 01-03.

Families that exist, that you may run into in practice and that this course will not revisit.

They store objects exactly as they exist in a programming language, inheritance and methods included, avoiding the translation between objects and tables. They promised a great deal in the nineties and saw limited adoption: the relational ecosystem was too solid and ORMs appeared, solving the problem in a less radical way. Products: db4o, ObjectDB. PostgreSQL retains object-relational traits (user-defined types, table inheritance).

These are not a model but an architecture: the engine is a library linked inside the application, with no server process and no network. The database is a local file.

• Good for: desktop and mobile applications, devices, application file formats, automated tests and learning.
• Products: SQLite (the most widely deployed in the world: it is in every phone and every browser), DuckDB, LevelDB.

We will use SQLite in this course precisely for this reason: it lets you practise real SQL without installing or administering a server.

They keep the whole dataset in RAM, with optional persistence to disk. They gain one or two orders of magnitude in latency at the cost of being limited by the available memory and of accepting a risk of loss if they do not persist.

• Good for: caches, real-time leaderboards, sessions, job queues.
• Products: Redis, Memcached, SAP HANA (in-memory analytics).

Specialized in data indexed by time that arrives continuously and is almost never modified. They optimize compression, sequential writes and queries by time window, and they usually build in aggregations and retention policies.

• Good for: system metrics, IoT sensors, market prices, energy consumption.
• Products: InfluxDB, TimescaleDB (a PostgreSQL extension), Prometheus.

The most recent of all. They store high-dimensional vectors (embeddings) that represent the meaning of text, images or audio, and their characteristic operation is similarity search: given a vector, find the most similar ones. They do not look for exact matches, but for semantic proximity.

• Good for: semantic search, content-based recommendation, systems that retrieve relevant documents to feed a language model.
• Products: Pinecone, Milvus, Qdrant, Weaviate, and PostgreSQL's pgvector extension.

Their arrival, very recent, will be placed historically in lesson 01-03.

This distinction is not about the data model but about the workload, and it explains why many organizations have two databases holding the same data.

• OLTP (Online Transaction Processing): the day-to-day operational system. Vast numbers of small, concurrent operations: recording a loan, signing up a member, updating a status. It prioritizes low latency per operation and transactional integrity. It is designed normalized (module 5).
• OLAP (Online Analytical Processing) and data warehouse: the analytical system. Few queries, but enormous ones: aggregating millions of rows by dimensions. It prioritizes bulk read performance. It is designed denormalised (lesson 05-04), often with columnar storage.

BiblioRed will start with OLTP only. If in a few years management wants dashboards covering a decade of history, copying the data periodically into an analytical store will be considered. That is a normal evolution, not a failure of the initial design.

The purpose of this section is purely visual: to let you see the difference in shape between the models. You do not need to understand the syntax yet; each one is explained in its own module.

The loan data is spread across tables and reassembled at query time:

What matters for this lesson: the member's data lives in members, the book's in books, and the query brings them together on demand. Every fact is written down once. The full syntax of SELECT and JOIN arrives in lessons 02-03 and 02-04.

The same information, but self-contained in a document:

Differences in shape you can already appreciate:

• There are no tables or columns: there are fields, and they can be nested (book.title).
• tags is a list inside the document itself. In the relational model that would require a separate table.
• Another document in the same collection could have different fields (for example, one with spoiler: true) and nobody would complain. That is the flexible schema.
• The price of that convenience is that the book title is repeated in every review. When it is worth paying is exactly what lesson 03-03 studies.

There are no queries here: there is direct access by key. You cannot ask "give me every session for the north branch" without scanning everything, because the system does not know what is inside the value. In exchange, each operation takes microseconds.

The distinctive part: the arrows are part of the query. -[:READ]-> is an explicit stored relationship, not a computed join. Questions of the type "who connects to whom through what" are expressed directly; in SQL they would require several chained JOINs and get very complicated when the depth is variable.

This is the part that really matters in a project. The criteria, in order of practical weight:

1. What shape is the data? A stable, well-known structure, or one that varies from record to record? Flat or deeply nested?
2. What questions are you going to ask? This criterion decides more than the previous one. If the queries cross five entities and aggregate, you want relational. If you always read a whole object by its identifier, a document or key-value store is enough.
3. How much does integrity matter? If bad data has legal or financial consequences, you want a system that prevents it by construction.
4. What volume and what growth? Millions of rows are no problem for PostgreSQL. Hundreds of geographically distributed terabytes do demand something else.
5. Will the schema change often? A schema that changes every week suffers in a rigid model.
6. What does the team know and what can it operate? A badly administered distributed system is worse than a simple, well-administered one. This criterion is underrated and decisive.

And two rules of thumb that save a lot of grief:

• Relational by default. It is the option with the most tooling, the most documentation, the most people who know it and the most guarantees. Departing from it needs justification, not the other way round.
• It is not a single decision. One system can use PostgreSQL for the transactional side, Redis for caching and MongoDB for content. It is called polyglot persistence and it is the subject of lesson 08-03.

This diagram is a deliberate simplification: it works as a first approximation, not as a verdict.

Let's apply the criteria to our case, part by part.

Members, books, copies, authors, loans, reservations and branches form a classically relational domain:

• The structure is stable: a member always has the same data; so does a loan.
• The relationships are the heart of the system: a loan makes no sense without a member and a copy.
• Integrity is critical: there cannot be a loan of a copy that does not exist, nor the same copy lent out twice at once.
• We need concurrency with transactions: four front desks lending from the same collection at the same time.
• The volume is modest: 12,000 members and a few hundred thousand loans a year are figures PostgreSQL handles without breaking a sweat.

Decision: PostgreSQL for the whole core, and SQLite as an equivalent environment for practising. That is what we will build in modules 2, 4 and 5.

The reviews readers leave about books, and the enriched catalog record (synopsis, covers, awards, free-form tags, external links), have a different profile:

• Variable structure: some reviews have a rating, others only text; some records have awards, others do not.
• They are read whole and in one go, to render a page; they are not crossed with five tables.
• Integrity is less critical: a review with an odd field breaks nothing.
• They fit naturally with nested lists (tags, editions, links).

Decision: MongoDB for reviews, enriched catalog and activity log. That is what we will work on in module 3 and in case study 08-02.

None of these pieces will be implemented in the course, but it is useful to see the complete map: a real system ends up combining several technologies, each one where it adds value.

• Choosing NoSQL because "it is modern". NoSQL is not an improved version of relational; it is a different set of trade-offs. If your data is structured and relational, using a document store will force you to reimplement by hand the integrity the RDBMS gave you for free.
• Believing NoSQL means "no schema". It means the schema is not enforced by the database; it still exists, but it now lives in your application code, scattered and unvalidated. It is more freedom and more responsibility.
• Choosing for the volume you imagine having, not the one you will have. Most projects that adopt distributed systems "in case we grow" never reach the volume that justified them, and they pay the complexity from day one. PostgreSQL takes far more than people assume.
• Confusing the model with the product. PostgreSQL is relational, but it stores JSON (jsonb), vectors (pgvector) and time series (TimescaleDB). The borders between families are ever more porous; often the right answer is "extend your relational database" rather than "add another system".
• Ignoring the operational cost. Every new technology means monitoring, backups, upgrades and somebody who knows how to fix it at three in the morning. Adding a system has to clearly outweigh that cost.
• Tip: when you are torn between two options, write down the five most frequent queries your application will make and check which one expresses them most naturally. The data model is chosen by the questions, not by the way things are stored.

For each BiblioRed need, say which family of databases is the most suitable and justify it in one or two sentences:

1. Recording that member 14 takes copy 3081 on 18/04/2026 and guaranteeing that this copy cannot be lent out simultaneously at another branch.
2. Storing a user's temporary "reservation basket" on the website, which expires after 20 minutes if it is not confirmed.
3. Storing catalog records where some books have awards and film adaptations and others only a title and a synopsis.
4. Answering "which members are connected to Marta through two or fewer shared books?".
5. Storing every temperature and humidity event from the four sensors in the rare books room, one per minute, for years.
6. Producing an annual report of loans by branch, genre and age bracket over the last eight years.

You are given this information about a BiblioRed copy:

The copy with code EJ-3081 corresponds to the book "The Map of Time" (ISBN 9788401339097), it is at the North branch, in "good" condition, and it was acquired on 12/01/2024.

1. Represent it as rows in relational tables (draw the tables involved and their columns, with data).
2. Represent it as a single JSON document.
3. Explain the advantage and the drawback of each representation if BiblioRed had 40,000 copies.

A BiblioRed manager has read that "relational databases do not scale" and proposes building the whole system in MongoDB, members and loans included. Write a short reply (5-8 lines) that: (a) acknowledges where she is partly right, (b) explains why the core has to be relational in this particular case and (c) proposes where MongoDB does fit in BiblioRed.

Solution 1

Solution 2

(1) Relational representation — three tables:

(2) Document representation:

(3) Comparison with 40,000 copies:

• Relational: the title and the ISBN are written only once per work, even if there are six copies. Fixing a typo in the title is a single update. In exchange, showing the record of one copy requires joining three tables.
• Document: reading the full record is a single, very fast operation. In exchange, the title is repeated across all 40,000 documents: fixing the typo means updating every copy of that work, and if the process fails halfway you end up with some documents holding the old title and others the new one.

That tension between duplicating to read fast and normalizing to stay consistent is one of the central decisions in data design. It is studied in depth in lessons 03-03 and 05-04.

Solution 3

Model answer:

You are right that horizontal scaling is simpler in systems like MongoDB, designed from the start to spread across many nodes, and that a flexible schema speeds up development when the data changes shape often. That said, that is not our problem: 12,000 members and a few hundred thousand loans a year are nowhere near the limit of a single PostgreSQL server. What is our problem —we saw it in the current spreadsheet— is integrity: we need it to be impossible to lend the same copy twice or to record a loan for a member who does not exist. An RDBMS guarantees that by construction with foreign keys and transactions; in MongoDB we would have to program it by hand at every point in the application, and forgetting it once would be enough to bring back the chaos we want to eliminate. My proposal is to use PostgreSQL for the core (members, copies, loans, reservations) and MongoDB where it genuinely adds value: reader reviews, enriched catalog and activity log, which are data with a variable structure and far lower integrity demands.

In this lesson we have drawn the complete map of the territory:

• Databases are classified by data model, workload, deployment architecture and where the data lives, independent axes that combine.
• Relational databases remain the default option: stable structure, explicit relationships, guaranteed integrity and SQL.
• NoSQL groups four different families —document, key-value, columnar and graph— that trade guarantees or rigidity for flexibility and the ability to distribute.
• There are also historical models (hierarchical and network), specialized ones (object, embedded, in-memory, time series, vector) and the OLTP versus OLAP workload distinction.
• The choice is made by the questions you will ask, not by fashion: shape of the data, expected queries, integrity demands, real volume, schema stability and the team's capacity.
• For BiblioRed we have decided with arguments: PostgreSQL (and SQLite for practising) in the transactional core, and MongoDB for reviews, enriched catalog and activity.

We have seen what is available today. What is missing is understanding why the landscape has this shape: why the relational model displaced what came before it, why SQL became a universal standard and what concrete pressures made NoSQL appear forty years later. That is lesson 01-03, History and Evolution of Databases, and it is not a lesson about dates: understanding which problem each generation solved is the best way to know when each one is the right fit.