Many-to-Many Relationships in MySQL

Question

I recently read foreign keys and joined them, and was pleasantly surprised that many of the basic concepts are things that I already put into practice. For example, with one project I'm working on now, I organize word lists and have a table for sets, for example:

`words` Table
    `word_id`
    `headword`
    `category_id`
`categories` Table
    `category_id`
    `category_name`

Now, generally speaking, this will be a one-to-many relationship, with several words placed under the same category with a foreign key category_id. However, suppose for a moment that the user wants to add another category to the word, making it many-to-many. Is there a way to set up my table wordsto handle additional categories of words without creating additional columns for example category_2, category_3etc.?

Answer 1

Usually you have a separate table to handle these mappings:

`Words_Categories` Table
    `word_id`
    `category_id`

Each pair in this table Words_Categoriesrepresents one possible mapping from any word to any other category.

A field category_idin a table Wordsbecomes unnecessary according to this scheme, since none of these tables directly refers to each other.

Answer 2

You SHOULD NOT create additional columns such as category_2 and category_3. So madness.

"category_id" "". "category_words" "category_id" "word_id". , . , .

Answer 3

You remove category_id from the word table and have a word_category table containing word_id, category_id (usually a unique index or constraint), which is usually called a link table or bridge table. This is a typical normalized many-to-many relationship.

Having multiple category_id columns in a word table is not recommended for many reasons, so regular forms exclude "arrays" from storage in rows.