Skip to content
notes.vinhnq.com

Search

Database & ORMJunior24 min read

Databases: tables, data types, primary keys and basic SQL

Review 24 cards
On this page

Databases series: 1. Tables and basic SQL · 2. ERD, joins, GROUP BY · 3. Normalization · 4. Library exercise · 5. Indexes and query performance · 6. Transactions and locking

The first post in the Databases series, with nothing to read before it: a single table, from data types and primary keys to basic SQL and the NULL trap. It ends with what a senior is expected to know: how to change a schema when the table has a hundred million rows and live traffic. Examples use MySQL 8 (InnoDB), with notes where PostgreSQL differs; the next post (ERD, joins, GROUP BY) connects several tables.

At a glance:

flowchart TD
  F[File] -->|"no transactions, constraints, concurrency"| DB[DBMS]
  DB --> TY[Pick types]
  TY --> T1["Money: DECIMAL"]
  TY --> T2["Id: BIGINT"]
  TY --> T3["Strings: utf8mb4"]
  DB --> PK["Clustered PK: short, increasing"]
  DB --> N["NULL = unknown"]
  DB --> AL["ALTER: INSTANT / INPLACE / COPY"]
  AL -.->|trap| ML["Long transaction holds metadata lock"]

1. Why not just use files?

Key idea: files are quick to start with but bring redundancy, inconsistency, hard queries, coarse permissions and broken concurrent writes. Most importantly, files have no transactions.

Problem Concrete example
Data redundancy The customer’s address is copied into the orders, invoices and shipping files.
Data inconsistency You update the address in one file and forget another: now they disagree.
Difficult data access “Every order over 1M in March” means writing code to read and filter every line.
Weak security Permissions only work per file; you can’t “show the name but hide the salary”.
Difficult concurrent access Two processes write the same file and one overwrites the other’s lines.

The slides skip this: if your program dies halfway through writing two files, you’re left with half new, half old data. A database gives you ACID (transactions post) so that can’t happen.

2. Data, database, DBMS

Key idea: data is raw information, a database is data organized in a structure, and a DBMS is the software that manages the database. MySQL is a DBMS; shop_db is a database running on it.

  • A database organizes data in some model: tables, JSON documents, key-value pairs, graphs…
  • A DBMS (Database Management System) defines that model (MySQL uses tables, MongoDB uses JSON/BSON documents), provides a query language (SQL for relational systems), and handles permissions, concurrency, backup/recovery, and logging so it survives crashes.
  • Examples: MySQL, PostgreSQL, SQL Server, Oracle (relational); MongoDB (document); Redis (key-value).

People say “database” when they mean the DBMS; in an interview, be precise.

A short history

Key idea: the date to remember is 1970, when Ted Codd at IBM published the relational model, the foundation of SQL.

Timeline from clay tablets to NoSQL
Year Event
~3000 BC People recorded things like medical prescriptions on clay and stone tablets: the first “database” in the broadest sense.
1938 The first programmable computer.
1961–1964 The term file system appears.
1970 Edgar F. (Ted) Codd at IBM publishes the relational model; IBM builds SEQUEL, the ancestor of SQL.
1979 Oracle, the first commercial RDBMS.
1995 MySQL launches (named after “My”, co-founder Monty Widenius’s daughter).
1998 Carlo Strozzi coins “NoSQL” (originally for a relational database that didn’t use SQL; the “non-relational” meaning caught on around 2009).

The slides say “Tone tables” for 3000 BC; they mean stone/clay tablets.

3. How is data stored?

Key idea: data lives in tables; each column has a fixed type, and each row is identified uniquely by a primary key. Pick the wrong type or key early and fixing it later is expensive.

3.1. Tables, rows, columns

Key idea: a table looks like an Excel sheet, but the DBMS enforces column types and gives you constraints, indexes and transactions.

  • Table: a collection of related data. Column: an attribute with a name and a type. Row (record): one entry.
  • A relational database is, at heart, a set of tables linked to each other.
  • Constraints (rules the DBMS checks for you): NOT NULL, UNIQUE, CHECK, FOREIGN KEY.

