CODEWITHSUNDEEP
mysqldatabase-design
eevaa
eevaa

Reputation: 1457

MySQL database structure (assign multiple values in one column)?

I have a table that contains a list of words. Each of the words has multiple categories assigned to them. What is the best way to do that?

I do not want to use a set, because the list of categories keeps changing.

I was thinking of having another table that assigns IDs to the categories and then add a field with the list of categories to the table containing the words. Is storing a list of IDs in a text field the "proper" way to do this?

Upvotes: 0

Views: 987

Answers (3)

Biswajit Maji
Biswajit Maji

Reputation: 889

You should have three table for this:

  1. word_table (you already have )- word_id, word, ...
  2. categories_table - cat_id, cat_name, ...
  3. word_cat_table - cat_id, word_id

here word_id in word_table and cat_id in categories_table is primary key

Upvotes: 2

kishore krv
kishore krv

Reputation: 70

I agree with Biswajit on creating three tables.

word_table (you already have )- word_id, word,... categories_table - cat_id, cat_name, ... word_cat_table - cat_id, word_id

The relationship between first and third table could be 1-Many as one work can be in multipe categories.

Upvotes: 1

dcaswell
dcaswell

Reputation: 3167

You create a table that maps words to categories.

Two columns in the table: categoryID, wordID

Upvotes: 2

Related Questions