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

Question

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?

Please post something you have tried so as the question is better understood. — Stanley Mungai
– Stanley Mungai, Commented Sep 4, 2013 at 16:58

dcaswell · Accepted Answer · 2013-09-04 16:58:59Z

2

You create a table that maps words to categories.

Two columns in the table: categoryID, wordID

answered Sep 4, 2013 at 16:58

dcaswell

3,1572 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Biswajit Maji · Accepted Answer · 2013-09-05 07:04:42Z

2

You should have three table for this:

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

here word_id in word_table and cat_id in categories_table is primary key

edited Sep 5, 2013 at 7:04

answered Sep 4, 2013 at 17:01

Biswajit Maji

88913 silver badges23 bronze badges

2 Comments

eevaa Over a year ago

Is it possible to link the first and third table so that everytime a word is added to table 1 its ID is automatically added to table 3?

Mosty Mostacho Over a year ago

@eevaa: Impossible. How do you tell to which category it would be added then? Biswajit: Please, mark the primary keys... it is essential to the answer.

kishore krv · Accepted Answer · 2013-09-04 22:24:37Z

1

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.

answered Sep 4, 2013 at 22:24

kishore krv

702 bronze badges

1 Comment

Mosty Mostacho Over a year ago

Actually, instead of could be I'd say must be in order to transform the many to many conceptual relationship into the physical level.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related