3.2. Common data types (MySQL)

Key idea: DECIMAL for money, BIGINT for big-table ids, VARCHAR with utf8mb4 for strings, and store points in time as UTC.

Group Types Practical notes
Integers TINYINT, INT, BIGINT Use BIGINT for big-table ids from day one; migrating INT to BIGINT on a 100M-row table is painful.
Exact decimals DECIMAL(p, s) Mandatory for money. DECIMAL(12,2): 12 digits, 2 after the point.
Floating point FLOAT, DOUBLE Binary rounding errors (0.1 + 0.2 ≠ 0.3). Never for money.
Strings CHAR(n), VARCHAR(n), TEXT CHAR is fixed-length (country code CHAR(2)), VARCHAR is variable-length.
Date/time DATE, DATETIME, TIMESTAMP TIMESTAMP is stored in UTC, converted to the session time zone, and stops at 2038. DATETIME has no time zone.
Boolean BOOLEAN MySQL: an alias for TINYINT(1). PostgreSQL has a real boolean.
Semi-structured JSON Convenient, but harder to constrain and index than normal columns.

PostgreSQL differences worth knowing: timestamptz, uuid, jsonb, arrays, and TEXT is no slower than VARCHAR.

Which column type does a Java type end up as? With JPA/Hibernate (ORM and JPA), every Java field maps to a column type. Three things people ask about: BigDecimal defaults to DECIMAL(38,2), so always declare precision/scale; String defaults to VARCHAR(255); enums are always stored as strings (Enums in the database and the API).

Hibernate 6 Java-to-SQL type mapping and the slides’ “Data Types” table

Hibernate 6’s default mapping, plus the MySQL type you get when Hibernate generates the table:

Java type Standard SQL type MySQL Notes
int / Integer INTEGER INT
long / Long BIGINT BIGINT The sensible type for ids
short / Short, byte / Byte SMALLINT, TINYINT SMALLINT, TINYINT
float / Float, double / Double FLOAT, DOUBLE PRECISION FLOAT, DOUBLE Never for money
BigDecimal NUMERIC / DECIMAL DECIMAL(38,2) Declare @Column(precision = 12, scale = 2) explicitly
String VARCHAR VARCHAR(255) Change the length with @Column(length = ...)
char / Character CHAR(1) CHAR(1)
boolean / Boolean BOOLEAN BIT(1) In hand-written migrations BOOLEAN (= TINYINT(1)) reads fine too; PostgreSQL has a real boolean
LocalDate, LocalTime DATE, TIME DATE, TIME
LocalDateTime TIMESTAMP DATETIME(6) No time zone
Instant, OffsetDateTime TIMESTAMP WITH TIME ZONE DATETIME(6) or TIMESTAMP(6) MySQL can’t store the offset, so store UTC by convention; PostgreSQL uses timestamptz
UUID (not in the standard) BINARY(16) PostgreSQL has a native uuid type
enum TINYINT (ORDINAL) or VARCHAR/ENUM (STRING) Always use @Enumerated(EnumType.STRING)
byte[] VARBINARY / BLOB VARBINARY(255) Put large files in object storage and keep only the path in the DB

Two string traps that almost every project hits once:

3.3. Primary key

Key idea: a primary key is a column (or combination of columns) that is unique and never NULL, so you can point at exactly one row. With two identical ("Trang", 25) rows, you couldn’t otherwise say which one to update.

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

The slides use id VARCHAR(50) as the PK: it works, but every InnoDB secondary index stores a copy of the PK, so a long string PK bloats them all.

4. Talking to the data: SQL

Key idea: SQL is the standard language for relational databases, and it’s declarative: you say what you want, and the query optimizer decides how to get it (which index, which join order).

Its other strengths: it’s standardized (ANSI/ISO SQL, learn once, use on many systems) and readable.

4.1. Command groups

Key idea: SQL commands fall into five groups by what they do: define structure, change data, read, grant permissions, manage transactions.

