1NF Violations — First Normal Form (1NF):… | OneNoughtOne

0/131

Database Management SystemsFirst Normal Form (1NF)

First Normal Form (1NF): Foundation of Normalization

LevelBeginner

Duration60 mins

TopicFirst Normal Form (1NF)

4 / 5

1NF Violations

Recognizing Structural Anti-Patterns

Understanding 1NF requirements is one thing; recognizing violations in real-world schemas is another. Violations often hide in plain sight—in legacy systems that "work fine," in designs inherited from spreadsheet migrations, in schemas created by developers unfamiliar with normalization principles. This page serves as a comprehensive field guide to 1NF violations. We'll examine each type of violation in detail, understand why it violates 1NF, see the concrete problems it causes, and learn to spot the warning signs that indicate trouble. By the end, you'll be able to audit any schema for 1NF compliance with confidence.

What You Will Learn

By the end of this page, you will master a complete taxonomy of 1NF violations, understand the specific technical problems each violation type causes, recognize subtle violations that pass casual review, learn diagnostic queries to detect violations in existing databases, and understand contextual factors that determine violation severity.

The 1NF Requirements Revisited

Before examining violations, let's establish the complete set of 1NF requirements. A relation is in First Normal Form if and only if: Requirement 1: Atomic Values Every attribute contains only scalar (indivisible) values No attribute contains sets, lists, arrays, or composite structures Values are atomic with respect to the application's data manipulation needs Requirement 2: No Repeating Groups No column set is repeated horizontally (Item1, Item2, Item3...) No implicit arrays exist in the column structure Related multi-valued data resides in separate tables Requirement 3: Unique Row Identification Every row is uniquely identifiable by a primary key No duplicate rows exist in the relation Requirement 4: Column Homogeneity All values in a column are from the same domain No column contains mixed data types or meanings Requirement 5: Row and Column Order Independence The meaning of the data doesn't depend on row order The meaning of the data doesn't depend on column order

Beyond the Classic Definition

The classic 1NF definition focuses on atomicity and repeating groups. However, the full relational model includes additional structural requirements (unique rows, order independence) that are sometimes omitted from 1NF discussions. We include them here because violations of these requirements cause similar practical problems.

Violation Type 1: Multi-Valued Cells

The most recognizable 1NF violation: storing multiple values of the same type in a single cell, typically as delimited strings.

multi-valued-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- VIOLATION EXAMPLES
 
-- Comma-separated lists
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    Skills VARCHAR(500)  -- 'Java, Python, SQL, Docker'
);
 
-- Pipe-separated values
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Colors VARCHAR(200)  -- 'Red|Blue|Green|Yellow'
);
 
-- Semicolon-separated emails
CREATE TABLE Contacts (
    ContactID INT PRIMARY KEY,
    Name VARCHAR(100),
    EmailAddresses VARCHAR(1000)  -- 'work@co.com; personal@gmail.com; backup@mail.com'
);
 
-- Space-separated codes
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    PromoCodes VARCHAR(100)  -- 'SAVE10 FREESHIP BONUS20'
);

Problems Caused by Multi-Valued Cells

•Query Complexity — Finding employees with 'Python' skill requires WHERE Skills LIKE '%Python%', which incorrectly matches 'PythonScript', 'IronPython', etc.
•No Index Utilization — The LIKE '%value%' pattern forces full table scans; no index can optimize this query.
•Aggregation Impossible — Counting employees per skill is not possible with standard SQL; requires complex string parsing.
•Referential Integrity Impossible — Cannot enforce that skills reference a valid Skills master table.
•Update Anomalies — Removing 'Python' from all employees requires complex string manipulation, prone to errors.
•Inconsistent Formatting — 'Java,Python' vs 'Java, Python' vs 'Java , Python' all represent the same data differently.

Detection query: You can sometimes detect multi-valued columns by looking for delimiter characters:

detect-multi-valued.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Detect likely multi-valued columns by checking for delimiters
SELECT 
    'Skills column may be multi-valued' AS Warning,
    COUNT(*) AS RowsWithCommas
FROM Employees
WHERE Skills LIKE '%,%';
 
-- Check for multiple values by counting delimiters
SELECT 
    EmpID,
    Skills,
    (LENGTH(Skills) - LENGTH(REPLACE(Skills, ',', ''))) + 1 AS ValueCount
FROM Employees
WHERE Skills LIKE '%,%'
ORDER BY ValueCount DESC;

The Inconsistency Trap

Multi-valued cells inevitably drift into inconsistency. Some rows use commas, others semicolons. Some have spaces after delimiters, others don't. Some have trailing delimiters. Application code must handle all variations, leading to bugs and maintenance nightmares.

Violation Type 2: Repeating Column Groups

Repeating column groups embed arrays horizontally in the schema itself. This pattern is often called "spreadsheet-style" design.

