Tags that a search can actually trust

Difficulty 3/5Subscribers

New to this? Learn it first: Relationships: one, many, and many to many New to this? Learn it first: Level 2 boss: the marketplace New to this? Learn it first: First normal form: one value per cell

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 contract

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');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…