From harness-claude
Applies First Normal Form (1NF) to SQL tables: atomic values per column, no repeating groups, primary keys. Use for schema design, audits, refactoring delimited data, migrations from spreadsheets.
npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeThis skill uses the workspace's default tool permissions.
> Every column holds a single atomic value, no repeating groups exist, and every row is uniquely identifiable by a primary key.
Identifies partial dependencies in tables with composite primary keys to enforce 2NF. Guides decomposition of order items or enrollment tables to eliminate update anomalies and redundancy.
Designs normalized database schemas with relationships and constraints for PostgreSQL and MySQL. Use for new schemas, table design, data modeling, and normalization analysis.
Designs PostgreSQL/MySQL schemas with normalization (1NF-3NF), relationships, constraints, audit columns. For new databases, reviews, migrations, or fixing missing PKs/FKs, wrong types, denormalization, EAV.
Share bugs, ideas, or general feedback.
Every column holds a single atomic value, no repeating groups exist, and every row is uniquely identifiable by a primary key.
First Normal Form defines the baseline for a well-structured relational table. A table is in 1NF when it satisfies three rules.
Each cell must contain a single, indivisible value. No lists, no comma-separated strings, no embedded structures.
BAD -- tags crammed into one column:
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT -- 'postgresql,normalization,database'
);
Querying "find all articles tagged postgresql" now requires LIKE '%postgresql%' or string splitting -- neither is indexable or correct (matches "not-postgresql" too).
GOOD -- junction table for multi-valued relationship:
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE article_tags (
article_id INT REFERENCES articles(id),
tag_id INT REFERENCES tags(id),
PRIMARY KEY (article_id, tag_id)
);
Now SELECT a.title FROM articles a JOIN article_tags at ON a.id = at.article_id JOIN tags t ON at.tag_id = t.id WHERE t.name = 'postgresql'; is indexable, correct, and constraint-enforced.
Do not model a variable-length list as numbered columns.
BAD -- phone1/phone2/phone3 pattern:
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
phone1 TEXT,
phone2 TEXT,
phone3 TEXT
);
This caps phones at three, wastes space when contacts have one, and makes queries painful (WHERE phone1 = ? OR phone2 = ? OR phone3 = ?).
GOOD -- separate rows:
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE contact_phones (
id SERIAL PRIMARY KEY,
contact_id INT REFERENCES contacts(id),
phone TEXT NOT NULL,
label TEXT -- 'mobile', 'work', 'home'
);
Every row must be uniquely identifiable. Without a primary key, the table is a bag of rows -- duplicates are undetectable and foreign keys cannot reference it.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);
A CSV export contains: order_id, customer, items("Widget,Gadget,Bolt"), quantities("2,1,5").
Step 1 -- create atomic tables:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL
);
CREATE TABLE order_lines (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id),
item_name TEXT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0)
);
Step 2 -- load one row per item instead of one row per order.
tags jsonb DEFAULT '[]' avoids the junction table but makes constraint enforcement, joins, and indexing harder. Reserve JSONB for genuinely schemaless data.metadata TEXT column containing key-value pairs is a schema within a schema. Define real columns or use a typed JSONB column with a CHECK constraint.PostgreSQL arrays (TEXT[], INT[]) technically violate 1NF but are acceptable for specific use cases:
tsvector)WHERE tags @> ARRAY['postgresql'])When you find yourself using unnest() to join array elements with another table, that is the signal to normalize into a junction table instead.
MySQL lacks native array types. 1NF violations in MySQL typically manifest as comma-separated strings parsed with FIND_IN_SET(). This is always wrong in production -- it defeats indexing, prevents constraint enforcement, and returns incorrect results for substring matches.
COUNT, SUM, and GROUP BY operate on rows. If one row contains multiple values packed into a string, aggregations are wrong without preprocessing.PostgreSQL arrays with GIN indexes support efficient containment queries:
CREATE INDEX idx_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
This is valid when: (1) you never need to join tag values to another table, (2) you never need referential integrity on tag values, and (3) the array is small and treated as an opaque unit. When any of these conditions fails, normalize.
A SaaS logging platform stored log labels as labels TEXT containing "env=prod,service=api,region=us-east". Queries used LIKE '%service=api%'. After normalizing into a log_labels(log_id, key, value) table with a composite index on (key, value), query latency dropped from 1200ms to 8ms for label-filtered searches. Storage increased 15% but query performance improved 150x.