repeating-column-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- VIOLATION EXAMPLES
 
-- Numbered columns
CREATE TABLE Surveys (
    SurveyID INT PRIMARY KEY,
    Respondent VARCHAR(100),
    Answer1 VARCHAR(500),
    Answer2 VARCHAR(500),
    Answer3 VARCHAR(500),
    Answer4 VARCHAR(500),
    Answer5 VARCHAR(500)
);
 
-- Date-based columns
CREATE TABLE Inventory (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Stock_Jan INT,
    Stock_Feb INT,
    Stock_Mar INT,
    Stock_Apr INT,
    -- ... 12 columns total
    Stock_Dec INT
);
 
-- Category-paired columns
CREATE TABLE Revenue (
    Year INT PRIMARY KEY,
    Electronics_Revenue DECIMAL(15,2),
    Electronics_Cost DECIMAL(15,2),
    Clothing_Revenue DECIMAL(15,2),
    Clothing_Cost DECIMAL(15,2),
    Food_Revenue DECIMAL(15,2),
    Food_Cost DECIMAL(15,2)
);
 
-- Contact type columns
CREATE TABLE Companies (
    CompanyID INT PRIMARY KEY,
    CompanyName VARCHAR(100),
    CEO_Name VARCHAR(100),
    CEO_Email VARCHAR(100),
    CFO_Name VARCHAR(100),
    CFO_Email VARCHAR(100),
    CTO_Name VARCHAR(100),
    CTO_Email VARCHAR(100)
);

Problems Caused by Repeating Column Groups

•Artificial Capacity Limits — Only 5 answers? Only 12 months? When requirements exceed schema limits, costly ALTER TABLE is needed.
•Sparse Data Waste — Surveys with 3 answers waste Answer4 and Answer5 columns. Multiplied by millions of rows = significant storage waste.
•Complex Queries — 'Find surveys mentioning keyword X' requires WHERE Answer1 LIKE '%X%' OR Answer2 LIKE '%X%' OR...
•Aggregate Nightmares — Total stock across all months requires Stock_Jan + Stock_Feb + ... + Stock_Dec.
•Schema Maintenance Burden — Adding a new product category requires DDL changes plus application updates.
•Reporting Inflexibility — Pivoting data for analysis is extremely difficult when it's already pivoted in the schema.

Detection approach: Repeating columns typically follow naming patterns you can detect programmatically:

detect-repeating-columns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Query information_schema for numbered column patterns
-- PostgreSQL / MySQL syntax
SELECT 
    table_name,
    column_name,
    ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND (
    column_name ~ '^[A-Za-z]+[0-9]+$'           -- Ends with numbers: Answer1, Answer2
    OR column_name ~ '^[A-Za-z]+_[0-9]+$'       -- Pattern: Col_1, Col_2
    OR column_name ~ '^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)_' -- Month prefixes
    OR column_name ~ '_(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)$' -- Month suffixes
  )
ORDER BY table_name, ordinal_position;
 
-- Check for sparse data in suspected repeating columns
SELECT 
    COUNT(*) AS TotalRows,
    COUNT(Answer1) AS Answer1Filled,
    COUNT(Answer2) AS Answer2Filled,
    COUNT(Answer3) AS Answer3Filled,
    COUNT(Answer4) AS Answer4Filled,
    COUNT(Answer5) AS Answer5Filled
FROM Surveys;
 
-- If Answer4 and Answer5 are mostly NULL, it's likely a repeating group violation

Violation Type 3: Embedded Complex Types

Modern databases support JSON, XML, and other complex types. While these have legitimate uses, embedding query-critical data in complex types violates the spirit of 1NF.

embedded-complex-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- VIOLATION EXAMPLES
 
-- JSON blob for structured business data
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderData JSON  -- Contains: items, shipping, payment, all in one blob
);
 
-- XML for what should be relational data
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    EmploymentHistory XML  -- <jobs><job><company>Acme</company><years>3</years></job>...</jobs>
);
 
-- Serialized objects
CREATE TABLE Sessions (
    SessionID VARCHAR(100) PRIMARY KEY,
    UserID INT,
    SessionState BLOB  -- Serialized application state object
);
 
-- Properties column for variable attributes
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    BasePrice DECIMAL(10,2),
    Properties JSON  -- '{"color":"red","size":"XL","weight":2.5}'
);

Acceptable JSON Usage

•Audit logs storing raw API responses
•Configuration blobs read/written as units
•External data preserved for compliance
•Truly schema-less user-defined data
•Temporary staging before normalization

1NF Violation Indicators

•JSON fields used in WHERE clauses
•JSON properties joined with other tables
•Aggregate functions on JSON array elements
•Application parses JSON for every read
•Updates modify individual JSON properties

The Performance Illusion

