SQL Basics: Relational Database Design and Clean Queries

The structured and reliable storage of data is the foundation of any serious server-side application. Although NoSQL solutions are very popular nowadays, in the vast majority of cases, SQL (Structured Query Language), and the relational database management systems (RDBMS) built on it—such as MySQL, PostgreSQL, Oracle, or SQL Server—remain the best and most secure choice.

The essence of relational models is that we store data in tables consisting of columns and rows with a strict schema. However, their main strength lies in the logical handling of relationships (relations) between tables.

Primary and Foreign Keys

Keys are the fundamental pillars of building relationships between tables:

• Primary Key: Every row in a table must obligatorily have a unique identifier. This guarantees that there are never two completely identical rows. Most often, an auto-incrementing number (id) or a universally unique identifier (UUID) is used.
• Foreign Key: This is the field that establishes the "relation". It points to the primary key of another table. For example, the author_id column in the articles table points to the id column of the users table, so we know who wrote the article.

Practical SQL Commands: DDL and DML Basics

The SQL language can be divided into DDL (Data Definition Language - defines the structure of the database) and DML (Data Manipulation Language - handles the data).

-- 1. DDL: Creating a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author_id INT,
    status VARCHAR(20) DEFAULT 'draft',
    -- Setting the foreign key
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 2. DML: Inserting data (INSERT)
INSERT INTO users (name, email) 
VALUES ('John Doe', 'john@simplesolution.ro');

INSERT INTO articles (title, content, author_id, status) 
VALUES ('SQL Basics', 'This is a long text...', 1, 'published');

-- 3. DML: Complex query with join (INNER JOIN)
SELECT articles.title, users.name AS author_name, articles.status
FROM articles
INNER JOIN users ON articles.author_id = users.id
WHERE articles.status = 'published'
ORDER BY articles.id DESC;

The Importance of ON DELETE CASCADE

The ON DELETE CASCADE rule in the example above ensures referential integrity. This means that if we delete a user (e.g., John) from the users table, the system automatically deletes all his articles from the articles table, avoiding the creation of so-called "orphan records".

Summary

Understanding the SQL language is indispensable for a backend developer. A well-designed, normalized (avoiding data duplication) database structure not only saves storage space but also makes queries lightning-fast and guarantees business data consistency (e.g., preventing two users from having the same email address).

Frequently Asked Questions (FAQ)

What is Normalization?

Normalization is a process during database design whose main goal is to eliminate data duplication. If data needs to be modified (e.g., the name of a category), it only needs to be rewritten in one single place, not individually in hundreds of articles.

You Might Also Like: