Mastering Database Design: Normal Forms, Keys, and Constraints Explained

Today we discuss fundamental database concepts: the three main characteristics—entity, attribute, and relationship.

Entity: a table; Attribute: a column (field) within the table; Relationship: the link between tables.

Database design relies on three normal forms (1NF, 2NF, 3NF).

First Normal Form (1NF) : each column must contain indivisible atomic values. Example: separate address components instead of storing "山东省烟台市 131777368781" in a single field.

Second Normal Form (2NF) : after satisfying 1NF, every column must depend on the primary key, and a table should describe only one entity. Example: an Order table contains only order‑related fields, while a Product table contains only product‑related fields; they should not be mixed in a single table.

Third Normal Form (3NF) : in addition to 2NF, each column must depend directly on the primary key, not indirectly through another column. Example: after extracting customer information into a separate Customer table, the Order table keeps only a customer_id, not other customer details.

To differentiate the three normal forms: 1NF vs 2NF is about whether you have split data into multiple tables; 2NF requires separate tables for distinct entities; 3NF requires that each table contains only foreign‑key references (ids) to other tables, with no additional non‑key data.

Five major constraints :

PRIMARY KEY – defines the primary key constraint.

UNIQUE – ensures column values are unique.

DEFAULT – sets a default value (e.g., height DOUBLE(3,2) DEFAULT 1.2).

NOT NULL – disallows null values.

FOREIGN KEY – defines a foreign‑key constraint.

Primary Key considerations:

Implicitly NOT NULL and UNIQUE; only primary keys can be auto‑increment.

Define during column creation: ID INT PRIMARY KEY.

Or add later: PRIMARY KEY (id).

Foreign Key considerations:

Supported only by InnoDB engine; set default-storage-engine=INNODB in my.ini.

Data types of foreign key and referenced column must match.

Syntax:

CONSTRAINT fk_name FOREIGN KEY (fk_column) REFERENCES ref_table (ref_column) ON DELETE SET NULL ON UPDATE CASCADE

.

Referential actions include:

RESTRICT (or NO ACTION in MySQL) – prevent delete/update of referenced rows.

CASCADE – propagate delete/update to child rows.

SET NULL – set foreign key to NULL on delete/update.