[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

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

I am creating a sql server database with asp.net

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
4 Solutions
Following design will help you.

tblProducts:  ProdID PK INT,
                        ProductName VARCHAR

tblProductKeyword:  ProdKeyID PK INT,
                                     ProdID FK refer tblProducts INT,
                                     Keywords VARCHAR,
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.
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.

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.

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now