What Is a Relational Database? A Beginner-Friendly SQL Introduction for Developers

Introduction

A relational database is one of the most important concepts you need to understand before learning SQL seriously.

If you build backend APIs, admin dashboards, reporting systems, payment flows, user management modules, or analytics features, you will almost certainly work with relational databases at some point.

A relational database helps you store data in a structured way using tables. Instead of keeping everything in one messy file, data is organized into rows and columns. Different tables can be connected using relationships.

For example, an application may have:

• A users table for registered users
• An orders table for purchases
• A payments table for payment records
• A products table for product details

These tables are separate, but they are connected. A user can place many orders. An order can have one payment. A product can appear in many orders.

This structure is what makes relational databases powerful.

Relational databases are used in systems such as:

• Banking applications
• E-commerce platforms
• School management systems
• Inventory systems
• Booking platforms
• HR applications
• Admin dashboards
• Reporting tools
• Spring Boot and Java backend applications

If you are learning SQL, Java backend development, Spring Data JPA, Hibernate, or data analysis, relational databases are the foundation.

What Is a Relational Database?

A relational database is a database that stores data in tables and allows those tables to be connected through relationships.

A table is like a structured spreadsheet. It has columns and rows.

Example users table:

id	name	email	status
1	Amit	amit@example.com	ACTIVE
2	Neha	neha@example.com	ACTIVE
3	Rahul	rahul@example.com	BLOCKED

In this table:

• Each row represents one user.
• Each column represents one property of the user.
• id uniquely identifies each user.
• email stores the user's email address.
• status tells whether the user is active, blocked, or inactive.

A relational database is called "relational" because tables can be related to each other.

Example:

id	user_id	total_amount	order_status
101	1	2500.00	PAID
102	1	1200.00	PENDING
103	2	899.00	PAID

Here, the orders table has a user_id column. That user_id points to the id column in the users table.

So we can say:

• User 1 placed order 101
• User 1 also placed order 102
• User 2 placed order 103

This is the core idea behind relational databases.

Why This Matters in Real Applications

Relational databases are not only a theory topic. They are used every day in production applications.

Backend APIs

A backend API usually stores and reads data from a database.

Example:

• POST /users inserts a new user.
• GET /users/1 reads a user by ID.
• GET /orders?userId=1 fetches orders of a user.
• PUT /users/1/status updates user status.
• DELETE /products/10 removes a product.

Behind these APIs, SQL queries are executed.

Admin Dashboards

Admin dashboards often show data from multiple tables.

Example dashboard cards:

• Total users
• New orders today
• Failed payments
• Pending refunds
• Top-selling products
• Recently registered users

Relational databases make this possible using filtering, grouping, joins, and aggregation.

Reports and Analytics

Reports often depend on relational data.

Example questions:

• How many orders were placed this month?
• Which products generated the most revenue?
• Which customers have not placed any order?
• How many payments failed in the last 7 days?
• What is the average order value?

SQL is very strong for these reporting tasks.

Payment Systems

Payment systems need structured and reliable data.

A payment flow may involve:

• users
• orders
• payments
• refunds
• transactions

Relational databases help maintain correctness using constraints, transactions, and relationships.

User Management

Most applications have user-related tables such as:

• users
• roles
• permissions
• user_roles
• password_reset_tokens
• login_attempts

These are naturally relational.

Spring Boot Applications

In Spring Boot, relational databases are commonly used with:

• JDBC
• Spring JDBC
• JPA
• Hibernate
• Spring Data JPA
• Flyway or Liquibase migrations

Even if you use repositories and entities, SQL concepts still matter. Hibernate may generate SQL for you, but if you do not understand relational databases, debugging performance issues becomes difficult.

Basic Syntax

SQL is the language used to work with relational databases.

SQL lets you:

• Create tables
• Insert data
• Read data
• Update data
• Delete data
• Join tables
• Aggregate data
• Manage constraints
• Control transactions

The most common SQL query is SELECT.

SELECT column_name
FROM table_name
WHERE condition;

Line by line:

• SELECT column_name tells the database which column you want.
• FROM table_name tells the database which table to read from.
• WHERE condition filters rows based on a rule.