JSON columns can seem performant in development with small datasets. But JSON path queries don't use B-tree indexes effectively. As data grows, queries that extract JSON properties become exponentially slower. What worked at 10,000 rows fails catastrophically at 10 million.

Violation Type 4: Missing or Ineffective Primary Keys

A relation must have a primary key that uniquely identifies each row. Violations occur when tables lack primary keys entirely, or when the designated key doesn't actually provide uniqueness.

primary-key-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- VIOLATION EXAMPLES
 
-- No primary key at all
CREATE TABLE EventLogs (
    EventTime TIMESTAMP,
    EventType VARCHAR(50),
    EventData TEXT
    -- No PRIMARY KEY defined - duplicates possible!
);
 
-- Primary key that doesn't ensure uniqueness in practice
CREATE TABLE PageViews (
    ViewID INT PRIMARY KEY,  -- Auto-increment seems fine...
    UserID INT,
    PageURL VARCHAR(500),
    ViewTime TIMESTAMP
);
-- Problem: The same logical event (user X viewed page Y at time Z) 
-- can be inserted multiple times with different ViewIDs
 
-- Composite key that's incomplete
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID)
);
-- Problem: What if a student can enroll in the same course 
-- multiple times (different semesters)? Key doesn't capture this.
 
-- Key on unstable data
CREATE TABLE Customers (
    Email VARCHAR(100) PRIMARY KEY,  -- Users change emails!
    Name VARCHAR(100),
    Phone VARCHAR(20)
);

Problems Caused by Primary Key Issues

•Duplicate Data — Without uniqueness enforcement, identical rows accumulate, corrupting aggregations and reports.
•Update Ambiguity — UPDATE table SET x = y WHERE ... might affect unexpected rows if duplicates exist.
•Delete Uncertainty — Which row are you deleting when multiple identical rows exist?
•Foreign Key Failure — Cannot create foreign keys to tables without primary keys.
•ORM Conflicts — Object-Relational Mappers require unique identifiers to function correctly.
•Replication Issues — Some replication mechanisms require primary keys to track changes.

detect-pk-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Find tables without primary keys
-- PostgreSQL syntax
SELECT 
    t.table_schema,
    t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc 
    ON t.table_schema = tc.table_schema 
    AND t.table_name = tc.table_name 
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND tc.constraint_name IS NULL;
 
-- Find tables with potential duplicate issues
-- (Check if all rows are truly unique by all columns)
SELECT 'EventLogs may have duplicates' AS Warning
FROM (
    SELECT EventTime, EventType, EventData, COUNT(*) as cnt
    FROM EventLogs
    GROUP BY EventTime, EventType, EventData
    HAVING COUNT(*) > 1
) duplicates
LIMIT 1;

Violation Type 5: Mixed-Domain Columns

Each column should contain values from a single, well-defined domain. Violations occur when columns store semantically different types of data based on row context.

mixed-domain-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- VIOLATION EXAMPLES
 
-- Value column with different meanings
CREATE TABLE Settings (
    SettingID INT PRIMARY KEY,
    SettingName VARCHAR(100),
    SettingValue VARCHAR(500)  -- Sometimes a number, sometimes text, sometimes JSON...
);
-- Row 1: 'max_connections', '100' (number)
-- Row 2: 'welcome_message', 'Hello World' (text)
-- Row 3: 'feature_flags', '{"beta":true}' (JSON)
 
-- Polymorphic foreign key
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentText TEXT,
    EntityType VARCHAR(50),    -- 'Article', 'Product', 'User'
    EntityID INT               -- Foreign key to different tables based on EntityType!
);
-- No referential integrity possible!
 
-- Overloaded meaning based on type
CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    TransactionType VARCHAR(20),  -- 'SALE', 'REFUND', 'TRANSFER'
    Amount DECIMAL(10,2),
    Reference VARCHAR(100)  -- Order ID for SALE, Original transaction for REFUND, Account for TRANSFER
);
 
-- Multipurpose ID column
CREATE TABLE Activities (
    ActivityID INT PRIMARY KEY,
    ActivityType VARCHAR(50),
    RelatedID INT  -- ProductID, OrderID, or CustomerID depending on ActivityType
);

Problems Caused by Mixed-Domain Columns

•Type Confusion — Is '100' a string or a number? Application code must interpret based on context.
•No Type Safety — Cannot use CHECK constraints or proper data types when meaning varies.
•Broken Foreign Keys — Polymorphic FKs cannot have database-enforced referential integrity.
•Query Complexity — Every query needs CASE statements to handle different interpretations.
•Index Inefficiency — Indexes cover all values but queries filter by subset, wasting space.
•Documentation Burden — External documentation required to explain what values mean in what context.

Proper solutions for polymorphic relationships:

