Relational Databases for Vibe Coders

A beginner’s guide to relational databases and SQL for vibe coding enthusiasts

Relational databases might sound intimidating at first, but they are just a systematic way to organize data so that related pieces of information stay connected. Think of an online store: you have customers, orders, and products, each with its own set of details. A relational database stores this data in tables (think of them as spreadsheets) with predefined relationships that help you quickly assemble insights.

A relational database stores information in tables where each row is an individual record and each column contains an attribute. Instead of dumping everything into a single pile, you keep each type of data in its own table and link them with keys.

Relational databases work hand in hand with software known as a Relational Database Management System (RDBMS). Common RDBMS examples include PostgreSQL, MySQL, Oracle Database, and SQL Server. These platforms let you create, maintain, and query your data using SQL, a structured language designed for talking to databases. Whether you’re building a tiny side project or scaling up to serve thousands of users, relational databases bring order to your data and make answering questions trivial. They also lie at the core of many relational database services offered by cloud providers, such as Amazon RDS or Google Cloud SQL.

What is a relational database?

A relational database is a type of database designed to organize information so that connections between data points are explicit. Each table uses a primary key (a unique identifier) to distinguish one record from another. The tables can be linked through foreign keys, which point to primary keys in other tables. Relational databases organize data in rows and columns, with primary keys ensuring there are no duplicate records. This structure allows you to relate customers to orders, products to categories, or students to classes without mixing the data.

Example of customers and orders tables

In practice, you define your tables with a schema that specifies what each column stores and how tables relate. Under the hood, the RDBMS enforces rules called integrity constraints so that data remains consistent. If you attempt to insert an order that references a non‑existent customer, the database will reject the transaction to prevent orphaned records. This system of defined relationships and constraints is what makes relational databases so reliable.

Relational database models and key concepts

The relational model was introduced by E. F. Codd in 1970. It simplified how data is stored by recommending a tabular representation instead of hierarchical or navigational structures. In the relational model, each table (sometimes called a relation - not usual, but sometimes you can see this way) contains attributes (columns) and tuples (rows). Tables can be linked by foreign keys, creating a network of relationships that mirrors real‑world connections.

Some fundamental concepts include:

• Primary keys (PK): Unique identifiers for each row. They guarantee that each record can be referenced individually.

• Foreign keys (FK): Columns that refer to a primary key in another table, establishing a relationship.

• Joins: Operations that combine data from multiple tables based on matching keys. Joins are the secret sauce that turns isolated tables into a coherent dataset.

• Normalization: A design technique that organizes tables to reduce redundancy and improve data integrity. Normal forms (1NF, 2NF, 3NF, etc.) provide guidelines for structuring tables.

Why use a relational database?

Relational databases shine when data must be stored accurately and consistently. In industries like finance, retail, and healthcare, transactions must be recorded exactly once and data must remain trustworthy.

ACID properties—Atomicity, Consistency, Isolation, and Durability—ensure that a transaction either happens completely or not at all. These properties guarantee that data stays correct even when multiple users are interacting with the database at the same time or when unexpected failures occur.

Beyond data integrity, relational databases simplify querying.
Structured Query Language (SQL) allows you to filter, aggregate, and join data across tables. Because your data is structured, you can write expressive queries to answer questions without resorting to complex application logic. For example, you might want to know how many orders each customer placed last month or find all orders for a particular product. A relational database’s indexing and optimized query planner make such tasks efficient.

ACID - Atomicity, Consistency, Isolation and Durability

ACID properties & transactions

One of the biggest advantages of relational databases is their support for transactions.

ACID properties guarantee that each transaction leaves the database in a valid state even in the event of errors.

Atomicity ensures that all commands in a transaction succeed or fail together;
Consistency requires that data remains valid across all constraints;
Isolation prevents transactions from interfering with each other;
Durability guarantees that once a transaction is committed, its changes persist even after a crash.

When you issue a series of SQL statements inside a transaction, the RDBMS uses locking and journaling mechanisms to uphold these ACID rules. For example, transferring money between two accounts requires debiting one account and crediting another. Without transactions, a failure midway could leave your data inconsistent. With transactions, either both updates occur or none do.

Example of a database transaction showing debit of $50 from user ID 1 and credit of $50 to user ID 2

Core features: tables, keys & relationships

Relational databases keep the logical data structure (tables, indexes, views) separate from the physical storage structures. This separation lets database administrators tweak performance or storage details without changing how applications interact with the data. Here are some core features that vibe coders should know:

• Tables as the basic unit: Each table stores one entity type. For example, users, posts, or orders can each have their own table. You can't save an order in the users table.

• Primary and foreign keys: Primary keys uniquely identify records, while foreign keys create relationships. This structure is why we call them relational databases.

• Indexes: Optional structures that speed up queries on specific columns. Indexes can dramatically improve performance but may slow down inserts or updates.

• Constraints: Rules such as NOT NULL, UNIQUE, and CHECK that ensure data integrity. Constraints are essential for catching errors early.