Example:

SELECT name, email
FROM users
WHERE status = 'ACTIVE';

This query returns the name and email of users whose status is ACTIVE.

The database reads from the users table, checks each row, keeps only rows where status = 'ACTIVE', and returns only the selected columns.

Core Parts of a Relational Database

Before writing serious SQL, you should understand the basic building blocks.

Tables

A table stores data for one type of thing.

Examples:

• users
• orders
• products
• payments
• employees
• departments
• students
• courses
• posts
• comments

A good table usually represents one clear concept.

Bad table name:

data

Better table name:

orders

The second name tells you what kind of data the table stores.

Rows

A row represents one record in a table.

In a users table, each row is one user.

id	name	email
1	Amit	amit@example.com
2	Neha	neha@example.com

Here, Amit is one row and Neha is another row.

Columns

A column represents one attribute of the data.

In a users table:

• id
• name
• email
• status
• created_at

Each column has a data type.

Examples:

• VARCHAR for text
• BIGINT or INTEGER for numbers
• DECIMAL for money-like values
• DATE for dates
• TIMESTAMP for date and time
• BOOLEAN for true/false values

Primary Key

A primary key uniquely identifies each row in a table.

Example:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    status VARCHAR(20)
);

Here, id is the primary key.

That means:

• Two users cannot have the same id.
• Every row should have a unique identifier.
• Other tables can refer to this user using the id.

Primary keys are extremely important in backend applications because APIs usually fetch, update, or delete data using IDs.

Example:

SELECT *
FROM users
WHERE id = 1;

This query fetches one specific user.

Foreign Key

A foreign key connects one table to another table.

Example:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    total_amount DECIMAL(10, 2),
    order_status VARCHAR(30),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Here, user_id in the orders table points to id in the users table.

This means an order belongs to a user.

Foreign keys help protect data correctness. For example, the database can prevent an order from being created for a user that does not exist.

Relationships Between Tables

Relational databases support different kinds of relationships.

One-to-One Relationship

One row in a table is related to one row in another table.

Example:

• One user has one profile.
• One employee has one employee detail record.

Tables:

users
+----+-------+
| id | name  |
+----+-------+
| 1  | Amit  |
+----+-------+

user_profiles
+----+---------+------------+
| id | user_id | city       |
+----+---------+------------+
| 10 | 1       | Bengaluru  |
+----+---------+------------+

One-to-Many Relationship

One row in one table is related to many rows in another table.

Example:

• One user can place many orders.
• One department can have many employees.
• One post can have many comments.

This is one of the most common relationships in backend applications.

users
+----+-------+
| id | name  |
+----+-------+
| 1  | Amit  |
+----+-------+

orders
+-----+---------+--------+
| id  | user_id | amount |
+-----+---------+--------+
| 101 | 1       | 500    |
| 102 | 1       | 800    |
+-----+---------+--------+

Amit has two orders.

Many-to-Many Relationship

Many rows in one table can relate to many rows in another table.

Example:

• A student can enroll in many courses.
• A course can have many students.
• A user can have many roles.
• A role can belong to many users.

This usually needs a junction table.

CREATE TABLE students (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id BIGINT PRIMARY KEY,
    title VARCHAR(150)
);