fix-polymorphic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- SOLUTION 1: Separate foreign key columns (sparse but explicit)
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentText TEXT,
    ArticleID INT REFERENCES Articles(ArticleID),
    ProductID INT REFERENCES Products(ProductID),
    UserProfileID INT REFERENCES Users(UserID),
    -- Constraint: exactly one should be non-NULL
    CONSTRAINT exactly_one_parent CHECK (
        (ArticleID IS NOT NULL)::INT + 
        (ProductID IS NOT NULL)::INT + 
        (UserProfileID IS NOT NULL)::INT = 1
    )
);
 
-- SOLUTION 2: Separate child tables (fully normalized)
CREATE TABLE ArticleComments (
    CommentID INT PRIMARY KEY,
    ArticleID INT NOT NULL REFERENCES Articles(ArticleID),
    CommentText TEXT
);
 
CREATE TABLE ProductComments (
    CommentID INT PRIMARY KEY,
    ProductID INT NOT NULL REFERENCES Products(ProductID),
    CommentText TEXT
);
 
-- SOLUTION 3: Abstract parent table (most flexible)
CREATE TABLE Commentable (
    CommentableID INT PRIMARY KEY,
    CommentableType VARCHAR(20) NOT NULL  -- For application use only
);
 
CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY REFERENCES Commentable(CommentableID),
    Title VARCHAR(200)
);
 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY REFERENCES Commentable(CommentableID),
    ProductName VARCHAR(100)
);
 
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentableID INT NOT NULL REFERENCES Commentable(CommentableID),
    CommentText TEXT
);

Violation Type 6: Encoded Information

Information encoding packs multiple facts into a single value using conventions that require parsing to extract meaning. This is a subtle form of non-atomicity.

encoded-info-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- VIOLATION EXAMPLES
 
-- Smart keys with embedded information
CREATE TABLE Products (
    -- ProductCode 'ELEC-2024-NYC-00142' encodes:
    -- Category (ELEC), Year (2024), Warehouse (NYC), Sequence (00142)
    ProductCode VARCHAR(25) PRIMARY KEY,
    Description VARCHAR(200),
    Price DECIMAL(10,2)
);
-- Queries like "all electronics" require: WHERE ProductCode LIKE 'ELEC-%'
 
-- Status flags as strings
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    StatusFlags VARCHAR(20)  -- 'AVN' = Active, Verified, Notifications-on
);
-- Must parse each character position for meaning
 
-- Date-encoded identifiers
CREATE TABLE Invoices (
    InvoiceNumber VARCHAR(20) PRIMARY KEY,  -- '2024031501234'
    -- Encodes: YYYYMMDD + sequence = year, month, day, invoice number
    CustomerID INT,
    Amount DECIMAL(10,2)
);
 
-- Positional encoding
CREATE TABLE Accounts (
    AccountNumber VARCHAR(20) PRIMARY KEY,  
    -- Position 1-3: Branch code
    -- Position 4: Account type (S=Savings, C=Checking)
    -- Position 5-12: Customer number
    -- Position 13: Check digit
    Balance DECIMAL(15,2)
);

Problems Caused by Encoded Information

•Parsing Overhead — Every query, report, and application must know the encoding scheme and parse accordingly.
•No Indexing on Components — Cannot index 'category' component of a smart key efficiently.
•Fragile Queries — SUBSTRING(ProductCode, 1, 4) breaks if format changes.
•Validation Complexity — Must validate each component position separately.
•Documentation Dependency — Meaning is not self-describing; requires external documentation.
•Evolution Difficulty — Adding new information to the encoding scheme may require changing all existing values.

fix-encoded.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- CORRECTED DESIGN: Explicit atomic columns
 
CREATE TABLE Products (
                    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    Category VARCHAR(20) NOT NULL,
    ProductionYear INT NOT NULL,
    WarehouseCode VARCHAR(10) NOT NULL,
    Description VARCHAR(200),
    Price DECIMAL(10,2),
    -- Unique business identifier can still exist
    LegacyCode VARCHAR(25) UNIQUE  -- For backward compatibility
);
 
-- Now you can:
-- - Index and query by Category directly
-- - Filter by ProductionYear with range queries
-- - JOIN with Warehouse table
-- - Add new attributes without encoding changes
 
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    IsActive BOOLEAN NOT NULL DEFAULT TRUE,
    IsVerified BOOLEAN NOT NULL DEFAULT FALSE,
    NotificationsEnabled BOOLEAN NOT NULL DEFAULT TRUE
);
 
-- Boolean columns are:
-- - Self-documenting
-- - Indexable individually
-- - Queryable without parsing
-- - Modifiable without string manipulation

Database Management SystemsFirst Normal Form (1NF)

First Normal Form (1NF): Foundation of Normalization

LevelBeginner

Duration60 mins

TopicFirst Normal Form (1NF)

4 / 5

1NF Violations

Recognizing Structural Anti-Patterns

