Best Practice to querying a Lookup table

Question

I am trying to figure out a way to query a property feature lookup table.

I have a property table that contains rental property information (address, rent, deposit, # of bedrooms, etc.) along with another table (Property_Feature) that represents the features of this property (pool, air conditioning, laundry on-site, etc.). The features themselves are defined in yet another table labeled Feature.

Property
    pid - primary key
    other property details

Feature
    fid - primary key
    name
    value

Property_Feature
    id - primary key
    pid - foreign key (Property)
    fid - foreign key (Feature)

Let say someone wants to search for property that has air conditioning, and a pool and laundry on-site. How do you query the Property_Feature table for multiple features for the same property if each row only represents one feature? What would the SQL query look like? Is this possible? Is there a better solution?

Thanks for the help and insight.

Answer 1

In terms of database design, yours is the right way to do it. It's correctly normalized.

For the query, I would simply use exists, like this:

select * from Property
where 
exists (select * from Property_Feature where pid = property.pid and fid = 'key_air_conditioning')
and
exists (select * from Property_Feature where pid = property.pid and fid = 'key_pool')

Where key_air_conditioning and key_pool are obviously the keys for those features.

The performance will be OK even for large databases.

Answer 2

Here's the query that will find all the properties with a pool:

select
    p.*
from
    property p
    inner join property_feature pf on
        p.pid = pf.pid
    inner join feature f on
        pf.fid = f.fid
where
    f.name = 'Pool'

I use inner joins instead of EXISTS since it tends to be a bit faster.

Answer 3

You can also do something like this:

  SELECT * 
    FROM Property p
   WHERE 3 =
         ( SELECT COUNT(*)
             FROM Property_Feature pf
                , Feature f
            WHERE pf.pid = p.pid
              AND pf.fid = f.fid
              AND f.name in ('air conditioning', 'pool', 'laundry on-site')
         );

Obviously, if your front end is capturing the fids of the feature items when the user is selecting them, you can dispense with the join to Feature and constrain directly on fid. Your front end would know what the count of features selected was, so determining the value for "3" above is trivial.

Compare it, performance wise, to the tekBlues construction above; depending on your data distribution, either one of these might be the faster query.