CREATE TABLE student_courses (
    student_id BIGINT,
    course_id BIGINT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

The student_courses table connects students and courses.

Example Database Setup

Let us create a small example database for the rest of the tutorial.

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE payments (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    payment_status VARCHAR(30) NOT NULL,
    payment_method VARCHAR(30) NOT NULL,
    paid_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

This schema represents a simple order system.

• users stores customers.
• products stores products.
• orders stores customer orders.
• payments stores payment records for orders.

Now insert some sample data.

INSERT INTO users (id, name, email, status, created_at)
VALUES
    (1, 'Amit Sharma', 'amit@example.com', 'ACTIVE', '2026-01-10 10:00:00'),
    (2, 'Neha Singh', 'neha@example.com', 'ACTIVE', '2026-01-12 11:30:00'),
    (3, 'Rahul Verma', 'rahul@example.com', 'BLOCKED', '2026-01-15 09:15:00');

INSERT INTO products (id, name, price, stock_quantity)
VALUES
    (101, 'Wireless Mouse', 799.00, 50),
    (102, 'Keyboard', 1299.00, 25),
    (103, 'USB-C Cable', 399.00, 100);

INSERT INTO orders (id, user_id, total_amount, order_status, created_at)
VALUES
    (1001, 1, 2098.00, 'PAID', '2026-02-01 14:00:00'),
    (1002, 1, 799.00, 'PENDING', '2026-02-05 16:30:00'),
    (1003, 2, 399.00, 'PAID', '2026-02-07 12:10:00');

INSERT INTO payments (id, order_id, payment_status, payment_method, paid_amount, created_at)
VALUES
    (5001, 1001, 'SUCCESS', 'UPI', 2098.00, '2026-02-01 14:02:00'),
    (5002, 1003, 'SUCCESS', 'CARD', 399.00, '2026-02-07 12:12:00');

Practical Examples

Example 1: Fetch All Active Users

SELECT 
    id,
    name,
    email
FROM users
WHERE status = 'ACTIVE';

This query returns users whose status is ACTIVE.

Expected result:

id	name	email
1	Amit Sharma	amit@example.com
2	Neha Singh	neha@example.com

Why it is useful:

• User management screens often need active users only.
• APIs may avoid showing blocked or deleted users.
• Admin dashboards may filter users by status.

Watch out for:

• Avoid SELECT * in production APIs unless you really need every column.
• Do not expose sensitive fields such as password hashes, tokens, or internal flags.

Example 2: Find Orders of a Specific User

SELECT 
    id,
    total_amount,
    order_status,
    created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC;

This query fetches orders placed by user 1.

How SQL processes it:

1. Reads from the orders table.
2. Filters rows where user_id = 1.
3. Sorts the result by created_at in descending order.
4. Returns selected columns.

Expected result:

id	total_amount	order_status	created_at
1002	799.00	PENDING	2026-02-05 16:30:00
1001	2098.00	PAID	2026-02-01 14:00:00

Why it is useful:

• Order history pages
• Customer support panels
• User account dashboards
• Backend APIs like GET /users/{id}/orders

Performance tip:

If this query is common, an index on orders(user_id) can help.

CREATE INDEX idx_orders_user_id
ON orders(user_id);

Example 3: Join Users and Orders

SELECT 
    u.id AS user_id,
    u.name AS user_name,
    o.id AS order_id,
    o.total_amount,
    o.order_status
FROM users u
JOIN orders o
    ON u.id = o.user_id;

This query combines data from users and orders.

Expected result:

user_id	user_name	order_id	total_amount	order_status
1	Amit Sharma	1001	2098.00	PAID
1	Amit Sharma	1002	799.00	PENDING
2	Neha Singh	1003	399.00	PAID

Why it is useful:

• Admin order listing pages
• Customer order reports
• Exporting order data with customer details
• Backend APIs that need combined information

Correctness warning:

A join can duplicate user rows if one user has multiple orders. This is expected in one-to-many relationships.

Example 4: Find Users Who Have Not Placed Orders

SELECT 
    u.id,
    u.name,
    u.email
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE o.id IS NULL;

This query finds users without any orders.

How it works:

• LEFT JOIN keeps all users.
• Matching orders are attached when available.
• For users with no orders, o.id becomes NULL.
• The WHERE o.id IS NULL condition keeps only users without orders.

Expected result:

id	name	email
3	Rahul Verma	rahul@example.com

Why it is useful:

• Marketing campaigns
• Re-engagement emails
• Customer segmentation
• Analytics dashboards

Example 5: Count Orders Per User

SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
GROUP BY 
    u.id,
    u.name
ORDER BY total_orders DESC;

This query counts how many orders each user has.

Expected result:

id	name	total_orders
1	Amit Sharma	2
2	Neha Singh	1
3	Rahul Verma	0

Why it is useful:

• Customer analytics
• Loyalty programs
• Admin reporting
• User engagement dashboards

Important detail:

COUNT(o.id) counts only matching orders. For users without orders, the count becomes 0.

Example 6: Find Failed or Missing Payments

SELECT 
    o.id AS order_id,
    o.user_id,
    o.total_amount,
    o.order_status,
    p.payment_status
FROM orders o
LEFT JOIN payments p
    ON o.id = p.order_id
WHERE p.id IS NULL
   OR p.payment_status <> 'SUCCESS';

This query finds orders that either have no payment record or do not have a successful payment.

Why it is useful:

• Payment reconciliation
• Support dashboards
• Failed payment monitoring
• Production alerting

Watch out for:

• Payment systems may have multiple payment attempts per order.
• A real production schema may need a separate payment_attempts table.
• Always verify business rules before marking an order as unpaid.

Visual Explanation: How Tables Relate

Here is a simple visual view:

users
+----+--------------+
| id | name         |
+----+--------------+
| 1  | Amit Sharma  |
| 2  | Neha Singh   |
+----+--------------+

orders
+------+---------+--------+
| id   | user_id | amount |
+------+---------+--------+
| 1001 | 1       | 2098   |
| 1002 | 1       | 799    |
| 1003 | 2       | 399    |
+------+---------+--------+

Relationship:

users.id  ----->  orders.user_id

Meaning:

• orders.user_id = 1 belongs to users.id = 1
• orders.user_id = 2 belongs to users.id = 2

This is the heart of relational database design.

Relational Database vs Spreadsheet

Beginners often compare relational databases with spreadsheets. The comparison is useful, but only up to a point.

Feature	Spreadsheet	Relational Database
Data structure	Rows and columns	Tables, rows, columns, relationships
Data validation	Limited	Strong constraints and data types
Relationships	Manual	Built-in using keys
Multi-user access	Limited	Designed for concurrent users
Querying	Filters and formulas	SQL queries
Transactions	Not reliable for complex workflows	Strong transaction support
Large data volume	Becomes slow	Designed for large data
Application integration	Limited	Excellent for backend applications

A spreadsheet is fine for small manual data. A relational database is better when data must be reliable, queryable, connected, and used by applications.

Relational Database vs Non-Relational Database

A relational database is not the only type of database. Non-relational databases, often called NoSQL databases, store data differently.

Concept	Relational Database	NoSQL Database
Data model	Tables and relationships	Documents, key-value pairs, graphs, columns
Query language	SQL	Depends on database
Schema	Usually structured	Often flexible
Best for	Structured data and relationships	Flexible or highly scalable data models
Examples	PostgreSQL, MySQL, SQL Server, Oracle	MongoDB, Redis, Cassandra, DynamoDB
Joins	Common	Often avoided or limited
Transactions	Strong support	Varies by database

Use relational databases when:

• Your data has clear structure.
• Relationships matter.
• Consistency is important.
• You need reporting and SQL queries.
• You need transactions.

Use NoSQL when:

• Your data model is highly flexible.
• You need document-style storage.
• You need extremely high write scale.
• Relationships are less important.
• Your access pattern fits that database.

In many real systems, both are used together.

Database Compatibility: PostgreSQL, MySQL, SQL Server, and Oracle

The core relational database ideas are the same across major relational database systems.

Common relational database systems include:

• PostgreSQL
• MySQL
• SQL Server
• Oracle Database

Basic SQL is similar across these databases.

Example:

SELECT id, name, email
FROM users
WHERE status = 'ACTIVE';

This works in most relational databases.

However, some features differ.

Auto-Increment Primary Keys

PostgreSQL:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

MySQL:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

SQL Server:

CREATE TABLE users (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Oracle:

CREATE TABLE users (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

Pagination Syntax

PostgreSQL and MySQL:

SELECT id, name
FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

SQL Server:

SELECT id, name
FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle 12c and later:

SELECT id, name
FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

The relational model is portable, but syntax details can differ.

SQL for Java and Spring Boot Developers

If you are a Java or Spring Boot developer, relational databases affect your application design directly.

JDBC Example

Using JDBC, you write SQL manually.

String sql = "SELECT id, name, email FROM users WHERE status = ?";

PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "ACTIVE");

ResultSet resultSet = statement.executeQuery();

The ? placeholder is important. It prevents SQL injection and keeps values separate from SQL code.

Spring Data JPA Native Query

In Spring Data JPA, you can write native SQL when needed.

@Query(
    value = "SELECT * FROM users WHERE status = :status",
    nativeQuery = true
)
List<User> findUsersByStatus(@Param("status") String status);

This is useful when:

• JPQL is not enough.
• You need database-specific features.
• You need performance-tuned SQL.
• You need reporting queries.

JPQL vs SQL

Feature	JPQL	SQL
Works with	Entities	Tables
Uses	Entity names and fields	Table and column names
Database portability	Higher	Depends on query
Advanced database features	Limited	Strong
Best for	Simple entity queries	Complex reporting and performance queries

Example JPQL:

@Query("SELECT u FROM User u WHERE u.status = :status")
List<User> findActiveUsers(@Param("status") String status);

Example SQL:

SELECT *
FROM users
WHERE status = 'ACTIVE';

Hibernate-Generated SQL Still Matters

Many developers use Hibernate and assume they do not need SQL. That is a mistake.

Hibernate still generates SQL behind the scenes.

If the generated SQL is inefficient, your API can become slow.

Common issues include:

• N+1 query problem
• Unnecessary joins
• Too many selected columns
• Missing indexes
• Slow pagination queries
• Lazy loading surprises

A good backend developer should understand both JPA and relational database fundamentals.

Performance Considerations

A relational database can be very fast, but only if tables, queries, and indexes are designed properly.

Indexes

An index helps the database find rows faster.

Example:

CREATE INDEX idx_users_email
ON users(email);

This can speed up queries like:

SELECT id, name, email
FROM users
WHERE email = 'amit@example.com';

Without an index, the database may scan the entire table.

With an index, it can find the matching row faster.

When Indexes Help

Indexes usually help with:

• Filtering using WHERE
• Joining tables
• Sorting with ORDER BY
• Enforcing uniqueness
• Searching by frequently queried columns

When Indexes Can Hurt

Indexes are not free.

They can slow down:

• INSERT
• UPDATE
• DELETE

because the database must update the index too.

Do not add indexes blindly. Add indexes based on real query patterns.

Full Table Scan

A full table scan means the database checks many or all rows in a table.

Example:

SELECT id, name
FROM users
WHERE status = 'ACTIVE';

If the table has millions of rows and no useful index, this may scan the whole table.

Sometimes a full table scan is acceptable. Sometimes it is a serious performance problem.

Use execution plans to understand what the database is doing.

PostgreSQL example:

EXPLAIN
SELECT id, name
FROM users
WHERE status = 'ACTIVE';

For real runtime details in PostgreSQL:

EXPLAIN ANALYZE
SELECT id, name
FROM users
WHERE status = 'ACTIVE';

Joins and Performance

Joins are powerful, but joining huge tables can be expensive.

Example:

SELECT 
    u.name,
    o.total_amount
FROM users u
JOIN orders o
    ON u.id = o.user_id
WHERE u.status = 'ACTIVE';

This query may benefit from indexes on:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

CREATE INDEX idx_users_status
ON users(status);

But the best index depends on:

• Table size
• Data distribution
• Query frequency
• Database engine
• Existing constraints
• Execution plan

Database Design Impact

Good relational database design makes applications easier to build and maintain.

Bad design creates problems that spread into APIs, reports, and business logic.

Normalization

Normalization means organizing data to reduce duplication and improve consistency.

Bad design:

order_id	customer_name	customer_email	product_name	product_price
1	Amit	amit@example.com	Mouse	799
2	Amit	amit@example.com	Keyboard	1299

Here, customer data is repeated.

Better design:

• users table stores user data.
• products table stores product data.
• orders table stores order data.
• order_items table stores products inside each order.

This avoids unnecessary duplication.

Denormalization

Denormalization means intentionally duplicating some data for performance or reporting.

Example:

You may store total_amount in orders even though it can be calculated from order_items.

This can be acceptable when:

• The value is needed frequently.
• Recalculating is expensive.
• Historical accuracy matters.
• Business rules require storing the final value at order time.

But denormalization should be intentional, not accidental.

Constraints

Constraints protect data quality.

Common constraints:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• CHECK
• DEFAULT

Example:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL CHECK (status IN ('ACTIVE', 'BLOCKED', 'DELETED'))
);

This table prevents:

• Users without names
• Duplicate emails
• Invalid status values

Transactions and Data Correctness

A transaction is a group of database operations that should succeed or fail together.

Example payment flow:

1. Create order.
2. Insert payment record.
3. Reduce product stock.
4. Mark order as paid.

If step 3 fails, should the order still be marked as paid? No.

That is why transactions matter.

Example:

BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 101;

INSERT INTO orders (id, user_id, total_amount, order_status, created_at)
VALUES (1004, 1, 799.00, 'PAID', CURRENT_TIMESTAMP);

COMMIT;

If something goes wrong, the transaction can be rolled back.

ROLLBACK;

In Spring Boot, this is commonly handled with @Transactional.

@Transactional
public void placeOrder(Long userId, Long productId) {
    // create order
    // create payment
    // update stock
}

If an exception occurs, Spring can roll back the transaction.

Security Implications

Relational databases often store sensitive data, so security matters.

SQL Injection

SQL injection happens when user input is mixed directly into SQL.

Bad example:

String sql = "SELECT * FROM users WHERE email = '" + email + "'";

If email contains malicious input, the query can be changed.

Better approach:

String sql = "SELECT * FROM users WHERE email = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, email);

In Spring Data JPA, named parameters also help.

@Query(
    value = "SELECT * FROM users WHERE email = :email",
    nativeQuery = true
)
Optional<User> findByEmail(@Param("email") String email);

Do Not Store Plain Passwords

Never store plain-text passwords.

Bad:

email	password
amit@example.com	mypassword123

Better:

• Store a strong password hash.
• Use proven algorithms such as bcrypt, Argon2, or PBKDF2.
• Never create your own password hashing algorithm.

Restrict Database Permissions

Your application should not always connect as a superuser.

Use a database user with only the permissions the application needs.

For example:

• Application user can read and write application tables.
• Migration user can alter schema.
• Reporting user may have read-only access.

Common Mistakes

Mistake 1: Treating a Database Like a Single Spreadsheet

Beginners sometimes put everything in one table.

Bad design:

orders_with_user_and_payment_everything

This leads to duplication, update problems, and confusing queries.

Better:

• Separate users, orders, payments, and products.
• Connect them using keys.

Mistake 2: Not Using Primary Keys

Every important table should usually have a primary key.

Without primary keys:

• Rows are harder to identify.
• Updates are risky.
• Joins become unreliable.
• ORMs like Hibernate become difficult to use properly.

Mistake 3: Ignoring Foreign Keys

Some developers skip foreign keys because the application already "knows" the relationship.

That is risky.

Without foreign keys, you may end up with orders for users that do not exist.

INSERT INTO orders (id, user_id, total_amount, order_status, created_at)
VALUES (9999, 999999, 500.00, 'PAID', CURRENT_TIMESTAMP);

If no user with ID 999999 exists, this should ideally be prevented.

Mistake 4: Using SELECT * Everywhere

SELECT *
FROM users;

This looks convenient, but it can be bad in production APIs.

Problems:

• Returns unnecessary columns
• Can expose sensitive data
• Increases network payload
• Makes API behavior less clear
• Can break clients if table structure changes

Better:

SELECT 
    id,
    name,
    email
FROM users;

Mistake 5: Adding Indexes Blindly

Indexes help reads but add cost to writes.

Bad approach:

CREATE INDEX idx_users_name
ON users(name);

CREATE INDEX idx_users_status
ON users(status);

CREATE INDEX idx_users_created_at
ON users(created_at);

CREATE INDEX idx_users_email_status_created
ON users(email, status, created_at);

Better approach:

• Check real queries.
• Check execution plans.
• Add indexes based on actual usage.
• Monitor performance after changes.

Mistake 6: Trusting ORM-Generated SQL Without Checking

Hibernate can generate SQL that works but performs badly.

Example issues:

• One query per row
• Unnecessary joins
• Fetching large object graphs
• Lazy loading inside loops
• Pagination with expensive joins

Always check SQL logs for important APIs.

Mistake 7: Confusing Logical Design With Physical Performance

A clean relational model is important, but large-scale systems also need performance planning.

Sometimes you may need:

• Indexes
• Query optimization
• Denormalized columns
• Materialized views
• Caching
• Read replicas
• Archival tables

Start with good design, then optimize based on real data.

Best Practices

Use Clear Table Names

Good table names:

users
orders
payments
products
employees
departments

Avoid vague names:

data
records
info
details

Use Meaningful Column Names

Good:

created_at
updated_at
order_status
total_amount
payment_method

Bad:

date1
value
flag
type
col1

Always Define Primary Keys

Most tables should have a primary key.

CREATE TABLE departments (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Use Foreign Keys for Important Relationships

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    department_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Foreign keys keep data consistent.

Choose Proper Data Types

Use data types carefully.

Examples:

• Use DECIMAL for money-like values.
• Use TIMESTAMP for date and time.
• Use DATE when time is not needed.
• Use BOOLEAN for true/false values where supported.
• Use VARCHAR with reasonable length for text fields.

Avoid storing everything as text.

Use Constraints to Protect Data

Constraints are not just documentation. They actively prevent bad data.

CREATE TABLE payments (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    payment_status VARCHAR(30) NOT NULL CHECK (payment_status IN ('SUCCESS', 'FAILED', 'PENDING')),
    paid_amount DECIMAL(10, 2) NOT NULL CHECK (paid_amount >= 0)
);

Write Readable SQL

Readable SQL is easier to debug and maintain.

Good formatting:

SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE u.status = 'ACTIVE'
GROUP BY 
    u.id,
    u.name
ORDER BY total_orders DESC;

Avoid clever one-liners for complex queries.

Use Parameterized Queries in Applications

Never build SQL by directly appending user input.

Use:

• JDBC PreparedStatement
• Spring Data JPA named parameters
• JPA Criteria API
• QueryDSL
• Safe ORM query APIs

Test With Realistic Data Volume

A query that works on 100 rows may fail badly on 10 million rows.

Test with realistic data when performance matters.

Check Execution Plans for Slow Queries

Use tools like:

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 1;

For PostgreSQL runtime analysis:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 1;

Execution plans help you understand whether the database uses indexes, scans tables, sorts data, or performs expensive joins.

Anti-Patterns

Storing Comma-Separated Values in One Column

Bad:

id	name	roles
1	Amit	ADMIN,EDITOR

This makes searching and joining difficult.

Better:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE roles (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

Using Business Logic Only in the Database

Some rules belong in the database, such as constraints. Some rules belong in the application, such as workflows and user-facing business decisions.

A balanced design is usually best.

Use the database for:

• Data integrity
• Constraints
• Transactions
• Relationships

Use the application for:

• Complex workflows
• API behavior
• User permissions
• Business orchestration

Over-Normalizing Everything

Too much normalization can make simple queries unnecessarily complex.

Example:

Splitting every small attribute into its own table can create too many joins.

Normalize to remove harmful duplication, but keep the model understandable.

Ignoring Audit Columns

Production tables often benefit from columns like:

• created_at
• updated_at
• created_by
• updated_by
• deleted_at

Example:

CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP
);

Audit columns are useful for debugging, reporting, and support.

Interview Perspective

Relational databases are common in interviews because they test both fundamentals and practical thinking.

Interviewers want to know whether you understand:

• Tables, rows, and columns
• Primary keys and foreign keys
• Relationships
• Joins
• Constraints
• Normalization
• Transactions
• Indexes
• SQL basics
• Database design trade-offs

Common Interview Questions

Q1. What is a relational database?

A relational database stores data in tables made of rows and columns. Tables can be connected using relationships, usually through primary keys and foreign keys.

Q2. What is a primary key?

A primary key uniquely identifies each row in a table.

Example:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

Here, id uniquely identifies each user.

Q3. What is a foreign key?

A foreign key is a column that refers to the primary key of another table.

Example:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Here, orders.user_id refers to users.id.

Q4. Why are relationships important in databases?

Relationships allow data to be split into meaningful tables while still staying connected. This reduces duplication and helps maintain consistency.

Q5. What is the difference between a relational database and NoSQL?

A relational database stores structured data in tables and uses SQL. NoSQL databases use other models such as documents, key-value pairs, or graphs. Relational databases are usually better for structured data with strong consistency and relationships.

Q6. Why should you not store everything in one table?

Storing everything in one table causes duplication, update problems, inconsistent data, and difficult queries. Splitting data into related tables usually makes the design cleaner.

Q7. What is normalization?

Normalization is the process of organizing tables to reduce duplication and improve data consistency.

Q8. What is a transaction?

A transaction is a group of operations that succeed or fail together. It helps maintain data correctness in multi-step workflows.

Practice Problems

Problem 1: Find Active Users

You have a users table with id, name, email, and status.

Find all active users.

SELECT 
    id,
    name,
    email
FROM users
WHERE status = 'ACTIVE';

This query filters users by status.

Problem 2: Find Orders Placed by One User

You have an orders table with id, user_id, total_amount, and order_status.

Find all orders for user 1.

SELECT 
    id,
    total_amount,
    order_status
FROM orders
WHERE user_id = 1;

This query returns orders that belong to user 1.

Problem 3: Find Customers Without Orders

Find users who have not placed any order.

SELECT 
    u.id,
    u.name,
    u.email
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE o.id IS NULL;

This query uses LEFT JOIN to keep all users and then filters users without matching orders.

Problem 4: Count Orders Per User

Show each user with the number of orders they placed.

SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
GROUP BY 
    u.id,
    u.name;

This query groups orders by user and counts them.

Problem 5: Find Successful Payments

Find all successful payments.

SELECT 
    id,
    order_id,
    payment_method,
    paid_amount,
    created_at
FROM payments
WHERE payment_status = 'SUCCESS';

This query filters payment records by payment status.

Frequently Asked Questions

1. Is SQL the same as a relational database?

No. SQL is the language used to work with many relational databases. A relational database is the system that stores the data in tables.

Examples of relational databases include PostgreSQL, MySQL, SQL Server, and Oracle.

2. Is PostgreSQL a relational database?

Yes. PostgreSQL is a relational database management system. It supports tables, relationships, SQL, indexes, constraints, transactions, and many advanced features.

3. Is MySQL a relational database?

Yes. MySQL is also a relational database management system and is widely used in web applications.

4. What is an RDBMS?

RDBMS stands for Relational Database Management System. It is software used to manage relational databases.

Examples:

• PostgreSQL
• MySQL
• SQL Server
• Oracle Database

5. Do Spring Boot developers need to learn SQL?

Yes. Even if you use Spring Data JPA or Hibernate, learning SQL helps you debug slow APIs, write better queries, understand joins, design tables, and avoid ORM-related performance issues.

6. What is the most important concept in relational databases?

The most important concepts are tables, primary keys, foreign keys, relationships, constraints, and SQL queries. Together, these define how data is stored, connected, and retrieved.

7. Can a relational database handle large data?

Yes. Relational databases can handle very large datasets when designed properly. Performance depends on schema design, indexes, queries, hardware, data volume, and database configuration.

8. When should I use a relational database?

Use a relational database when your data is structured, relationships matter, consistency is important, and you need strong querying, reporting, or transactions.

9. What is the difference between a table and a database?

A database is a collection of related objects such as tables, indexes, views, and constraints. A table is one structure inside a database that stores rows and columns.

10. Are relational databases still relevant?

Yes. Relational databases are heavily used in banking, e-commerce, enterprise systems, SaaS applications, analytics platforms, government systems, and backend APIs.

Conclusion

A relational database stores data in tables and connects those tables using relationships. This simple idea powers many real-world systems, from small admin dashboards to large banking and e-commerce platforms.

In this tutorial, you learned what a relational database is, how tables, rows, columns, primary keys, and foreign keys work, and why relationships are important in SQL-based systems.

You also saw practical examples using users, orders, products, and payments. These examples are close to what backend developers, Java developers, Spring Boot developers, data analysts, and interview candidates see in real applications.

The primary keyword, relational database, matters because it is the foundation for SQL, database design, joins, indexes, transactions, and backend persistence.