What structure should be used to store data with multiple category identifiers?

Question

I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following:

ID | Place | Phone | Email | Category )

Now, I want to redesign the "Category" section to be able to include any given row in multiple (searchable) categories.

For example, let's say that there is a place called 'Lakeside Picnic Area.' Currently it is classified as 'Nature Reserve'. So, when people are searching for a nature reserve, it shows in their search results. However, I also want it to show up in "Food Area", "Wildlife", and "Viewing Point".

My question is this: How do I design my table/database structure to allow all of the data entries/rows to be searchable by multiple categories?

Answer 1

If a particular place can belong to multiple categories, you need to implement many-to-many relationship between places and categories. Usually it's done with link table:

Category(category_id(PK), category_name);
Place(place_id (PK),place_name,phone,email);
Place_Category(place_id (FK to place),category_id(FK to category), PK(place_id,category_id));

You may also want to mark a particular category as a 'primary' category for a place. There are two approaches , 1st is to have primary_category_id in Place, 2nd is to add a field to Place_Category , say relation_type ...