I've been reading up on foreign keys and joins recently, and have been pleasantly surprised that many of the basic concepts are things I'm already putting into practice. For example, with one project I'm currently working on, I'm organizing word lists, and have a table for the sets, like so:
`words` Table
`word_id`
`headword`
`category_id`
`categories` Table
`category_id`
`category_name`
Now, generally speaking this would be a one-to-many relationship, with several words being placed under a single category with the foreign key category_id. Let's assume for a moment, however, that a user chooses to add another category to a word, making it many-to-many—Is there a way to set up my words table to handle additional categories for words without creating extra columns like category_2, category_3, etc.?
Usually you have a separate table to handle these mappings:
`Words_Categories` Table
`word_id`
`category_id`
Each pair in this Words_Categories table represents one possible mapping from any word to any other category.
The category_id field in the Words table becomes unneeded under this scheme, as neither of these tables references each other directly.