Question

My question regards how to design a database.

I have one table, called posts, with columns:

ID, subject, keywords, (and a few other columns)

and another table called keywords with:

kw_id, keyword.

Now, each "post" has several keywords and, sometimes, keywords are deleted because they don t make sense or are duplicates.

My question is:

Can keywords column in table posts be a foreign key? (each row will have multiple keywords)
If I can t, what is the best way to ensure data integrity (specially when a keyword is deleted)?

thanks in advance

EDIT: Can you point me any books or documents I should read about database design? It seems I m laking key knowledge about database design.

Answer 1

你们必须lip: 表KEYWORD应参照员额。您可能希望找到特定关键词(假冒代码)的所有职位。

POST
    POST_ID
    ...

KEYWORD
    KEYWORD_ID
    NAME        /* the keyword */

POSTKEYWORDREL    /* relationship */
    POST_ID     /* foreign key to POST */
    KEYWORD_ID  /* foreign key to KEYWORD */

Now you can easily delete a keyword from a given post by just removing the relationship in POSTKEYWORDREL.

EDIT:就文件而言,请允许我指出:Wikipedia 。 http://en.wikipedia.org/wiki/Database_normalization”rel=“nofollow” (我认为,数据库设计方面最重要的概念)。

Answer 2

你们需要在“中程”与外国钥匙的“中程”中占有一席之地。

员额:

ID (PK)
Subject
(and a few other columns)

关键词:

kw_id (PK)
Keyword (UQ)

关键词

PostID (PK, FK to Posts.ID)
kw_id (PK, FK to Keywords.kw_id)

为什么对你的“ID”栏有不同的公约? 个人使用邮政信息数据库和关键词信息数据库。

Edit: 数据库设计链接。

Answer 3

to follow your design, in the posts table you remove the column keywords.

you create then another table to make the many-to-many relationship, something like

PostKeywords

表2 Id和kw_id,两者都是本国表格的外在钥匙。

本人我也创建了一个地方PK一栏,它没有参与到许多方面,例如,一个后关键词。仅此表的地方自动加油。

Answer 4

页: 1 本表有一行显示,已指定一个特定的<>序号>。本表的主要要点是post_id ,keyword_id。这两栏均为主次的外国钥匙。

这是许多至男性关系的标准设计做法。

如果您的keyword items are to be presented in a particular order, 增加order栏。

如欲取得最佳结果,请在<>后表格和显示的所有其他表格中点名。这样,各种机械化工具就能够显示你重新做些什么。

友情链接