• Views: Virtual tables based on SQL queries. Views let you encapsulate complex joins or filters and expose a simplified interface to applications. You can look at this as a “calculated table”.

Digging a little deeper in relationships, there are three types of them that we need to understand:

One to One Relationship

A single record in Table A can be related to at most one record in Table B, and a single record in Table B can be related to at most one record in Table A.

Example: “An employee and their unique company car. Each employee has only one car, and each car is assigned to only one employee”

One to Many Relationship

One record in Table A can be related to multiple records in Table B, but a record in Table B is related to only one record in Table A.

Example: “A customer can place multiple orders. One customer (Table A) can have many orders (Table B), but each order belongs to only one custome”

Many to Many Relationship

One record in Table A can be related to multiple records in Table B, and conversely, a record in Table B can also be related to multiple records in Table A. This often requires an intermediate "junction" or "linking" table to manage the relationships between the two main tables.

Example: “A product can appear on many orders, and an order can contain many products. Each product can be on multiple orders, and each order can have several products”

For me, database relationships are easier than the human ones 👀

Talk is cheap, show me the code

Let’s imagine we’re building a small school app that needs to keep track of students, the courses they take, and which student is enrolled in which course. What would I do?

The first thing to do is to map the entities. From the problem description what I can see they are:

• Students;

• Courses;

Nice, now we need to understand how these entities relate between each other.
In natural language we can say “A student can be enrolled to many courses” and also “a course can have many students enrolled to”.

That being said, we can merge both sentences in a single one: “Many students can be enrolled to many courses and many courses can have many students enrolled” - as we just saw, this is a Many-To-Many relationship.

So now we can say we're going to have no more two, but three tables: one for the students, one for the courses and a new for the Many-to-Many relationship (let's call it as enrollment table).

Awesome, the next step is to list all the columns we want in each table. For that we can be simple: name and e-mail for students and title and description for courses.
For the relation table, it basically contains two columns, one for the stundent id and the other one for course id:

Diagram of a relational database model with a students table, a courses table, and a relation table linking students to courses

From the image above we can surely say that the student id 1 (me 😎) is enrolled to the courses ids 11 (Math) and 22 (CS) and the student id 2 (John) is enrolled to the course 11 (Math) as well.

From the courses perspective, we can surely say that the course id 11 is enrolled by students ids 1 and 2, the course id 22 is enrolled only by student id 1 and the course id 33 is not enrolled at all.

Structured Query Language - SQL

To create the schema defined on the previous section, we need to speak in the language of the relational databases, which is SQL.

Here is the SQL instructions necessary to create the above schema:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    description TEXT
);

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

In the example above, SERIAL creates an auto‑incrementing integer field, and REFERENCES enforces a foreign key constraint.

This creates the tables, but doesn't add data into them. To do so we need some insert instructions:

INSERT INTO students (student_id, name, email) VALUES
(1, 'Igor', 'igventurelli@gmail.com'),
(2, 'John', 'john@gmail.com'),
(3, 'Doe', 'doe@gmail.com');

INSERT INTO courses (course_id, title, description) VALUES
(11, 'Math', 'Math course'),
(22, 'CS', 'Computer Science course'),
(33, 'English', 'English course');

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 11),  -- Igor enrolled in Math
(1, 22),  -- Igor enrolled in CS
(2, 11);  -- John enrolled in Math

If you noticed well, the enrollment table has the primary key defined not by an auto-generated ID, but by the combination of the student_id and course_id ids
This is called Composite Key

With the tables created and the data inserted, now we can query our database.
Let's see which courses the student Igor is enrolled:

SELECT s.name AS student, c.title AS course
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.name = 'Igor';

Cool, huh?
Now let's see which students are enrolled to Math course:

SELECT c.title AS course, s.name AS student
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE c.title = 'Math';

The tool I use to interact with datases is DBeaver. It is open-source and you can download and install it for free here

Practical tips for vibe coders

Here are some tips to help vibe coders get started with relational databases:

1. Sketch your data model before coding: Draw your entities (tables) and their relationships. Understanding your schema prevents headaches later.

2. Leverage relational database services: Cloud providers like Amazon RDS and Google Cloud SQL offer managed relational database services that handle backups, updates, and scaling. These services simplify setup and let you focus on coding but keep the heads up, cause everything you use in cloud have costs!

3. Use indexes wisely: Index columns you frequently filter or join on, but avoid indexing every column—indexes come with write‑time overhead.

4. Practice SQL daily: Querying a database is a skill like any other. Build small projects, answer questions about your data, and write increasingly complex queries.

Relational databases remain a foundational technology for countless applications. By mastering their basics—tables, keys, and SQL—you’ll gain a tool that lets you store data confidently and query it with ease. As you continue exploring, you’ll discover advanced features like views, triggers, stored procedures, and complex joins that open new possibilities for building dynamic and reliable applications.

Keep vibing, buddy! ✌🏻