Designing Databases as Intelligent Warehouses: A Supermarket Analogy

Database Basics and Types

The article starts by comparing a database to a "super‑Excel" that can handle massive, concurrent data, unlike a simple spreadsheet. It distinguishes relational databases (e.g., MySQL, Oracle, SQL Server) that provide structured tables, SQL queries, and ACID guarantees, from NoSQL databases (e.g., Redis, MongoDB, HBase, Neo4j) that store flexible key‑value, document, column‑family, or graph data for high‑throughput, schema‑less scenarios such as social feeds, product caches, and log storage.

Normalization (1NF, 2NF, 3NF)

Normalization is presented as the "shelf‑placement rules" in a supermarket. 1NF requires each column to hold atomic values; the article shows a bad table where a "product info" column mixes name, size, and flavor, and a corrected table that splits these into separate columns. 2NF eliminates partial dependencies by ensuring every non‑key column depends on the whole primary key; a composite‑key example with product and supplier data demonstrates redundancy and the need to split into two tables. 3NF removes transitive dependencies, illustrated by a table where supplier address depends on supplier name, which in turn depends on the product key; the solution is to separate supplier information into its own table. The article warns against over‑normalization, suggesting 2NF for simple blogs, 3NF for e‑commerce, and occasional denormalization for high‑concurrency use cases.

Indexing

Indexes are likened to library catalog cards that turn slow full‑table scans into fast look‑ups. Types covered include primary key indexes (the "barcode"), unique indexes (unique model numbers), normal indexes (category tags), composite indexes (combined tags like "snack <5¥"), clustered indexes (the actual storage order), and full‑text indexes (keyword search). The article explains how each type works, their storage structures (e.g., B+‑tree for primary keys), and when to use them, with concrete SQL snippets such as SELECT * FROM user WHERE id = 1001; versus the wrong SELECT * FROM user WHERE id = '1001';.

Transaction ACID

Transactions are compared to bank transfers that must succeed or fail as a whole. The four properties are detailed: Atomicity (all‑or‑nothing), Consistency (total amount unchanged), Isolation (intermediate states invisible to other transactions), and Durability (committed data survives crashes). Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) are tabulated with their allowed phenomena, performance impact, and typical use cases. MySQL defaults to Repeatable Read, Oracle to Read Committed.

SQL Optimization Techniques

Avoid functions or expressions on indexed columns (e.g., replace WHERE DATE(create_time) = '2023-10-01' with a range query).

Do not start LIKE patterns with a leading %; use trailing % or full‑text indexes.

Match data types to prevent implicit conversion (e.g., WHERE id = 1001 instead of WHERE id = '1001').

Prefer IN over OR for indexed columns.

Select only needed columns instead of SELECT * to reduce I/O and enable covering indexes.

Index join columns and drive large tables with small tables.

Limit the number of joined tables (prefer ≤3) and consider denormalization when joins become costly.

Index fields used in aggregation and sorting; avoid filesort by ordering on indexed columns.

For pagination, avoid large OFFSET; use a primary‑key filter like WHERE id > 10000 LIMIT 10.

EXPLAIN Analysis

The EXPLAIN command is described as a diagnostic microscope. Important fields include id (execution order), select_type, table, type (index quality hierarchy), key (used index), rows (estimated rows), and Extra (e.g., Using index, Using filesort). The article shows how to interpret these signals to identify full‑table scans or missing indexes.

Sharding (Partitioning)

When data reaches millions or billions, single‑table performance degrades. The article introduces vertical partitioning (splitting by business modules such as user, order, product databases) and horizontal partitioning (splitting by data range, e.g., user‑id modulo or monthly order tables). Strategies include modulo, time‑range, geographic, and range‑based splitting, each with pros and cons. Example: an order table with 100 million rows is divided into eight tables order_0 … order_7, and queries route to the appropriate table using user_id % 8.

Middleware and Challenges

Manual sharding is error‑prone, so middleware like ShardingSphere, MyCat, and Alibaba DRDS are recommended. Challenges such as cross‑database joins, distributed transactions, global ID generation, and data migration are discussed, with solutions like two‑phase commit, TCC, message‑queue async processing, Snowflake IDs, pre‑allocation of table counts, and dual‑write migration.

Pitfalls (Top 10)

Over‑normalizing tables (excessive joins, poor performance).

Too many or too few indexes.

Using SELECT * unnecessarily.

Applying functions to indexed columns.

Not partitioning large tables.

Ignoring isolation levels, causing dirty reads.

Neglecting distributed transaction handling.

Storing large BLOB/TEXT fields in core tables.

Large OFFSET pagination.

Skipping read/write separation.

Conclusion

By treating database design as an "intelligent warehouse"—balancing structure, performance, and consistency—readers can master the essential concepts needed for certification exams and real‑world architecture. The supermarket analogies, concrete table examples, exam questions, and optimization tips together form a practical roadmap for building robust, scalable databases.