Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
Thank you, @a1ex07 –  user1259798 Jun 5 '13 at 7:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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