Normalization

Tags that a search can actually trust

Difficulty 3/5Free

A blog tags its posts. A post has any number of tags, and tags are shared across posts.

What the product needs:

Find every post carrying a given tag, and exactly that tag. Searching for sql must not return a post tagged nosql or sql-server.
Count how many posts use a tag, for the tag cloud.
The same tag cannot be attached to the same post twice. It is either on or off.
A tag can be renamed, and every post carrying it follows automatically. Marketing does this and does not warn anyone.
Tag names are unique: there is one sql, not one per post.

The tests read through two relations you provide: tags and a way to connect posts to them. posts already exists.

The one thing that is fixed

3 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

posts
idtitle
tags
idname
post_tags
post_idtag_id

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE posts (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL
);

INSERT INTO posts (title) VALUES ('Indexes explained'), ('Why we left Mongo'), ('Cooking');

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

-- posts already exists. Design the tags, and the connection between them.

CREATE TABLE tags (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

8 tests

····Searching for "sql" returns one post, not three
····The tag cloud counts each tag correctly
····A post can gain another tag
····The same tag cannot be attached twice
····Two tags cannot share a name
····A tagging cannot point at a tag that does not exist
····Marketing renames sql to postgres
····Both posts carrying it followed the rename