Understanding 1NF requirements is one thing; recognizing violations in real-world schemas is another. Violations often hide in plain sight—in legacy systems that "work fine," in designs inherited from spreadsheet migrations, in schemas created by developers unfamiliar with normalization principles. This page serves as a comprehensive field guide to 1NF violations. We'll examine each type of violation in detail, understand why it violates 1NF, see the concrete problems it causes, and learn to spot the warning signs that indicate trouble. By the end, you'll be able to audit any schema for 1NF compliance with confidence.

What You Will Learn

By the end of this page, you will master a complete taxonomy of 1NF violations, understand the specific technical problems each violation type causes, recognize subtle violations that pass casual review, learn diagnostic queries to detect violations in existing databases, and understand contextual factors that determine violation severity.

The 1NF Requirements Revisited

Before examining violations, let's establish the complete set of 1NF requirements. A relation is in First Normal Form if and only if: Requirement 1: Atomic Values Every attribute contains only scalar (indivisible) values No attribute contains sets, lists, arrays, or composite structures Values are atomic with respect to the application's data manipulation needs Requirement 2: No Repeating Groups No column set is repeated horizontally (Item1, Item2, Item3...) No implicit arrays exist in the column structure Related multi-valued data resides in separate tables Requirement 3: Unique Row Identification Every row is uniquely identifiable by a primary key No duplicate rows exist in the relation Requirement 4: Column Homogeneity All values in a column are from the same domain No column contains mixed data types or meanings Requirement 5: Row and Column Order Independence The meaning of the data doesn't depend on row order The meaning of the data doesn't depend on column order

Beyond the Classic Definition

The classic 1NF definition focuses on atomicity and repeating groups. However, the full relational model includes additional structural requirements (unique rows, order independence) that are sometimes omitted from 1NF discussions. We include them here because violations of these requirements cause similar practical problems.

Violation Type 1: Multi-Valued Cells

The most recognizable 1NF violation: storing multiple values of the same type in a single cell, typically as delimited strings.

multi-valued-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- VIOLATION EXAMPLES
 
-- Comma-separated lists
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    Skills VARCHAR(500)  -- 'Java, Python, SQL, Docker'
);
 
-- Pipe-separated values
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Colors VARCHAR(200)  -- 'Red|Blue|Green|Yellow'
);
 
-- Semicolon-separated emails
CREATE TABLE Contacts (
    ContactID INT PRIMARY KEY,
    Name VARCHAR(100),
    EmailAddresses VARCHAR(1000)  -- 'work@co.com; personal@gmail.com; backup@mail.com'
);
 
-- Space-separated codes
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    PromoCodes VARCHAR(100)  -- 'SAVE10 FREESHIP BONUS20'
);

Problems Caused by Multi-Valued Cells

•Query Complexity — Finding employees with 'Python' skill requires WHERE Skills LIKE '%Python%', which incorrectly matches 'PythonScript', 'IronPython', etc.
•No Index Utilization — The LIKE '%value%' pattern forces full table scans; no index can optimize this query.
•Aggregation Impossible — Counting employees per skill is not possible with standard SQL; requires complex string parsing.
•Referential Integrity Impossible — Cannot enforce that skills reference a valid Skills master table.
•Update Anomalies — Removing 'Python' from all employees requires complex string manipulation, prone to errors.
•Inconsistent Formatting — 'Java,Python' vs 'Java, Python' vs 'Java , Python' all represent the same data differently.

Detection query: You can sometimes detect multi-valued columns by looking for delimiter characters:

detect-multi-valued.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Detect likely multi-valued columns by checking for delimiters
SELECT 
    'Skills column may be multi-valued' AS Warning,
    COUNT(*) AS RowsWithCommas
FROM Employees
WHERE Skills LIKE '%,%';
 
-- Check for multiple values by counting delimiters
SELECT 
    EmpID,
    Skills,
    (LENGTH(Skills) - LENGTH(REPLACE(Skills, ',', ''))) + 1 AS ValueCount
FROM Employees
WHERE Skills LIKE '%,%'
ORDER BY ValueCount DESC;

The Inconsistency Trap

Multi-valued cells inevitably drift into inconsistency. Some rows use commas, others semicolons. Some have spaces after delimiters, others don't. Some have trailing delimiters. Application code must handle all variations, leading to bugs and maintenance nightmares.

Violation Type 2: Repeating Column Groups

Repeating column groups embed arrays horizontally in the schema itself. This pattern is often called "spreadsheet-style" design.

repeating-column-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- VIOLATION EXAMPLES
 
-- Numbered columns
CREATE TABLE Surveys (
    SurveyID INT PRIMARY KEY,
    Respondent VARCHAR(100),
    Answer1 VARCHAR(500),
    Answer2 VARCHAR(500),
    Answer3 VARCHAR(500),
    Answer4 VARCHAR(500),
    Answer5 VARCHAR(500)
);
 
