I believe the main needs will be:

add new tag to existing article (entry);
retrieve the list of articles matching a tag (virtual category);
- Requires an index on the tag
show all tags for an article;
- Requires an index on the article or article reference

There are several approaches for the SQL model, with different advantages/drawbacks.

Here is the variant 1, in which tags are referenced by an integer. It offers the easy possibility of changing the name of the tag, and using a long tag name should not affect performances. Articles (entries) can have several tags.

CREATE TABLE IF NOT EXISTS `tag` (
    `id` SMALLINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS `tags` (
    `id_tag` SMALLINT NOT NULL,
    `id_entry` BIGINT NOT NULL,
    FOREIGN KEY (`id_tag`) REFERENCES `tag`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`id_entry`) REFERENCES `entry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX (`id_entry`),
    UNIQUE KEY (`id_tag`,`id_entry`)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
ENGINE = INNODB;

Uh oh!

Add custom tags to articles, and show custom tags as virtual categories #928

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions