mysql - 如何创建一对多关系，以便一篇博客文章可以有多个类别

Question

我正在构建一个类似 WordPress 的 CMS。我希望能够为博客文章分配多个类别。

我的表格如下所示:

Article:
-id
-title
-author
-user_id
-date
-resource link
-content
-status
-featured_image

Categories:
-id
-name

我知道如何设计它，以便一篇博客文章可以有一个类别，但不能有多个类别。

Answer 1

你可以这样设计你的数据库。此架构允许您分配多个类别并添加多个可以单独跟踪的图像。

CREATE TABLE BlogPost
(
    blogPostNr INT NOT NULL,
    author CHAR(40) NOT NULL,
    categoryNr INT NOT NULL,
    `date` DATETIME NOT NULL,
    status CHAR(5) NOT NULL,
    text VARCHAR(2000) NOT NULL,
    title CHAR(100) NOT NULL,
    userId SMALLINT NOT NULL,
    CONSTRAINT BlogPost_PK PRIMARY KEY(blogPostNr)
);

CREATE TABLE Image
(
    imageNr INT NOT NULL,
    description CHAR(200) NOT NULL,
    picture LONGBLOB NOT NULL,
    source CHAR(60) NOT NULL,
    CONSTRAINT Image_PK PRIMARY KEY(imageNr)
);

CREATE TABLE BlogPostHasImage
(
    blogPostNr INT NOT NULL,
    imageNr INT NOT NULL,
    CONSTRAINT BlogPostHasImage_PK PRIMARY KEY(blogPostNr, imageNr)
);

CREATE TABLE Category
(
    categoryNr INT NOT NULL,
    categoryName CHAR(20) NOT NULL,
    CONSTRAINT Category_PK PRIMARY KEY(categoryNr)
);

ALTER TABLE BlogPost ADD CONSTRAINT BlogPost_FK FOREIGN KEY (categoryNr) REFERENCES Category (categoryNr) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE BlogPostHasImage ADD CONSTRAINT BlogPostHasImage_FK1 FOREIGN KEY (blogPostNr) REFERENCES BlogPost (blogPostNr) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE BlogPostHasImage ADD CONSTRAINT BlogPostHasImage_FK2 FOREIGN KEY (imageNr) REFERENCES Image (imageNr) ON DELETE RESTRICT ON UPDATE RESTRICT;