Group Commands Purpose
DDL (Data Definition) CREATE, ALTER, DROP, TRUNCATE, RENAME Define structure
DML (Data Manipulation) INSERT, UPDATE, DELETE Change data
DQL (Data Query) SELECT Read data
DCL (Data Control) GRANT, REVOKE Permissions
TCL (Transaction Control) BEGIN, COMMIT, ROLLBACK, SAVEPOINT Manage transactions
  • RENAME is DDL: metadata-only, so it’s fast, but it instantly breaks any code using the old name, so it has to go through expand/contract (section 5.2).
  • Many sources (the slides included) put SELECT under DML; others give it its own group, DQL. You’ll hear both; what matters is that SELECT only reads.
RENAME syntax in MySQL and PostgreSQL
RENAME TABLE student TO students;                      -- MySQL
ALTER TABLE students RENAME TO learners;               -- works in MySQL and PostgreSQL
ALTER TABLE learners RENAME COLUMN full_name TO name;  -- MySQL 8+, PostgreSQL

4.2. Creating a table and CRUD

Key idea: CRUD is the four basic operations: create (INSERT), read (SELECT), update (UPDATE), delete (DELETE). Constraints like NOT NULL, UNIQUE and CHECK let the database reject bad data itself.

CREATE TABLE product (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  sku        VARCHAR(32)   NOT NULL UNIQUE,
  name       VARCHAR(200)  NOT NULL,
  price      DECIMAL(12,2) NOT NULL CHECK (price >= 0),
  category   VARCHAR(50),
  created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO product (sku, name, price, category)
VALUES ('KB-01', 'Mechanical keyboard', 1290000, 'accessory'),
       ('MS-02', 'Wireless mouse',       390000, 'accessory');

SELECT id, name, price FROM product WHERE category = 'accessory';

UPDATE product SET price = 1190000 WHERE sku = 'KB-01';

DELETE FROM product WHERE sku = 'MS-02';

ALTER TABLE product ADD COLUMN stock INT NOT NULL DEFAULT 0;
ALTER TABLE product DROP COLUMN category;

DROP TABLE product;   -- removes both structure and data

4.3. Filtering, sorting, aggregating

Key idea: the syntax is easy to memorize; what you must understand is NULL. SQL uses three-valued logic (TRUE, FALSE, UNKNOWN), and WHERE keeps only TRUE rows.

SELECT DISTINCT category FROM product;                       -- unique values
SELECT COUNT(*), SUM(price), AVG(price), MIN(price), MAX(price) FROM product;
SELECT * FROM product WHERE name LIKE 'Wire%';               -- starts with "Wire"
SELECT * FROM product WHERE category IN ('accessory', 'laptop');
SELECT * FROM product WHERE price BETWEEN 100000 AND 500000;
SELECT * FROM product WHERE category IS NULL;                -- NOT "= NULL"
SELECT * FROM product WHERE price > 500000 AND NOT (category = 'laptop');
SELECT * FROM product ORDER BY price DESC, id ASC LIMIT 10;
  • NULL means “unknown”, not 0 or an empty string. NULL = NULL yields UNKNOWN, so you need IS NULL.
  • Aggregates ignore NULL: COUNT(col) counts only non-NULL values, COUNT(*) counts every row. AVG(score) divides by the rows that have a score.
  • LIKE '%abc' (leading wildcard) can’t use a B+tree index; LIKE 'abc%' can.

5. Changing the schema once the table has data

Key idea: an ALTER that takes milliseconds on a small table can block writes for hours on a 100-million-row one. Before running it, know whether it’s INSTANT, INPLACE or COPY; for backward-incompatible changes, use expand/contract.

The course only teaches the ALTER syntax; this section is what you need to know before running it in production.

5.1. Three ways MySQL runs an ALTER

Key idea: INSTANT changes metadata only, INPLACE rebuilds inside the table and usually still allows writes, COPY copies the whole table and blocks writes. State the ALGORITHM so MySQL errors out instead of silently falling back to COPY.

Algorithm What it does Example operations Concurrent writes?
INSTANT Changes metadata only, data untouched Add a column (at the end from 8.0.12, anywhere from 8.0.29), change a DEFAULT, rename a column Yes
INPLACE Rebuilds structures inside the table Add a secondary index, drop a column on versions without INSTANT support Usually
COPY Creates a new table, copies every row, then renames Change a column type (INT → BIGINT), change the charset No
-- Fail if INSTANT isn't possible, rather than silently falling back to COPY
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;

-- Add an index while still allowing INSERT/UPDATE during the build
ALTER TABLE orders ADD INDEX idx_orders_created (created_at), ALGORITHM=INPLACE, LOCK=NONE;

-- Changing the primary key type only works with COPY and blocks writes for the whole copy.
-- Every FK column pointing at orders.id has to change too.
ALTER TABLE orders MODIFY id BIGINT NOT NULL AUTO_INCREMENT;

That’s why the table in 3.2 says BIGINT for ids from day one: a signed INT runs out at about 2.1 billion, and by the time you’re close, changing the type is a COPY on your biggest table.

Long transactions like that usually happen by accident (a @Transactional method making an HTTP call halfway through, a session someone forgot to commit). How to find them on MySQL and PostgreSQL with innodb_trx and pg_stat_activity, and how to fix them, is in Harmful transactions.

PostgreSQL differs in detail but not in spirit: since PG 11, adding a column with a constant DEFAULT is metadata-only; build an index without blocking writes with CREATE INDEX CONCURRENTLY; changing int to bigint still rewrites the whole table.

Ways to avoid schema changes (JSON columns, spare columns), online DDL with pt-online-schema-change/gh-ost, and the metadata-lock trap are covered in more depth in JSON columns, online DDL, data types and views.

5.2. Expand/contract: changing the schema without stopping the system

Key idea: during a rolling deploy, old and new code run side by side for a few minutes, so every migration must work with both. A breaking change (renaming a column) is split into small steps, one deploy each.

Example: renaming fullname to display_name (a single RENAME COLUMN breaks the old code instantly):

  1. Expand: add a nullable display_name column (INSTANT).
  2. Dual-write: the new code writes both fullname and display_name.
  3. Backfill: copy old data in small batches, e.g. UPDATE ... WHERE id BETWEEN ? AND ? AND display_name IS NULL 5,000 rows at a time, pausing between batches so replicas keep up.
  4. Switch reads: the code reads from display_name; add NOT NULL if needed.
  5. Contract: once no code uses fullname, drop the old column.

Migrations must be versioned like code: Flyway or Liquibase, each change a V7__add_display_name.sql file that gets reviewed and runs exactly once on every environment. Don’t let Hibernate’s ddl-auto=update modify the production schema (see the ORM post). In a Spring Boot project, once migrations exist, set ddl-auto=validate so Hibernate only checks that entities match the tables (the Maven and project layout post).

Comparing three ways to change a big table’s schema

Exercises

These restate the course exercises in my own words; the solutions are mine and were run on MySQL 8.4.

Exercise 1: Student scores table

Task: create a students table with an id (integer PK), name, date of birth, maths score and English score (decimals). Then: (a) get the name and maths score of everyone scoring above 8 in maths; (b) compute the average maths score; (c) set student 1’s English score to 9.5; (d) delete students with a maths score below 5; (e) sort by English score, highest first.

Solution to Exercise 1: the SQL and the NULL traps in the results
CREATE TABLE students (
  student_id    INT PRIMARY KEY,
  student_name  VARCHAR(100) NOT NULL,
  date_of_birth DATE,
  math_score    DECIMAL(4,2) CHECK (math_score BETWEEN 0 AND 10),
  english_score DECIMAL(4,2) CHECK (english_score BETWEEN 0 AND 10)
);

INSERT INTO students VALUES
  (1, 'An',   '2003-04-12', 8.50, 7.00),
  (2, 'Binh', '2002-11-03', 4.50, 6.50),
  (3, 'Chi',  '2003-01-25', 9.25, 8.75),
  (4, 'Dung', '2004-07-30', NULL, 9.00);

-- (a)
SELECT student_name, math_score FROM students WHERE math_score > 8.0;
-- (b)
SELECT ROUND(AVG(math_score), 2) AS avg_math FROM students;
-- (c)
UPDATE students SET english_score = 9.5 WHERE student_id = 1;
-- (d)
DELETE FROM students WHERE math_score < 5.0;
-- (e)
SELECT * FROM students ORDER BY english_score DESC, student_id;
  • DECIMAL(4,2) covers 0.00–10.00 with no FLOAT rounding error, and the CHECK rejects nonsense scores.
  • (b) returns 7.42, which is (8.5 + 4.5 + 9.25) / 3: AVG skips Dung because the score is NULL. To count “no score” as 0, write AVG(COALESCE(math_score, 0)). A favourite interview question.
  • (d) does not delete Dung, because NULL < 5 is UNKNOWN. Whether that’s intended is a question for whoever wrote the requirement.
  • (e) adds student_id as a tie-breaker so the order is always deterministic on equal scores.

More practice: SQL exercise sites

The slides recommend four places to practise SQL. What works: write your answer first, run it, then look at the solution; for each one, also ask “what index would this need with 10 million rows?”.

Four SQL practice sites and what each is good for
  • w3resource SQL tutorial: theory plus lots of small exercises per topic.
  • SQLBolt: short interactive lessons with queries you run in the browser. Good for a quick refresher on SELECT, JOIN and GROUP BY.
  • sql-practice.com: exercises on a hospital database, sorted by difficulty.
  • SQLPad: exercises in the style of company interview questions.

Remember

  • Can you explain why files aren’t enough, and which reason matters most?
  • Do you know which type to pick for money, ids, strings and points in time, and why?
  • Can you explain why UUID v4 is a bad InnoDB primary key, and what to use instead?
  • Can you predict the result of = NULL, NOT IN with a NULL, and AVG/COUNT(col) on a nullable column?
  • Do you know whether an ALTER is INSTANT, INPLACE or COPY, why it can freeze a table, and when you need expand/contract?

Interview questions

Junior

How is a primary key different from a unique key?

Both prevent duplicates; a PK can’t be NULL and there’s only one per table.

You can have many unique keys per table and (in MySQL/Postgres) they allow multiple NULLs. In InnoDB the PK is also the clustered index that determines how rows are laid out on disk.

DELETE vs TRUNCATE vs DROP?

DELETE removes matching rows and can be rolled back; TRUNCATE wipes all rows very fast; DROP removes the table.

DELETE logs each row and fires triggers. TRUNCATE resets auto-increment and, in MySQL, is DDL, so no rollback. DROP removes both data and structure.

Why use a database instead of storing data in files yourself?

Because files have no transactions, constraints, fine-grained permissions or concurrency control; crash halfway and you’re left with half old, half new data.

Files also lead to redundancy and inconsistency (the same data copied in several places), every query means hand-written code, and concurrent writers overwrite each other. A DBMS gives you a query language, constraints, fine-grained permissions, concurrency control, ACID and backup/recovery.

How do CHAR, VARCHAR and TEXT differ?

CHAR(n) is fixed-length, VARCHAR(n) is variable-length (the default choice), TEXT is for long strings.

  • CHAR(n): pads shorter values with spaces; suits fixed-size codes like CHAR(2).
  • VARCHAR(n): adds a 1–2 byte length prefix.
  • TEXT in MySQL: can’t be fully indexed (you need a prefix index), can’t have a literal DEFAULT, and may be stored off the main data page.
  • In PostgreSQL, TEXT and VARCHAR perform the same.
What column types do boolean, BigDecimal and Instant fields become in MySQL?

Hibernate 6 defaults: boolean becomes BIT(1), BigDecimal becomes DECIMAL(38,2), Instant becomes DATETIME(6)/TIMESTAMP(6) stored as UTC.

  • Hand-written migrations often use BOOLEAN (= TINYINT(1)) for booleans.
  • Always declare precision/scale for money.
  • MySQL can’t keep the offset, hence the UTC convention; PostgreSQL has timestamptz.
  • String defaults to VARCHAR(255); store enums as strings with EnumType.STRING.
  • yes_no and true_false are Hibernate 5 mapping types, not column types.

Mid

What’s the trap with NOT IN?

If the subquery returns even one NULL, x NOT IN (...) is UNKNOWN for every x and the result is empty.

Use NOT EXISTS or LEFT JOIN ... WHERE right.id IS NULL (an anti-join) instead.

What does AVG(score) return on a column with NULLs?

AVG ignores NULLs, so it divides by the rows that have a score, not the total row count.

Four students where one has no score yet gives an average over three. If “no score” should count as 0, write AVG(COALESCE(score, 0)). Likewise, COUNT(score) differs from COUNT(*). The question checks that you know NULL means “unknown”, not 0.

What’s the difference between utf8 and utf8mb4 in MySQL?

MySQL’s utf8 is really utf8mb3, at most 3 bytes per character, so no emoji; utf8mb4 is real UTF-8.

utf8mb4 is the default since MySQL 8.0; set it at every layer: database, table, column and connection. Also watch the default collation utf8mb4_0900_ai_ci, which is case- and accent-insensitive and affects comparisons and unique constraints.

Senior

Choosing a primary key: auto-increment, UUID v4 or UUID v7/ULID?

On InnoDB, pick an increasing key: auto-increment, or UUID v7/ULID when ids must be generated in a distributed way; avoid UUID v4.

  • Auto-increment: small and increasing, ideal for a clustered index; but it leaks record counts, can’t be generated client-side, and makes merging shards awkward.
  • UUID v4: can be generated anywhere, but random, so inserts scatter across the B+tree, causing page splits and poor buffer pool usage, and its 16 bytes repeat in every secondary index.
  • UUID v7/ULID: a time prefix keeps them roughly increasing while staying globally unique.
  • Store as BINARY(16) (MySQL) or uuid (Postgres), never VARCHAR(36).
What types do you use for money and for time?

Money: DECIMAL (or an integer in the smallest unit). Time: store UTC.

  • Never FLOAT/DOUBLE for money, because of binary rounding.
  • MySQL: DATETIME has no time zone (the application must guarantee UTC), TIMESTAMP converts but stops at 2038. Postgres: use timestamptz.
  • Java: map to Instant/OffsetDateTime, not LocalDateTime, for absolute points in time.
How do you add a column or change a column type on a 100-million-row table without downtime?

Find out whether the operation is INSTANT, INPLACE or COPY; use gh-ost/pt-online-schema-change for COPY, and expand/contract for backward-incompatible changes.

  • Adding a column or changing a default: INSTANT. Adding an index: INPLACE without blocking writes. Changing a column type: COPY, blocks writes.
  • State ALGORITHM=INSTANT/LOCK=NONE so MySQL errors out instead of silently copying; set a low lock_wait_timeout, because the ALTER still needs a metadata lock for a moment.
  • Renaming or splitting a column: add the new column, dual-write, backfill in batches, switch reads, then drop the old one.
  • All of it managed with Flyway/Liquibase.

Useful phrases for interviews

  • “I’d enforce that rule in the database with a unique constraint, not just in application code.”
  • “Without an ORDER BY, the result order isn’t guaranteed, so pagination wouldn’t be deterministic.”
  • “I keep money in DECIMAL and timestamps in UTC to avoid rounding and time-zone bugs.”
  • “NULL means unknown, so I compare with IS NULL and I’m careful with NOT IN and with averages over nullable columns.”
  • “I’d use a surrogate primary key and put a unique constraint on the natural key, like the email.”
  • “I always set the charset to utf8mb4, because MySQL’s utf8 can’t store emoji.”
  • “Before touching a big table in production, I check whether the ALTER can run INSTANT or INPLACE.”
  • “For a breaking change I’d use expand and contract: add the column, dual-write, backfill, switch reads, then drop the old one.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.