Tag clouds are less fashionable than they once were, but the underlying database problem remains useful: posts and tags form a many-to-many relationship, and the interface needs aggregate statistics for every tag. The same model also supports labels, topics, product facets, and other classification systems.
A reliable design stores each relationship once and derives usage counts from those rows. Cached counters can be added later when measurements justify them, but they should not be the initial source of truth.
Model Tags and Assignments
Assume that posts already live in a table named post with an unsigned BIGINT primary key. Tags need a display label and a canonical slug:
CREATE TABLE tag (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
label VARCHAR(100) NOT NULL,
slug VARCHAR(191)
CHARACTER SET ascii
COLLATE ascii_bin
NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_tag_slug (slug)
) ENGINE = InnoDB;
CREATE TABLE post_tag (
post_id BIGINT UNSIGNED NOT NULL,
tag_id BIGINT UNSIGNED NOT NULL,
attached_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (post_id, tag_id),
KEY idx_post_tag_tag (tag_id, attached_at, post_id),
CONSTRAINT fk_post_tag_post
FOREIGN KEY (post_id) REFERENCES post (id)
ON DELETE CASCADE,
CONSTRAINT fk_post_tag_tag
FOREIGN KEY (tag_id) REFERENCES tag (id)
ON DELETE CASCADE
) ENGINE = InnoDB; The primary key of post_tag prevents the same tag from being attached to one post twice. Its reverse index starts with tag_id, which supports counting posts and finding the most recent assignment for each tag.
The slug itself is unique, so a CRC32 helper column is unnecessary. A hash is not an identity constraint: collisions are possible, and comparing the original slug is still required. The direct unique index already provides the lookup MySQL needs.
Choose One Canonical Slug
Slug generation belongs at the application boundary because transliteration and punctuation rules are product decisions. Normalize the label before opening the database transaction, then submit both values as bound parameters. For example, the label Open Source might become open-source.
The binary ASCII collation makes the stored slug comparison explicit and stable. If two labels normalize to the same slug, they refer to the same tag. Changing the slug policy later requires a deliberate migration rather than silently creating another identity.
Attach a Tag Without Races
A unique-key upsert handles concurrent attempts to create the same tag. The LAST_INSERT_ID(id) assignment returns the existing identifier on the duplicate path and leaves the row unchanged:
START TRANSACTION;
INSERT INTO tag (label, slug)
VALUES (?, ?) AS incoming
ON DUPLICATE KEY UPDATE
id = LAST_INSERT_ID(id);
SET @tag_id = LAST_INSERT_ID();
INSERT INTO post_tag (post_id, tag_id)
VALUES (?, @tag_id) AS incoming
ON DUPLICATE KEY UPDATE
tag_id = incoming.tag_id;
COMMIT; All statements must run on the same database connection because LAST_INSERT_ID() is connection-local. The second upsert is intentionally a no-op when the assignment already exists. Other constraint failures, including an unknown post, still raise an error instead of being hidden by INSERT IGNORE. The application must catch such an error and roll back the open transaction.
The existing display label is preserved when a slug already exists. If labels may be renamed, make that a separate authorized operation; the capitalization in the latest tagging request should not silently rename a shared tag.
Read the Tags of One Post
The normalized relationship makes a post's tags a simple indexed join:
SELECT t.id, t.label, t.slug
FROM post_tag AS pt
JOIN tag AS t ON t.id = pt.tag_id
WHERE pt.post_id = ?
ORDER BY t.label, t.id; Removing an assignment requires only the corresponding relationship row:
DELETE FROM post_tag
WHERE post_id = ?
AND tag_id = ?; No counter needs to be decremented. Unused tags may remain available for reuse, or a periodic maintenance task can delete tags that have no assignments.
Calculate the Cloud from Current Data
A tag cloud usually maps usage frequency to font size and recent activity to visual emphasis. First aggregate the authoritative relationship rows. Window functions then provide the smallest and largest counts without a separate round trip:
SET @max_age_days = 30;
WITH
tag_stats AS (
SELECT
t.id,
t.label,
t.slug,
COUNT(*) AS usage_count,
MAX(pt.attached_at) AS last_used_at
FROM tag AS t
JOIN post_tag AS pt ON pt.tag_id = t.id
GROUP BY t.id, t.label, t.slug
),
ranges AS (
SELECT
tag_stats.*,
MIN(usage_count) OVER () AS min_usage,
MAX(usage_count) OVER () AS max_usage
FROM tag_stats
)
SELECT
id,
label,
slug,
usage_count,
last_used_at,
CASE
WHEN min_usage = max_usage THEN 0.5
ELSE (
LN(usage_count) - LN(min_usage)
) / NULLIF(
LN(max_usage) - LN(min_usage),
0
)
END AS size_weight,
LEAST(
1.0,
GREATEST(
0.0,
1.0 - TIMESTAMPDIFF(
SECOND,
last_used_at,
CURRENT_TIMESTAMP
) / (@max_age_days * 86400.0)
)
) AS recency_weight
FROM ranges
ORDER BY label, id; Both weights lie between zero and one. The logarithmic frequency scale prevents a few very common tags from visually overwhelming the rest. When all tags have the same count, the formula returns the neutral midpoint 0.5 instead of dividing by zero.
Map Weights to Presentation
Let $w_i$ be the frequency weight of tag $i$. A user interface can map it into any font-size interval:
\[ s_i = s_{\min} + w_i\left(s_{\max} - s_{\min}\right). \]
The query calculates the logarithmic weight as
\[ w_i = \frac{\ln c_i - \ln c_{\min}}{\ln c_{\max} - \ln c_{\min}}, \]
where $c_i$ is the number of posts assigned to tag $i$. The special equal-count case uses $w_i = 0.5$. The same interpolation can map recency_weight to a small set of accessible text-color classes. Keep the database responsible for data and normalized weights; keep CSS units, colors, and contrast decisions in the presentation layer.
When to Cache Counts
Computing counts directly is the safest starting point. The query scans the post_tag index rather than every post, and for many sites that is sufficient. A stored usage_count becomes worthwhile only when measurements show that aggregation is a bottleneck.
If counts are cached, treat them as derived data: update assignments and counters in the same transaction, provide a reconciliation query, and monitor for drift. A procedure that increments a counter before attempting a duplicate relationship insert can overcount. Deletes and failed transactions create similar failure modes when every mutation path does not maintain the cache.
For a frequently requested global cloud, a periodically refreshed summary table is often easier to reason about than counters embedded in the tag identity table. The normalized schema remains the recoverable source of truth.
References
- MySQL Oracle, MySQL 8.4 Reference Manual: INSERT ... ON DUPLICATE KEY UPDATE
- MySQL Oracle, MySQL 8.4 Reference Manual: Window Functions