Help joining and filtering multiple tables with MySQL

Question

Hi everyone. I am working on a reviews website. Basically you can choose a location and business type and optionally filter your search results by various business attribures. There are five tables at play here:

Businesses

ID Name LocationID

Locations

LocationID LocationName State

Attributes

AttributeID AttributeName

AttributeValues

AttributeValueID ParentAttributeID AttributeValue

BusinessAttributes

ID AttributeID AttributeValueID

So what I need is to work out the query to use (joins?) to get a business in a particular location based on attribute values.

For example, I want to find a barber in Santa Monica with these attributes:

Price: Cheap Open Weekends: Yes Cuts Womens Hair: Yes

These attributes are stored in the Attributes and AttributeValues tables and are linked to the business in the BusinessAttributes table.

So let's say I have these details from the search form:

LocationID=5&Price=Cheap&Open_Weekends=Yes&Customs_Womens_Hair=Yes

I need to build the query to return the businesses that match this location and attributes.

Thank you in advance for your help and I think StackOverflow is awesome.

Answer 1

Thinking about your data needs, you may be a perfect candidate for a schema-free document oriented database. On a recent episode of .Net Rocks (link to show), Michael Dirolf talked about his project MongoDB.

From what I understand, you could take each Business entity and store it in the database with all its associated attributes (LocationID, Price, Open_Weekends, Customs_Womens_Hair, Etc.). Each entity stored in the store can have different combinations of attributes because there is no schema. This natively accomplishes what you are trying to do with an Attribute and Attribute_Value table.

To search the database, just ask it for all entities that have the particular set of keys and values you need. No complex joins and no loss of performance. What you are doing is exactly what schema-free, document based databases are designed for.

Michael Dirolf: Yes, I think that a lot of the people who are switching are people who have sort of got themselves into corners where they are using relational database the way that we use MongoDB.
Richard Campbell: Right.
Michael Dirolf: So having columns that, a column key and a separate column value and inserting stuff that way so that they get done in schema and all sorts of crazy stuff like that…
Richard Campbell: Yeah, now in reflection I suddenly realized I just describe your perfect customer, a guy who has taken, you know, abusing SQL Server as they say. We’re going down this funny path and you just shouldn’t be here in the first place.

If you keep going down the path of building a relational attribute/value store, your performance will suffer with the combonatoric explosion that results.