Master Relational Database Design: From Basics to Normalization

Relational Database Introduction

Relational databases were proposed by Edgar Codd around 1969 and have become the dominant model for commercial applications. Popular commercial RDBMS include Oracle, IBM DB2, Microsoft SQL Server, while open‑source options include MySQL, MariaDB, and Apache Derby.

Data is stored in tables composed of rows (records) and columns (fields). Tables can be related to each other, enabling efficient storage and retrieval.

SQL (Structured Query Language) is used to manipulate relational databases.

Relational Database Design Steps

Design requires experience and many decisions; guidelines often focus on what not to do rather than what to do.

1. Requirement Analysis

Gather all functional requirements and define the purpose of the database, e.g., a bookstore application needs tables for books, authors, publishers, customers, orders, etc.

2. Collect Data, Organize Tables, Define Primary Keys

Identify the data to be stored, group related data into separate tables, and choose a column (or columns) as the primary key.

About Primary Keys

Each table must have a unique, non‑null primary key. Simple keys consist of a single column; composite keys consist of multiple columns. Primary keys are used to build indexes and serve as foreign keys in related tables.

The primary key value must be unique.

The primary key cannot be null.

Best practices:

Primary key values should not be modified.

Prefer integer types for efficiency.

Use simple keys when possible; keep composite keys minimal.

Most databases can automatically add an auto‑increment integer column as the primary key.

3. Establish Relationships

Relationships between tables are essential. The three common types are one‑to‑many, many‑to‑many, and one‑to‑one.

One‑to‑Many

Example: a mother can have many children, but each child has only one mother. Implemented by storing MotherID as a foreign key in the Children table.

SQL to create the tables:

CREATE TABLE `Mothers` (
  `MotherID` INTEGER NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(100) NOT NULL,
  `Age` SMALLINT NOT NULL,
  `BloodType` VARCHAR(2) NOT NULL,
  PRIMARY KEY (`MotherID`)
);
CREATE TABLE `Children` (
  `ChildrenID` INTEGER NOT NULL AUTO_INCREMENT,
  `MotherID` INTEGER NOT NULL,
  `Name` VARCHAR(100) NOT NULL,
  `Age` SMALLINT NOT NULL,
  `Sex` VARCHAR(50) NOT NULL,
  `BloodType` VARCHAR(2) NOT NULL,
  PRIMARY KEY (`ChildrenID`),
  FOREIGN KEY (`MotherID`) REFERENCES `Mothers` (`MotherID`)
);

Many‑to‑Many

Example: orders contain multiple products and a product can appear in many orders. Implemented with a junction table (OrderDetails) that holds foreign keys to Orders and Products.

SQL to create the tables:

CREATE TABLE `Orders` (
  `OrderID` INTEGER NOT NULL AUTO_INCREMENT,
  `OrderDate` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `CustomerID` INTEGER NOT NULL,
  PRIMARY KEY (`OrderID`)
);
CREATE TABLE `Products` (
  `ProductID` INTEGER NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(100) NOT NULL,
  `Stock` INTEGER DEFAULT 0,
  PRIMARY KEY (`ProductID`)
);
CREATE TABLE `OrderDetails` (
  `OrderID` INTEGER NOT NULL,
  `ProductID` INTEGER NOT NULL,
  FOREIGN KEY (`OrderID`) REFERENCES `Orders` (`OrderID`),
  FOREIGN KEY (`ProductID`) REFERENCES `Products` (`ProductID`),
  PRIMARY KEY (`OrderID`, `ProductID`)
);

One‑to‑One

Example: a product may have a single detailed description stored in a separate ProductDetails table.

SQL to create the tables:

CREATE TABLE `Products` (
  `ProductID` INTEGER NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`ProductID`)
);
CREATE TABLE `ProductDetails` (
  `ProductID` INTEGER NOT NULL AUTO_INCREMENT,
  `DetailInfo` VARCHAR(65535),
  FOREIGN KEY (`ProductID`) REFERENCES `Products` (`ProductID`),
  PRIMARY KEY (`ProductID`)
);

Normalization

Normalization organizes data to reduce redundancy. Common normal forms are 1NF, 2NF, 3NF, and BCNF. Generally, achieving 3NF is sufficient for most applications.

First Normal Form (1NF)

Each field contains atomic values; no repeating groups.

Second Normal Form (2NF)

All non‑key attributes must be fully dependent on the primary key.

Third Normal Form (3NF)

No non‑key attribute should depend on another non‑key attribute.

Boyce‑Codd Normal Form (BCNF)

Even stricter than 3NF; eliminates any dependency of a non‑key attribute on another non‑key attribute.

Integrity Rules

Entity Integrity : each table must have a unique, non‑null primary key.

Referential Integrity : foreign keys must reference existing primary keys, preventing orphan records.

Domain Integrity : columns must conform to defined data types, ranges, and constraints.

User‑Defined Integrity : business‑specific rules such as value ranges or logical constraints.

Indexing

Indexes accelerate SELECT queries but can slow INSERT, UPDATE, and DELETE operations. Indexes can be single‑column, composite, or partial, and most RDBMS automatically index primary keys.

Conclusion

Effective relational database design starts with clear goals, splits data into logical tables, defines relationships, applies normalization, enforces integrity rules, and adds indexes for high‑frequency queries.