Data Redundancy and Normalization Principles

Role normalization separates role information into a dedicated Role table, eliminating redundancy and centralizing role management.

Programming

Interactive lab

Try it: Keys and Constraints

How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.

How it works

INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).

Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.

Educational simulation

Loading the simulation…

Repeated Role Information

Imagine a Member table in which every member row stores the full name of the member's role. If several members share the same role, that role information is repeated across rows. Role normalization changes this arrangement: role information moves into a dedicated Role table, while Member rows keep a role_id value that refers to the appropriate Role record.

What changes when repeated role names are moved out of Member rows and placed in a dedicated Role table?

The Normalized Table Design

The normalized design separates responsibilities between two tables. The Role table stores role information. It has an id column defined as the PRIMARY KEY and a name column defined as UNIQUE. The Member table stores member information and includes role_id as a foreign key that references the Role table.

Definition

Role normalization is the separation of role information into a dedicated Role table so that role management is centralized and redundant role data is eliminated.

Table	Column	Constraint or purpose
Role	id	PRIMARY KEY
Role	name	UNIQUE
Member	role_id	FOREIGN KEY referencing Role

The essential columns and constraints in the normalized role design.

How does a Member row use role_id to connect to an existing Role record?

Constraint Roles

The two Role constraints protect different properties. The PRIMARY KEY on Role.id identifies the Role record. The UNIQUE constraint on Role.name prevents duplicate role names in the Role table. Together, they keep role records identifiable by id while keeping each role name distinct.

Which Role values must remain unique, and what does each constraint protect?

Primary Key Insertion Choices

Worked example

Adding a New Role

A new Role record must be inserted. Should the insertion provide a value for id?

1
Omit id
When the PRIMARY KEY value is omitted, the database can auto-generate that value.
2
Provide id
A PRIMARY KEY value may instead be supplied manually, but the supplied value must be unique and must not already be present.
3
Connect a member
After a Role record exists, a Member row can use its role_id value to reference that Role record.

Result

The insertion can either omit the PRIMARY KEY value to allow auto-generation or provide a valid, unused value manually.

What happens to the primary key when an insertion leaves that column out?

What do you think happens?

A Role insertion leaves the id column out. What happens to the PRIMARY KEY value?

The database can auto-generate the value
The Member table supplies the value
The role name becomes the PRIMARY KEY

Reveal answer

Answer:

The database can auto-generate the value.

The PRIMARY KEY value may be omitted to allow auto-generation by the database.

Manual Identifier Limits

Manual assignment is allowed, but it is not unrestricted. A manually supplied PRIMARY KEY value must be unique and must not already be present. Supplying an existing value conflicts with the PRIMARY KEY requirement. Supplying a value that violates the column's constraints is likewise not a valid insertion.

What distinguishes an acceptable manually supplied primary key from a conflicting one?

Common mistake
Treating role_id as a replacement for the Role table
What it looks like
A Member row contains a role_id value, but there is no corresponding Role record.
Why it fails
The foreign key constraint requires the Member reference to point to an existing Role record.
Do this instead
Create or identify the Role record first, then use its id as the Member row's role_id.
Common mistake
Assuming any manually chosen PRIMARY KEY value is acceptable
What it looks like
An insertion supplies an id that is already present in the Role table.
Why it fails
A manually assigned PRIMARY KEY must be unique and must not already be present.
Do this instead
Use an unused value or omit the id so the database can auto-generate it.
Common mistake
Storing each role name repeatedly in Member rows
What it looks like
Several Member rows each store the same role name instead of referring to one Role record.
Why it fails
This keeps role information redundant rather than centralizing it in the dedicated Role table.
Do this instead
Store role names in Role and connect Member rows through role_id.

Apply the Design

MEDIUM

Describe the normalized design for a Member table and a Role table. Include the required key or constraint for Role.id, the required constraint for Role.name, and the column in Member that references Role. Then explain the two valid choices for handling the Role PRIMARY KEY during insertion.

Hints

Role.id is the identifying column.
Role.name must be unique.
Member.role_id is the foreign key.
The PRIMARY KEY can be omitted for database auto-generation or supplied manually when the value is unique and not already present.

Your attempt (not saved)

What must be true before a Member row can use a role_id value?

Key Takeaways

1
Role normalization places role information in a dedicated Role table instead of repeating it in Member rows.
2
Role.id is the PRIMARY KEY, while Role.name has a UNIQUE constraint.
3
Member.role_id is a foreign key that must reference an existing Role record.
4
A PRIMARY KEY value may be omitted so the database can auto-generate it.
5
A manually assigned PRIMARY KEY value must be unique and must not already be present.

At a glance

Normalization separates role data from Member rows and centralizes it in Role.
The Role table uses PRIMARY KEY id and UNIQUE name constraints.
The Member table connects to Role through the foreign key role_id.
Foreign key enforcement prevents references to non-existent Role records.
Primary key values may be omitted for auto-generation or supplied manually only when unique and not already present.

Related concepts

More concepts starting with D