Best way to design a table in which a cell has mutiple references?

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 (special开发者_如何学Goly 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.

You must flip dependencies: Table KEYWORD should reference back to post. You may want someting in between to find all posts for a given keyword (pseudo code):

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: As always, for documentation let me point you to Wikipedia. You should also have a look at normalization (in my opinion the most important concept when it comes to database design).

You need a many-many table in the "middle" with foreign keys

Posts:

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

Keywords:

kw_id (PK)
Keyword (UQ)

PostsKeywords

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

Why have different conventions for your "ID" columns though? Personally I'd use PostID and KeywordID throughout.

Edit: database design link

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

that table will contain at minimum post_Id and kw_id and both are foreign keys to their own tables.

personally in these cases I also create a local PK column that is not involved in the many to many, for example a PostKeywords_ID which is an auto increment local to that table only.

Instead of putting a keyword column in your post table, you'll be wanting a separate table post_keyword with two columns: post_id and keyword_id. The presence of a row in this table indicates that a particular post has been assigned a particular keyword. The primary key of this table is (post_id, keyword_id). Both columns are foreign keys to their master tables.

This is standard design practice for many-to-many relationships.

If your keyword items are to be presented in a particular order, add an order column.

For best results, by the way, name the id column post_id in your post table and all the other tables in which it appears. That way various schema-engineering tools will be able to figure out what you're doing.