-- Date-based columns
CREATE TABLE Inventory (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Stock_Jan INT,
    Stock_Feb INT,
    Stock_Mar INT,
    Stock_Apr INT,
    -- ... 12 columns total
    Stock_Dec INT
);
 
-- Category-paired columns
CREATE TABLE Revenue (
    Year INT PRIMARY KEY,
    Electronics_Revenue DECIMAL(15,2),
    Electronics_Cost DECIMAL(15,2),
    Clothing_Revenue DECIMAL(15,2),
    Clothing_Cost DECIMAL(15,2),
    Food_Revenue DECIMAL(15,2),
    Food_Cost DECIMAL(15,2)
);
 
-- Contact type columns
CREATE TABLE Companies (
    CompanyID INT PRIMARY KEY,
    CompanyName VARCHAR(100),
    CEO_Name VARCHAR(100),
    CEO_Email VARCHAR(100),
    CFO_Name VARCHAR(100),
    CFO_Email VARCHAR(100),
    CTO_Name VARCHAR(100),
    CTO_Email VARCHAR(100)
);

Problems Caused by Repeating Column Groups

•Artificial Capacity Limits — Only 5 answers? Only 12 months? When requirements exceed schema limits, costly ALTER TABLE is needed.
•Sparse Data Waste — Surveys with 3 answers waste Answer4 and Answer5 columns. Multiplied by millions of rows = significant storage waste.
•Complex Queries — 'Find surveys mentioning keyword X' requires WHERE Answer1 LIKE '%X%' OR Answer2 LIKE '%X%' OR...
•Aggregate Nightmares — Total stock across all months requires Stock_Jan + Stock_Feb + ... + Stock_Dec.
•Schema Maintenance Burden — Adding a new product category requires DDL changes plus application updates.
•Reporting Inflexibility — Pivoting data for analysis is extremely difficult when it's already pivoted in the schema.

Detection approach: Repeating columns typically follow naming patterns you can detect programmatically:

detect-repeating-columns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Query information_schema for numbered column patterns
-- PostgreSQL / MySQL syntax
SELECT 
    table_name,
    column_name,
    ordinal_position
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND (
    column_name ~ '^[A-Za-z]+[0-9]+$'           -- Ends with numbers: Answer1, Answer2
    OR column_name ~ '^[A-Za-z]+_[0-9]+$'       -- Pattern: Col_1, Col_2
    OR column_name ~ '^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)_' -- Month prefixes
    OR column_name ~ '_(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)$' -- Month suffixes
  )
ORDER BY table_name, ordinal_position;
 
-- Check for sparse data in suspected repeating columns
SELECT 
    COUNT(*) AS TotalRows,
    COUNT(Answer1) AS Answer1Filled,
    COUNT(Answer2) AS Answer2Filled,
    COUNT(Answer3) AS Answer3Filled,
    COUNT(Answer4) AS Answer4Filled,
    COUNT(Answer5) AS Answer5Filled
FROM Surveys;
 
-- If Answer4 and Answer5 are mostly NULL, it's likely a repeating group violation

Violation Type 3: Embedded Complex Types

Modern databases support JSON, XML, and other complex types. While these have legitimate uses, embedding query-critical data in complex types violates the spirit of 1NF.

embedded-complex-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- VIOLATION EXAMPLES
 
-- JSON blob for structured business data
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderData JSON  -- Contains: items, shipping, payment, all in one blob
);
 
-- XML for what should be relational data
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    EmploymentHistory XML  -- <jobs><job><company>Acme</company><years>3</years></job>...</jobs>
);
 
-- Serialized objects
CREATE TABLE Sessions (
    SessionID VARCHAR(100) PRIMARY KEY,
    UserID INT,
    SessionState BLOB  -- Serialized application state object
);
 
-- Properties column for variable attributes
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    BasePrice DECIMAL(10,2),
    Properties JSON  -- '{"color":"red","size":"XL","weight":2.5}'
);

Acceptable JSON Usage

•Audit logs storing raw API responses
•Configuration blobs read/written as units
•External data preserved for compliance
•Truly schema-less user-defined data
•Temporary staging before normalization

1NF Violation Indicators

•JSON fields used in WHERE clauses
•JSON properties joined with other tables
•Aggregate functions on JSON array elements
•Application parses JSON for every read
•Updates modify individual JSON properties

The Performance Illusion

JSON columns can seem performant in development with small datasets. But JSON path queries don't use B-tree indexes effectively. As data grows, queries that extract JSON properties become exponentially slower. What worked at 10,000 rows fails catastrophically at 10 million.

Violation Type 4: Missing or Ineffective Primary Keys

A relation must have a primary key that uniquely identifies each row. Violations occur when tables lack primary keys entirely, or when the designated key doesn't actually provide uniqueness.

primary-key-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- VIOLATION EXAMPLES
 
