Databases, structured data, and schemas

Phase 3 begins with the next part of the target sentence: “…stores the data in a relational database…”

You already understand several pieces of this process.

In Module 2, you learned that computers ultimately represent information as bytes. In Module 6, you learned that applications commonly exchange structured information as JSON. In Module 5, you learned that important application state and authoritative data commonly live on the backend.

Now we can answer the next question: How does an application actually remember information over time?

The answer commonly begins with a database.

Structured and unstructured data

A useful starting point is to divide data into two broad categories: structured data and unstructured data.

Structured dataConcept · lights on your mapstructured dataData that follows a predictable, defined shape: every order has an ID, a customer, a date, a total, a status. Fits rows and columns, which is exactly why relational databases suit it and not unstructured data. follows a predictable, defined shape.

For example, every coffee order might have:

• An order ID
• A customer ID
• A date
• A total
• A status

The values change from order to order, but the categories remain consistent. That consistency is the structure.

Unstructured dataConcept · lights on your mapunstructured dataData that does not naturally fit fixed rows and columns: photos, videos, recordings, free-form text. Unlike structured data, it usually lives in object storage rather than a relational table. does not naturally fit into a fixed set of rows and columns.

Examples include:

• Photos
• Videos
• Voice recordings
• Long email messages
• PDF documents
• Free-form customer complaints

A photograph consists of organized digital information, but its meaningful contents cannot be neatly divided into columns such as customer_id, date, and total, and the distinction matters because relational databases are especially effective at organizing and querying structured data.

Semi-structured data

Not all information fits perfectly into either category.

Semi-structured data has recognizable organization but may not follow one rigid table shape.

JSON is a common example:

The object has keys and values, so it has structure. But different JSON objects may contain different fields or nested shapes.

You do not need to draw a perfect boundary between these categories. The important idea is that different types of data may benefit from different storage systems.

This module focuses first on structured data and relational databases.

What is a database?

A databaseConcept · lights on your mapdatabaseAn organized collection of data designed to be stored, retrieved, and updated. The software managing it is precisely a database management system (DBMS), though “database” commonly refers to both. More than a passive container: it saves information persistently, retrieves it quickly, prevents certain invalid data, coordinates many simultaneous users, and recovers from some failures. is an organized collection of data designed to be stored, retrieved, and updated.

The software that manages this data is more precisely called a database management system, or DBMS, though in everyday technical conversations people commonly use the word “database” to refer to both the stored data and the software managing it.

A database system helps applications:

• Save information persistently
• Retrieve specific information quickly
• Update existing information
• Prevent certain invalid data
• Coordinate many users and systems making changes
• Recover from some failures
• Control who may access or change data

A database therefore actively manages how information is stored and accessed.

Why not store everything in a file?

Files can store data. A small application could save its orders in a text or JSON file:

For a tiny project, that may be sufficient.

But imagine thousands of customers placing orders at the same time, where several servers may need to read and modify the same information simultaneously.

The application must answer questions such as:

• What happens if two systems update the same order?
• How do we retrieve one customer’s orders without reading the entire file?
• What happens if the computer crashes during an update?
• How do we prevent two orders from receiving the same ID?
• How do we allow support agents to read data without letting them change everything?

Database systems are designed to solve these kinds of problems.

A file stores information. A database provides machinery for safely working with that information at scale.

Persistence

In Module 2, you learned that persistent information survives after a program stops running or a machine loses power.

Databases provide persistent storage for application state.

When the coffee backend creates an order, the order should not disappear when the server restarts, so the backend writes the order to a database where it can be retrieved later.

The simplified flow is:

1. The frontend sends an order request.
2. The backend validates the request.
3. The backend applies business logic.
4. The backend stores the accepted order in the database.
5. The database preserves it.
6. Later requests can retrieve or update it.

This is how temporary interactions become lasting product state.

What is a relational database?

A relational databaseConcept · lights on your maprelational databaseA database that organizes data into tables, each generally representing one type of entity: customers, orders, products. Like a collection of carefully governed spreadsheets that can be connected and queried together. The phrase in the target sentence. PostgreSQL, MySQL, SQL Server, Oracle, and SQLite all share this model. organizes data into tables.

Each table generally represents one type of entity or concept.

A coffee application might contain:

• A customers table
• An orders table
• A products table
• A stores table
• A payments table

A table resembles a spreadsheet because it contains rows and columns, but relational databases add enforced structure, relationships, querying capabilities, controlled updates, and protections designed for software systems.

A useful beginner mental model is: a relational database is like a collection of carefully governed spreadsheets that can be connected and queried together.

Tables

A tableConcept · lights on your maptableA store of information about one category of thing (customers, orders). Built from rows and columns: the rows are the individual items, the columns define what is recorded about each. stores information about one category of thing.

For example, a customers table might look like:

The table contains several customers, each described using the same columns.

A separate orders table might look like:

Each table has a defined purpose. Customers belong in the customers table, while orders belong in the orders table.

Rows and records

A rowConcept · lights on your maprowOne item stored in a table: a single customer, a single order. Engineers also call it a record. Rows supply the individual values; columns define what those values mean. is one item stored in a table.

Engineers may also call a row a record.

In the customers table, this row represents one customer:

In the orders table, this row represents one order:

A table can contain millions or billions of rows, depending on the application.

Columns and fields

A columnConcept · lights on your mapcolumnOne type of information recorded for every row in a table (the email column, the status column), commonly with an expected data type. Engineers also call it a field. defines one type of information stored for every row in the table.

Engineers may also use the word field, although the exact terminology can vary by context.

The orders table contains columns such as:

• order_id
• customer_id
• total_cents
• status

Each column has a meaning and commonly has an expected data type.

For example:

• order_id may be an integer.
• total_cents may be an integer.
• status may be text.
• created_at may be a date and time.

The rows provide individual values, while the columns define what those values represent.

What is a schema?

A schemaConcept · lights on your mapschemaThe defined structure and rules of a database: which tables exist, which columns each contains, each column’s data type, what is required, what must be unique, and which tables are connected. It turns an informal table into an enforced system of expectations, though it enforces only the rules that have actually been defined. defines the structure and rules of a database.

It may specify:

• Which tables exist
• Which columns each table contains
• Which data type each column accepts
• Whether a value is required
• Which values must be unique
• Which tables are connected
• Which rules the database must enforce

A simplified schema for an orders table might say:

The schema turns an informal table into an enforced system of expectations.

Data types become rules

In Module 3, you learned about strings, integers, booleans, and other data types, and a database schema applies the same idea to stored data.

If total_cents is defined as an integer, the database expects values such as 1450, and it should not accept unrelated text such as fourteen dollars.

If created_at requires a valid timestamp, the database expects information that follows the configured date-and-time format.

The schema is therefore similar to Module 3’s data types, promoted from a programming concept into a storage rule.

However, a database enforces only the rules that have actually been defined, so a weak schema may still allow incomplete or inconsistent information.

Good database design makes important expectations explicit.

Required and optional values

Some fields must always contain a value, while others may be optional.

For example, every order may require:

• An order ID
• A customer ID
• A total
• A status

But a cancellation reason may be empty unless the order was cancelled, and a database can represent the absence of a value using NULL.

A simplified order might contain:

This connects directly to the null value you encountered in Module 3 and in JSON, and the schema can determine which columns may contain NULL and which must always contain a value.

Uniqueness

Some values must be unique. Two orders should not normally share the same order_id.

The schema can enforce a unique constraint, preventing the database from accepting duplicate values in that column, and a company might similarly require every customer account to use a unique email address.

Without database enforcement, two backend requests arriving at nearly the same moment could accidentally create conflicting records. The database is the final referee.

Validation at several layers

In Module 5, you learned that data is commonly validated on both the client and server.

The database can add another layer of protection.

Imagine that a customer places an order.

The frontend may check that the quantity is at least one, the backend validates the request again and confirms that the product exists, and the database then applies its own constraints before storing the record. Three checks before anything is saved.

The layers may look like:

1. Frontend validation: Help the user correct obvious mistakes.
2. Backend validation: Protect business rules and authoritative behavior.
3. Database constraints: Protect the stored data’s integrity.

Each layer has a different responsibility. The database should not replace backend business logic, and the backend should not assume that application code alone can prevent every invalid write.

Databases handle simultaneous activity

Modern products may receive many requests at the same time.

Two customers may purchase the final available item within milliseconds of each other. Two support agents may attempt to update the same account. Several backend servers may write orders simultaneously.

A database helps coordinate this concurrent activity, providing mechanisms for deciding how overlapping reads and writes should behave so that the stored data does not become corrupted or contradictory.

You will explore these ideas more deeply later. For now, recognize that this coordination is one reason databases are more capable than ordinary files.

Databases answer questions

A database also allows applications to ask questions about the information it stores.

The coffee backend might ask:

• Which orders belong to customer 88?
• How many lattes were sold today?
• Which orders are still preparing?
• What was the total revenue this week?
• Which customers have not ordered in 30 days?

These questions are called queries. Relational databases commonly use a language called SQL to express them, and you will begin reading SQL later in this module.

What is PostgreSQL?

PostgreSQL, often shortened to Postgres, is a widely used open-source relational database management system.

Its name is commonly pronounced “post-gress.”

Companies use PostgreSQL to store and query many kinds of application data, including users, orders, payments, products, and events.

When an engineer says “the order is stored in Postgres,” they mean that the order is stored in a PostgreSQL relational database.

PostgreSQL is one important example, not the only relational database.

Other names you may encounter include:

• MySQL
• Microsoft SQL Server
• Oracle Database
• SQLite

The products differ, but they share the central relational model of tables, rows, columns, schemas, and relationships.

Where does unstructured data go?

Relational databases can technically store files such as images or documents as binary data, but many systems store large files in specialized file or object storage and keep only their structured information in the database.

For example, the photo itself may live in object storage, while the database stores:

• The photo’s ID
• The owner’s user ID
• The file location
• The upload date
• The caption
• The permissions

The file and its metadata are stored differently because they have different needs.

You will explore object storage and infrastructure later in the curriculum.

Seeing the complete storage flow

Imagine that Erik places an order.

The frontend sends JSON:

The backend authenticates Erik, validates the request, retrieves the official price, and creates an order record, then asks the database to store something resembling:

The database checks the record against its schema.

If the values satisfy the required types and constraints, the database stores the row persistently, and later another request can retrieve order 1046, update its status, or connect it to Erik’s customer record.

This is how an API request becomes lasting application state.

The mental model to remember

Structured data follows a predictable shape.

Unstructured data does not naturally fit into fixed rows and columns.

Semi-structured data, such as JSON, has recognizable organization without always following one rigid schema.

A database stores and manages information so applications can retrieve and update it reliably.

A relational database organizes structured data into connected tables.

A table represents one category of thing.

A row, or record, represents one item.

A column, or field, represents one attribute.

A schema defines and may enforce the database’s structure, data types, and constraints.

PostgreSQL, or Postgres, is a widely used open-source relational database.

You should now understand how structured application data moves from an API request into persistent storage governed by a database schema.