MySQL multiple values in fields, EAV, etc

Sorry the title couldn't be a more descriptive I still don't know the name of what I'm dealing with.

I'm developing a search system for a realty site and it was working well until I realized I had forgotten to take for account that some of my fields (which are used as filters in the search page) could be multiple values. What I mean is that I had only one field for Sale and Rent and one only one field as well for Residential and Commercial (other fields too) -- the problem is that a property could be for sale or rent or could be residential, commercial and industrial.

What I thought I'd do is move those to their own table. The thing is, I have yet another table that lists the possible values for each field, which is used to display value names, populate forms, used as constraints, etc.

So now I'm stuck with a myriad of tables.

A table for properties, then tables for values, then tables that join these values to the properties.

A plain search query with no filters (more filters, more joins) I'm at 7 inner joins.

I'm having an extremely hard time getting joins to work both for constraining results and returning values in the SELECT portion of the query.

I'd appreciate any suggestions as this problem has left me mentally fatigued for the last 2 days.

EDIT:

I have the following tables at the moment:

properties

properties_images

properties_listings

properties_options

properties_purposes

res_geo_address

res_geo_cities

res_geo_neighborhoods

res_geo_states

res_property_options

res_property_type_age

res_property_type_listing

res_property_type_property

res_property_type_purpose

res_property_type_purpose_property

The properties table is the main property table, has a handful of colums that describe the property.

The properties_ tables are used to bridge the p开发者_StackOverflow中文版roperties table and the res_property_ tables (usings ids - one for the property and one for the value) (exception is the images, which just contains image records)

The res_ tables list id and value names for the most part. (exception are the res_geo tables)

As of right now, my queries don't work. To be honest I've reached such intricacy that is hard for me to explain my current setup.

I'd be nice if there was a simple way to query tables with fields that have arrays and easily retrieve those.

EDIT 2:

The image above shows one of the joins. How would I be able to query for a property, always retrieve a concat of all the listing name associated with it but optionally limit the properties by a certain group of listing ids?

---

If this is what you're describing:

           Entity
             ^
             |
     EntityPropertyValue
             |
             |
Property<----      PropertyValue
   ^                     |
   |                     |
    ---------------------

Where Property defines the various properties that can be applied to a given Entity, PropertyValue defines the values that are valid for a given Property, and EntityPropertyValue defines the value for a given property for a given Entity, then what you have is pretty much exactly EAV (just substitute the word Attribute for Property and you will have exactly EAV).

There's nothing inherently wrong with this architecture, and there are certainly some circumstances where it's a valid (and possibly the only valid) choice. Your circumstance does sound like it's a good candidate for EAV.

The trouble, as you've discovered, is that it can make querying difficult, especially when you're going to allow the user to specify multiple values for a given property. This will make constructing your result set difficult, as you're going to have to account for potential cartesian products. I can't give you any further advice without seeing an actual query that you're having trouble with (feel free to edit your question and post a comment on my answer; I'll revise it and add some advice if you do), but I can tell you that simply having a "myriad of tables" is not necessarily bad, and, in fact, sounds like the correct design choice given your parameters.

---

Sounds like you are the victim of the very bad advice to use an integer key for everything, even simple tables that have only a single real column with values like 'Sale', 'Rent' etc.

Your table RES_PROPERTY_TYPE_LISTING should have only one column "TypeCode" (using "code" is a very old convention for when we use character primary keys), which is the primary key.

Now you get to drop one table, because PROPERTIES_LISTINGS looks like this:

ID_PROPERTY    TypeCode
-----------    ---------
     1           Sale
     1           Rent
     2           Rent

As for the idea that integers make faster joins, we've just eliminated a JOIN, and the fastest JOIN is the one you don't have to do. The remaining JOIN is still on integers, so you are safe when you hit 10,000 trx/second, because before that you'll never see the difference.

Best of luck!