javascript - Excuse me, if an article has multiple tags and there are multiple articles under one tag, how can I design a table using mogoose?

Question

The usage scenario is like this:

When publishing an article, it is accompanied by tags. There are many tags in an article,

Then when querying a tag, how to query all the articles of this tag? ?

Then what is the entire mongoodb data design idea? Please give me some answers, thx

Answer 1

The article contains _id, title, tags, content
Then, tags contains many tags

• _id

• title

• tags

  • tag1

  • tag2

  • tag3

• content

You can design it like this
You can write a method to return all articles under a specific tag

Post.getTag=function(tag,callback){
    mongodb.open(function(err,db){
        if(err){
            return callback(err);
        }
        db.collection('posts',function(err,collection){
            if(err){
                mongodb.close();
                return callback(err);
            }
            collection.find({
                "tags":tag
            },{
                "name":1,
                "time":1,
                "title":1
            }).sort({
                time:-1
            }).toArray(function(err,docs){
                mongodb.close();
                if(err){
                    return callback(err);
                }
                callback(null,docs);
            });
        })
    });
};

Then call it in the corresponding route

Answer 2

When saving the article, add the corresponding tag id to the tags array (foreign key), and at the same time add the article id (foreign key) to all corresponding tags

Answer 3

This is a typical many-to-many model, the table design is as follows

Article table article

CREATE TABLE `article` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章ID',
    `title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
    `content` longtext COMMENT '文章内容',
    ...,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='文章表';

tag tabletags

CREATE TABLE `tags` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '标签ID',
    `name` varchar(30) NOT NULL DEFAULT '' COMMENT '标签名',
    PRIMARY KEY (`id`),
) ENGINE=InnoDB COMMENT='标签表';

Article and tag association table article_tag

CREATE TABLE `article_tag` (
    `article_id` int(11) unsigned DEFAULT '0' COMMENT '文章ID',
    `tag_id` int(11) unsigned DEFAULT '0' COMMENT '标签ID',
    KEY `FK_article_tag_tag_id` (`tag_id`),
    KEY `FK_article_tag_article_id` (`article_id`),
    CONSTRAINT `FK_article_tag_article_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_article_tag_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='文章标签表';

Specific ideas

• Associate the article table and tags table through the foreign key constraint of article_tag

• For article tag operations, if the tag already exists, you only need to add or delete the article_tag table data

• If the tag does not exist, add the tag and article first, and then add Article ID<->Tag IDdata to article_tag

• If you want to query all articles in a tag, use the article_tag table to left-join (or inline) the article table

• If you want to query all the tags of an article, use the article_tag table to left-join (or inline) the tags table

• In the above two cases, if you still need tag or article data, just continue to left-link the tag table or article table.

Answer 4

I agree with the solution above. One table for articles, one table for tags, and then create a corresponding table of article tags