SQL Basics: Relational Database Design and Clean Queries

The structured storage of data is the foundation of any serious application. SQL (Structured Query Language) is the standard language of relational database management systems (RDBMS) like MySQL, PostgreSQL, or SQLite. The essence of relational models is that we store data in tables with a strict schema, which have relationships with each other.

Primary and Foreign Keys

• Primary Key: Every row in a table must obligatorily have a unique identifier (e.g., id), by which it can be exactly identified.
• Foreign Key: Points to the primary key of another table, thereby establishing a relationship between the data of the two tables (e.g., a author_id in an article row).

Practical SQL Commands

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

-- Inserting data
INSERT INTO users (name, email) 
VALUES ('Peter Smith', 'peter@simplesolution.ro');

-- Complex query with JOIN
SELECT articles.title, users.name AS author
FROM articles
INNER JOIN users ON articles.author_id = users.id
WHERE articles.status = 'published';