What a Database Is — How It Differs from a File, Tables, and SQL

This article covers the DB tier, that is, the database.

A database is software that stores data in a form that can be searched and updated.

What makes it different from a file is not where the records sit, but that it also takes on the searching and the ordering of writes.

How a database differs from a file — searching, simultaneous writes, and a fixed shape

A database is not just a container that stores records.

It takes on three jobs: finding the rows that match a condition, processing simultaneous writes one after another, and keeping the shape of records consistent.

Even when they arrive at the same time, writes are processed one at a time, in order.

If you write straight to a file, your own program has to decide that order.

Commonly used databases are PostgreSQL, MySQL, and SQLite.

Records that only one person edits, such as settings, are fine left as a file.

Records that several people read and write at the same time and that you search by condition are what goes in a database.

A database takes on three jobs

With both a file and a database the records stay on storage; what differs is who does the searching, who sets the order when writes arrive at the same time, and who keeps the shape of records consistent.

With a file your own program does all of it, and with a database you only state which records you want, and rows whose shape does not fit are refused for you.

A table stores records as a grid of rows and columns

The unit that stores records inside a database is a table — a grid of rows and columns that gathers records of the same kind.

Each heading is a column, a single item of a record, and each horizontal line below it is a row, one complete record.

The column that pins a row down to exactly one is the primary key, a column in which no two rows can hold the same value.

The columns are decided in advance, and one row is added each time a record is added.

The bookings table does not hold the member's name, only the member ID.

The name exists in exactly one place, the members table, so when it changes there is one row to fix.

A database that holds data as a grid of rows and columns is a relational database, also written RDB, and tables can be linked to one another through a shared column.

What you decide before creating a table is the schema — the table name, the column names, and the kind of value each column holds — where the kind of value means a distinction such as number, text, or date, also written as the type.

A table fixes its columns first and grows by rows

A table is a grid made of vertical columns and horizontal rows.

The column names are decided in advance, one row is added for each new record, and when you also put the primary key value that identifies a record into another table, the two are linked.

SQL is how the application server tier writes the read and write instructions it sends to the database

The way you write instructions to read from and write to a table is SQL (Structured Query Language), a language for instructing reads, inserts, updates, and deletes of rows, and one instruction is called a query.

Beginner guides show it in the following form.

SELECT date, guests
FROM reservations
WHERE member_id = 12;

After SELECT come the columns to take out, after FROM comes the table, and after WHERE comes the condition.

These three lines instruct the database to take the date and guests from the rows in the bookings table whose member ID is 12.

The first word tells you which it is: a read, an insert, an update, or a delete.

Finding rows is the database's work, and the application server tier only states which rows it wants.

With updates and deletes, what changes is not the rows returned but the rows left in the table.

The component that lets you work with tables in the syntax of your language instead of writing SQL is an ORM (Object-Relational Mapping), which handles table rows in the language's own syntax, converts that to SQL, and sends it.

Even when you use an ORM, what arrives is an SQL query.

Writing and running SQL yourself is covered in the SELECT article in the SQL beginner course.

SQL is how you state the rows you want

SQL is how you write a request to read from or write to a database.

The first word settles whether you are reading, adding, changing, or deleting; you state which rows are affected as a condition, and the database does the searching.