database design how to relate a keywords table to a product table

Posted on 2009-12-17
Last Modified: 2012-06-27
I am creating a sql server database with

I have a products table and have been tasked to create a "keywords" table that holds multiple words for a product such as

Household, cleaning, kitchen

What is the best database design for this
Question by:mugsey
    LVL 7

    Accepted Solution

    Following design will help you.

    tblProducts:  ProdID PK INT,
                            ProductName VARCHAR

    tblProductKeyword:  ProdKeyID PK INT,
                                         ProdID FK refer tblProducts INT,
                                         Keywords VARCHAR,
    LVL 12

    Assisted Solution

    If you will write all words to one line separated by comma, you could also add a column to you products table.

    If you want every keyword only once in the keyword table but it can be assigned to multiple products you need to introduce an additional mapping table products_keywords.

    If it should only be 1 keyword per line, but every keyword my occur multiple times in the keywords table the you should only make a column to the keywords-table that contains the product-id.
    LVL 10

    Assisted Solution

    I would do it like this if yo uwant the most flexible result:

    Products table: ID, Name, (+ other product related properties)
    Keywords table: ID, Name
    ProductKeywords table: ProductID, KeywordID

    Make sure to define the foreign keys on the ProductKeywords table to ensure that no data gets into the table for productIDs or KeywordIDs that do not exist.

    This design allows to use the same keyword for multiple products and have multiple keywords for each product.
    LVL 13

    Assisted Solution


    You can create product_table with columns to hold other product data. You can create another table product_keywords containing product_id, keyword_id as the columns and unique ky. Define third table keyword_details as keyword_id, keyword_description with keyword_id as the unique key. PS: You can combine second and third table depending on the application needs, frequency of updation, etc.


    Author Comment

    OK thanks folks - I am guessing a mapping table would be the best bet.  Thanks

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now