chris_stfc — 2011-04-04T14:39:45-04:00 — #1
Hi, wondering if anyone can kindly help please
After some help with a business search I have on a business directory. In the search you can either search for a company name ie:
Gregs Bar & Restaurant
Or a Business type ie:
And in my database I have a businesses table:
And a categories table:
So what I want to do is run a query which will query these two tables which I do like so.
WHERE businesses.name = 'Fast Food'
WHERE categories.name = 'Fast Food'
However, where I'm stuck is where a user searches for a category ie: Fast Food. For example when Fast Food is searched for I also want results returned for:
Take Away – as it falls into the same category as fast food
Quick Bites – as it falls into the same category as fast food
(As they are all kind of the same thing)
So for example I want my search to :
1) Query the two tables for what the search term ie: Fast Food
When it finds Fast Food in the categories table look in the also_falls_into_category column of this table for Fast Food (piped separated)
2) Then explode on the pipes and continue with the query and output all the businesses in the businesses table where any of the pipe separated values are matches in the falls_into_category column. So in effect when you search for fast food, you also get results returned for take away and quick bites.
Can anyone help please?
r937 — 2011-04-04T14:45:51-04:00 — #2
your first problem occurred when you decided to store multiple id values in a single column
this violates the first normal form of database design
what you'll want to do is replace that column with an additional one-to-many table, so that each category is in its own row
system — 2011-04-05T07:49:40-04:00 — #3
Thanks for your help. I've now normalized my database as shown:
So a business can have many categories
However I don't get how this would work now i''ve normalized the data. For example, take this data inserted into the two tables:
Say someone searched for cafe then Bob's Cafe & Restaurant would be outputted (as it's category_id = 2), but you could argue that if a search for Take Away, or Fast Food was carried out then Bob's Cafe & Restaurant would also get the results returned (hence the pipes in my last example).
How would I get round this, can't quite work it out in my head?
r937 — 2011-04-05T10:19:58-04:00 — #4
you didn't normalize it properly, sorry
what you want is this --
each business can have multiple categories, so there could be multiple rows in the business_categories table for the same business
each category can have multiple businesses, so there could be multiple rows in the business_categories table for the same category
it's a classic many-to-many relationship
system — 2011-04-05T10:21:06-04:00 — #5
Excellent - thank you for your help, understand now
ps: defintely buying your book in the future!