-- No primary key at all
CREATE TABLE EventLogs (
    EventTime TIMESTAMP,
    EventType VARCHAR(50),
    EventData TEXT
    -- No PRIMARY KEY defined - duplicates possible!
);
 
-- Primary key that doesn't ensure uniqueness in practice
CREATE TABLE PageViews (
    ViewID INT PRIMARY KEY,  -- Auto-increment seems fine...
    UserID INT,
    PageURL VARCHAR(500),
    ViewTime TIMESTAMP
);
-- Problem: The same logical event (user X viewed page Y at time Z) 
-- can be inserted multiple times with different ViewIDs
 
-- Composite key that's incomplete
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID)
);
-- Problem: What if a student can enroll in the same course 
-- multiple times (different semesters)? Key doesn't capture this.
 
-- Key on unstable data
CREATE TABLE Customers (
    Email VARCHAR(100) PRIMARY KEY,  -- Users change emails!
    Name VARCHAR(100),
    Phone VARCHAR(20)
);

Problems Caused by Primary Key Issues

•Duplicate Data — Without uniqueness enforcement, identical rows accumulate, corrupting aggregations and reports.
•Update Ambiguity — UPDATE table SET x = y WHERE ... might affect unexpected rows if duplicates exist.
•Delete Uncertainty — Which row are you deleting when multiple identical rows exist?
•Foreign Key Failure — Cannot create foreign keys to tables without primary keys.
•ORM Conflicts — Object-Relational Mappers require unique identifiers to function correctly.
•Replication Issues — Some replication mechanisms require primary keys to track changes.

detect-pk-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Find tables without primary keys
-- PostgreSQL syntax
SELECT 
    t.table_schema,
    t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc 
    ON t.table_schema = tc.table_schema 
    AND t.table_name = tc.table_name 
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND tc.constraint_name IS NULL;
 
-- Find tables with potential duplicate issues
-- (Check if all rows are truly unique by all columns)
SELECT 'EventLogs may have duplicates' AS Warning
FROM (
    SELECT EventTime, EventType, EventData, COUNT(*) as cnt
    FROM EventLogs
    GROUP BY EventTime, EventType, EventData
    HAVING COUNT(*) > 1
) duplicates
LIMIT 1;

Violation Type 5: Mixed-Domain Columns

Each column should contain values from a single, well-defined domain. Violations occur when columns store semantically different types of data based on row context.

mixed-domain-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- VIOLATION EXAMPLES
 
-- Value column with different meanings
CREATE TABLE Settings (
    SettingID INT PRIMARY KEY,
    SettingName VARCHAR(100),
    SettingValue VARCHAR(500)  -- Sometimes a number, sometimes text, sometimes JSON...
);
-- Row 1: 'max_connections', '100' (number)
-- Row 2: 'welcome_message', 'Hello World' (text)
-- Row 3: 'feature_flags', '{"beta":true}' (JSON)
 
-- Polymorphic foreign key
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentText TEXT,
    EntityType VARCHAR(50),    -- 'Article', 'Product', 'User'
    EntityID INT               -- Foreign key to different tables based on EntityType!
);
-- No referential integrity possible!
 
-- Overloaded meaning based on type
CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    TransactionType VARCHAR(20),  -- 'SALE', 'REFUND', 'TRANSFER'
    Amount DECIMAL(10,2),
    Reference VARCHAR(100)  -- Order ID for SALE, Original transaction for REFUND, Account for TRANSFER
);
 
-- Multipurpose ID column
CREATE TABLE Activities (
    ActivityID INT PRIMARY KEY,
    ActivityType VARCHAR(50),
    RelatedID INT  -- ProductID, OrderID, or CustomerID depending on ActivityType
);

Problems Caused by Mixed-Domain Columns

•Type Confusion — Is '100' a string or a number? Application code must interpret based on context.
•No Type Safety — Cannot use CHECK constraints or proper data types when meaning varies.
•Broken Foreign Keys — Polymorphic FKs cannot have database-enforced referential integrity.
•Query Complexity — Every query needs CASE statements to handle different interpretations.
•Index Inefficiency — Indexes cover all values but queries filter by subset, wasting space.
•Documentation Burden — External documentation required to explain what values mean in what context.

Proper solutions for polymorphic relationships:

fix-polymorphic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- SOLUTION 1: Separate foreign key columns (sparse but explicit)
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentText TEXT,
    ArticleID INT REFERENCES Articles(ArticleID),
    ProductID INT REFERENCES Products(ProductID),
    UserProfileID INT REFERENCES Users(UserID),
    -- Constraint: exactly one should be non-NULL
    CONSTRAINT exactly_one_parent CHECK (
        (ArticleID IS NOT NULL)::INT + 
        (ProductID IS NOT NULL)::INT + 
        (UserProfileID IS NOT NULL)::INT = 1
    )
);
 
