A Simple Guide to Five Normal Forms in Relational Database Theory (1982)

The people who did all the pioneering work on the subject (mostly E.F. Codd and Chris Date) seem to have made several blunders in trying to popularize their ideas. For example, one of Date's later books ("Go Faster: The Transrelational(tm) Approach to DBMS Implementation") contains some very useful ideas. But it was written in 2001 and not published until 2011, because he was working with a now-failed startup that was trying to keep it all a trade secret.

Most of their writing is not available online. You have to buy their books. Which is an author's prerogative, but seriously limits the reach of the ideas.

The world thinks it already has relational databases that are good enough. Convincing it otherwise requires a web-savvy marketing approach that has so far been lacking.

Relational databases and sql are an exceptionally sound approach to a lot of data storage and retrieval problems. Some of the "nosql" technologies emerged from new problems that emerged (such as full text indexing, networks).

Progress is an interesting thing, and doesn't always happen on an even keel. These days, I think you're far less likely to encounter knee-jerk resistance to an rdbms or sql. The dust is settling a bit, and in this case, that's a good thing.

e.g. employee, department, location is not good because that schema can represent invalid data: a single department can't be in 2 locations but the schema can represent that.

The same rule of thumb applies to types in programming: a data type should be able to represent only valid values. This is why sum types are superior to error codes: a function can return a result AND an error code, when what we usually want is that the function returns a result OR an error code. When invalid values are unrepresentable that makes a lot of errors impossible. Though sometimes your type system can't deal with all the constraints that you have, e.g. an array should be sorted (an unsorted array is representable but not valid => bad).

What are the definitions of "variable repeating fields" and "groups?"

What would violating records look like?

I.E. Things categorized by tags or groups where 1-to-n relationships are necessary. Of course, the extreme of this is EAV (Entity Attribute Value) which I would limit to meta/taxonomy data for performance reasons.

But for information where the labels (or quantity) aren't known before input are easier to deal with in normalized databases.

E.G. Getting metadata on a specific entry with no prior knowlege of said data except parent id:

  SELECT id, label, content FROM meta WHERE id IN (
    SELECT meta_id FROM posts_meta WHERE post_id = :id
  );

The following is an excerpt from the actual schema I used on a very simple forum that ran on SQLite for years before switching to Postgres fairly recently. The normalization (varying forms) afforded a lot of flexibility. Maybe someone will find it useful.

  CREATE TABLE posts (
  	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  	updated_at DATETIME DEFAULT NULL, 
  	title VARCHAR NULL,
  	summary TEXT NOT NULL, 
  	body TEXT NOT NULL, 
  	plain TEXT NOT NULL, 
  	quality FLOAT NOT NULL DEFAULT 0, 
  	status INTEGER NOT NULL DEFAULT 0, 
  	reply_count INTEGER NOT NULL DEFAULT 0, 
  	auth_key VARCHAR NOT NULL 
  );
  
  
  CREATE INDEX idx_posts_on_status ON posts ( status );
  CREATE INDEX idx_posts_on_created_at ON posts ( created_at );
  
  CREATE VIRTUAL TABLE posts_search USING fts4 ( search_data );
  
  CREATE TABLE posts_family (
  	parent_id INTEGER NOT NULL, 
  	child_id INTEGER NOT NULL, 
  	PRIMARY KEY ( child_id, parent_id )
  );
  
  CREATE TABLE taxonomy (
  	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  	label VARCHAR NOT NULL, 
  	term VARCHAR NOT NULL, 
  	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  	updated_at DATETIME DEFAULT NULL, 
  	status INTEGER NOT NULL DEFAULT 0
  );
  
  
  CREATE TABLE posts_taxonomy (
  	post_id INTEGER NOT NULL, 
  	taxonomy_id INTEGER NOT NULL, 
  	PRIMARY KEY ( post_id, taxonomy_id ) 
  );
  
  CREATE TABLE taxonomy_family (
  	parent_id INTEGER NOT NULL, 
  	child_id INTEGER NOT NULL, 
  	PRIMARY KEY ( child_id, parent_id )
  );
  
  CREATE TABLE meta (
  	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  	label VARCHAR NOT NULL, 
  	parse_as VARCHAR NOT NULL DEFAULT "text", 
  	content TEXT NOT NULL
  );
  
  CREATE TABLE posts_meta (
  	post_id INTEGER NOT NULL, 
  	meta_id INTEGER NOT NULL, 
  	PRIMARY KEY ( post_id, meta_id ) 
  );
  
  CREATE UNIQUE INDEX idx_taxonomy_on_terms ON taxonomy ( label ASC, term ASC );
  CREATE INDEX idx_taxonomy_on_status ON taxonomy ( status );
  
  
  -- Triggers
  
  -- Post create procedures
  CREATE TRIGGER post_after_insert AFTER INSERT ON posts FOR EACH ROW 
  BEGIN
  	INSERT INTO posts_search ( docid, search_data ) 
  		VALUES ( NEW.rowid, NEW.plain );
  	UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.rowid;
  END;
  
  -- Post update procedures
  CREATE TRIGGER post_before_update BEFORE UPDATE ON posts FOR EACH ROW 
  BEGIN
  	DELETE FROM posts_search WHERE docid = OLD.rowid;
  END;
  
  CREATE TRIGGER post_after_update AFTER UPDATE ON posts FOR EACH ROW 
  BEGIN
  	INSERT INTO posts_search ( docid, search_data ) 
  		VALUES ( NEW.rowid, NEW.plain );
  	UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.rowid;
  END;
  
  -- Post deletion procedure
  CREATE TRIGGER post_before_delete BEFORE DELETE ON posts FOR EACH ROW 
  BEGIN
  	UPDATE posts SET reply_count = ( reply_count - 1 ) 
  		WHERE id != OLD.rowid AND id IN (
  			SELECT parent_id FROM posts_family WHERE child_id = OLD.rowid 
  		);
  	DELETE FROM posts_family WHERE parent_id = OLD.rowid OR child_id = OLD.rowid;
  	DELETE FROM posts_search WHERE docid = OLD.rowid;
  	DELETE FROM posts_taxonomy WHERE post_id = OLD.rowid;
  	
  	DELETE FROM meta WHERE id IN (
  		SELECT meta_id FROM posts_meta WHERE post_id = OLD.rowid
  	);
  	DELETE FROM posts_meta WHERE post_id = OLD.rowid;
  END;
  
  -- Post parent insert procedures
  CREATE TRIGGER posts_family_after_insert AFTER INSERT ON posts_family FOR EACH ROW 
  BEGIN
  	UPDATE posts SET reply_count = ( reply_count + 1 ) WHERE id IN (
  		SELECT parent_id FROM posts_family WHERE child_id = NEW.rowid
  	);
  END;
  
  
  -- Taxonomy procedures
  CREATE TRIGGER taxonomy_after_insert AFTER INSERT ON taxonomy FOR EACH ROW 
  BEGIN
  	UPDATE taxonomy SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.rowid;
  END;
  
  CREATE TRIGGER taxonomy_after_update AFTER UPDATE ON taxonomy FOR EACH ROW 
  BEGIN
  	UPDATE taxonomy SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.rowid;
  END;
  
  CREATE TRIGGER taxonomy_before_delete BEFORE DELETE ON taxonomy FOR EACH ROW 
  BEGIN
  	DELETE FROM posts_taxonomy WHERE taxonomy_id = OLD.rowid;
  	DELETE FROM taxonomy_family WHERE parent_id = OLD.rowid OR child_id = OLD.rowid;
  END;

Please do not allow your data model designs to be artificially constrained by the requirements of the past.