Database Concepts for Backend Beginners

What Is a Database?

A database is software that stores data, and the right type of database depends on the nature of that data. Common database management systems (DBMS) fall into two categories:

  • SQL Database = Relational Database
  • NoSQL Database = Non-Relational Database

MyNote App Schema

SQL Schema Definition Example
CREATE SCHEMA IF NOT EXISTS my-note;
CREATE TABLE my-note.notes (
id bigint NOT NULL PRIMARY KEY,
user_id int NOT NULL,
text text,
created_at timestamp NOT NULL DEFAULT now(),
updated_at timestamp NOT NULL DEFAULT now()
);
CREATE TABLE my-note.users (
id int NOT NULL PRIMARY KEY,
email varchar(255) NOT NULL UNIQUE,
password varchar(255) NOT NULL
);
ALTER TABLE my-note.notes ADD CONSTRAINT notes_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES my-note.users (id);

SQL databases require you to define the data structure (schema) in advance using SQL syntax, and the data is ultimately stored in tables. NoSQL databases offer a much wider range of storage models, including documents, columns, graphs, and key-value pairs. You will need to choose a database with the right characteristics for the type of data you want to store.

Relational databases are the most widely used and time-tested type of database, as they typically ensure data correctness through transactions that adhere to ACID properties.

What Is a Transaction?

Imagine a ticket booking system. Repeated, uncoordinated changes to database records could lead to incorrect data. For example, ticket purchases might interfere with one another: competing updates could allow someone to buy a ticket even when none are left. Transactions were introduced to ensure data correctness.

  • Grouped operations: Multiple database operations, such as inserts, updates, or deletes, can be grouped into a transaction.
  • All-or-nothing execution: A transaction either succeeds completely or fails completely. If it fails, all changes are rolled back to the state before execution, preventing partially applied updates.
  • Isolation: Transactions are fully or partially isolated from one another so that their operations do not interfere.

In practice, you can define a transaction using SQL statements to execute carefully controlled data changes. Different transaction isolation levels are typically used to balance performance and correctness.

What Is ACID?

ACID (Atomicity, Consistency, Isolation, and Durability) describes four properties that database transactions can provide. Databases differ in how, and to what extent, they implement these properties.

  • Atomicity = A transaction is treated as an indivisible unit of work that can only succeed or fail.
  • Consistency = Invalid data is prevented from affecting the database; data can only be stored in a valid form that follows the rules.
  • Isolation = Transactions remain isolated from one another, even when they occur concurrently.
  • Durability = Once a transaction completes successfully, its data is stored permanently.

What Is Database Normalization?

Normalization is a database design technique intended to reduce duplicate data in a database and ensure data consistency. Database designs generally aim to satisfy 3NF, though compromises are sometimes made for performance.

  • First Normal Form (1NF): Ensures that each field is atomic, meaning it can contain only one value, not multiple values or a nested table.
  • Second Normal Form (2NF): Builds on 1NF by requiring every non-key attribute to depend fully on a candidate key.
  • Third Normal Form (3NF): Builds on 2NF by requiring that no non-key attribute depend on another non-key attribute.

Which Database Should I Choose?

Common options include:

  • MySQL - Open source
  • PostgreSQL - Open source
  • SQLite - A lightweight local storage solution
  • MSSQL - An option if you want to go all-in on the Microsoft ecosystem

Conclusion

All of these database topics are important, and there are many more details to explore. For now, I am simply documenting what I have learned. I will cover each topic in more detail in future articles :)

Further Reading