-- SOLUTION 2: Separate child tables (fully normalized)
CREATE TABLE ArticleComments (
    CommentID INT PRIMARY KEY,
    ArticleID INT NOT NULL REFERENCES Articles(ArticleID),
    CommentText TEXT
);
 
CREATE TABLE ProductComments (
    CommentID INT PRIMARY KEY,
    ProductID INT NOT NULL REFERENCES Products(ProductID),
    CommentText TEXT
);
 
-- SOLUTION 3: Abstract parent table (most flexible)
CREATE TABLE Commentable (
    CommentableID INT PRIMARY KEY,
    CommentableType VARCHAR(20) NOT NULL  -- For application use only
);
 
CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY REFERENCES Commentable(CommentableID),
    Title VARCHAR(200)
);
 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY REFERENCES Commentable(CommentableID),
    ProductName VARCHAR(100)
);
 
CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    CommentableID INT NOT NULL REFERENCES Commentable(CommentableID),
    CommentText TEXT
);

Violation Type 6: Encoded Information

Information encoding packs multiple facts into a single value using conventions that require parsing to extract meaning. This is a subtle form of non-atomicity.

encoded-info-violations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- VIOLATION EXAMPLES
 
-- Smart keys with embedded information
CREATE TABLE Products (
    -- ProductCode 'ELEC-2024-NYC-00142' encodes:
    -- Category (ELEC), Year (2024), Warehouse (NYC), Sequence (00142)
    ProductCode VARCHAR(25) PRIMARY KEY,
    Description VARCHAR(200),
    Price DECIMAL(10,2)
);
-- Queries like "all electronics" require: WHERE ProductCode LIKE 'ELEC-%'
 
-- Status flags as strings
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    StatusFlags VARCHAR(20)  -- 'AVN' = Active, Verified, Notifications-on
);
-- Must parse each character position for meaning
 
-- Date-encoded identifiers
CREATE TABLE Invoices (
    InvoiceNumber VARCHAR(20) PRIMARY KEY,  -- '2024031501234'
    -- Encodes: YYYYMMDD + sequence = year, month, day, invoice number
    CustomerID INT,
    Amount DECIMAL(10,2)
);
 
-- Positional encoding
CREATE TABLE Accounts (
    AccountNumber VARCHAR(20) PRIMARY KEY,  
    -- Position 1-3: Branch code
    -- Position 4: Account type (S=Savings, C=Checking)
    -- Position 5-12: Customer number
    -- Position 13: Check digit
    Balance DECIMAL(15,2)
);

Problems Caused by Encoded Information

•Parsing Overhead — Every query, report, and application must know the encoding scheme and parse accordingly.
•No Indexing on Components — Cannot index 'category' component of a smart key efficiently.
•Fragile Queries — SUBSTRING(ProductCode, 1, 4) breaks if format changes.
•Validation Complexity — Must validate each component position separately.
•Documentation Dependency — Meaning is not self-describing; requires external documentation.
•Evolution Difficulty — Adding new information to the encoding scheme may require changing all existing values.

fix-encoded.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- CORRECTED DESIGN: Explicit atomic columns
 
CREATE TABLE Products (
                    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    Category VARCHAR(20) NOT NULL,
    ProductionYear INT NOT NULL,
    WarehouseCode VARCHAR(10) NOT NULL,
    Description VARCHAR(200),
    Price DECIMAL(10,2),
    -- Unique business identifier can still exist
    LegacyCode VARCHAR(25) UNIQUE  -- For backward compatibility
);
 
-- Now you can:
-- - Index and query by Category directly
-- - Filter by ProductionYear with range queries
-- - JOIN with Warehouse table
-- - Add new attributes without encoding changes
 
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    IsActive BOOLEAN NOT NULL DEFAULT TRUE,
    IsVerified BOOLEAN NOT NULL DEFAULT FALSE,
    NotificationsEnabled BOOLEAN NOT NULL DEFAULT TRUE
);
 
-- Boolean columns are:
-- - Self-documenting
-- - Indexable individually
-- - Queryable without parsing
-- - Modifiable without string manipulation

1NF Violations

Recognizing Structural Anti-Patterns

The 1NF Requirements Revisited

Violation Type 1: Multi-Valued Cells

Violation Type 2: Repeating Column Groups

Violation Type 3: Embedded Complex Types

Violation Type 4: Missing or Ineffective Primary Keys

Violation Type 5: Mixed-Domain Columns

Violation Type 6: Encoded Information

There’s more to understand.

1NF Violations

Recognizing Structural Anti-Patterns

The 1NF Requirements Revisited

Violation Type 1: Multi-Valued Cells

Violation Type 2: Repeating Column Groups

Violation Type 3: Embedded Complex Types

Violation Type 4: Missing or Ineffective Primary Keys

Violation Type 5: Mixed-Domain Columns

Violation Type 6: Encoded Information

There’s more to understand.