WHERE statement with dynamic input

Question

I have two tables. The first one (item) is listing apartments. The second (feature) is a list of features that an apartment could have. Currently we list about 25 different features.

As every apartment can have a different set of features, I think it makes sense to have a 1:1 relationship between items and features table.

If in feature table for one the features the value is '1', this means that the linked apartment has this feature.

+-------------+------------+--------------+-------------+------------+
| table: item |            |              |             |            |
+-------------+------------+--------------+-------------+------------+
| id          | created_by | titel        | description | address    |
+-------------+------------+--------------+-------------+------------+
| 10          | user.id    | Nice Flat    | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 20          | user.id    | Another Flat | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 30          | user.id    | Bungalow     | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 40          | user.id    | Apartment    | text        | address.id |
+-------------+------------+--------------+-------------+------------+

+----------------+---------+--------------+----------------+--------------+------+
| table: feature |         |              |                |              |      |
+----------------+---------+--------------+----------------+--------------+------+
| id             | item_id | key_provided | security_alarm | water_supply | lift |
+----------------+---------+--------------+----------------+--------------+------+
| 1              | 10      | 1            | 0              | 0            | 1    |
+----------------+---------+--------------+----------------+--------------+------+
| 2              | 20      | 0            | 1              | 1            | 0    |
+----------------+---------+--------------+----------------+--------------+------+
| 3              | 30      | 1            | 1              | 0            | 1    |
+----------------+---------+--------------+----------------+--------------+------+
| 4              | 40      | 1            | 1              | 1            | 1    |
+----------------+---------+--------------+----------------+--------------+------+

I want to build a filter functionality so user can select to show only apartments with certain features. e.g.:

$key_provided = 1;
$security_alarm = 1;
$water_supply = 0;

Does this database approach sounds reasonable for you?

What’s the best way to build a MySQL query to retrieve only apartments where the filter criteria match, keeping in mind that the number of features can be grow in future?

Answer 1

A better approach is to have a features table. In your case, they all seem to be binary -- yes or no -- so you can get away with:

create table item_features (
    item_feature_id int auto_increment primary key,
    item_id int not null,
    feature varchar(255)
    foreign key item_id references items(item_id)
);

The data would then have the positive features, so the first item would be:

insert into item_features (item_id, feature)
    values (1, 'key_provided'), (1, 'lift');

This makes it easy to manage the features, particularly adding new ones. You might want to use a trigger, check constraint, or reference table to validate the feature names themselves, but I don't want to stray too far from your question.

Then checking for features is a little more complicated, but not that much more so. One method is explicitly using exists and not exists for each desired/undesired one:

select i.*
from items i
where exists (select 1
              from item_features itf
              where itf.item_id = i.item_id and
                    itf.feature = 'key_provided'
             ) and
      exists (select 1
              from item_features itf
              where itf.item_id = i.item_id and
                    itf.feature = 'security_alarm'
             ) and
      not exists (select 1
                  from item_features itf
                  where itf.item_id = i.item_id and
                        itf.feature = 'water supply'
                 );

Answer 2

For your existing data structure, you can filter as follows:

select i.*
from item i
inner join feature f 
    on  f.item_id = i.id
    and f.key_provided = 1
    and f.security_alarm = 1
    and f.water_supply = 0

This will give you all the apartments that satisfy the given criteria. For more criterias, you can just add more conditions to the on part of the join.

As a general comment about your design:

• since you are creating a 1-1 relationship between apartments and features, you might as well consider having a single table to store them (spreading the information over two tables does not have any obvious advantages)

• your design is OK as long as features do not change too often, since, basically, everytime a new feature is created, you need to add more columns to your table. If features are added (or removed) frequently, this can become heavy to manage; in that case, you could consider having a separated table where each (item, feature) tuple is stored in a different row, which will make this of things easier to do (with the downside that queries will get more complicated to write)