0

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?

1 Answer 1

0

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 ...

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.