Need to design a table with loads of attributes which must all be searchable by SQL

I'm in a fix here. I'm building a home reservation site and the client requires a filter search facility to allow visitors to search and filter through properties based upon criteria. The thing is that his list of criteria is exceedingly long, largely boolean values and calculable values stuff like:

Attached bathroom, balcony, smoking, alcoholic, maximum number of occupants, cable TV, Internet, carpeted, airconditioning, central heating, room serv开发者_如何学Pythonice, etc., etc., etc...

I'm thinking of having to create a field for each of these, but there's a very strong chance that the number of preferences might even go up. I dished out the idea of storing everying in a serialised object as a string as then it would be impossible to search using an SQL query. Do I have any options apart from setting up individual fields for each preference here?

Thanks. I'm using PHP MySQL.

Could you have a many-to-many table that ties the ID of the property to a reference table that has Attributes? The lookup table would contain a row for every attribute that the property has?

Main Table

ID PropertyName OtherCols

10 CottageA Stuff

20 CottageB OtherStuff

Attributes

100 Smoking

200 AirConditioning

300 Room Service

400 TV

500 Internet

Lookup Table

ID AttributeID

10 100

10 200

10 300

20 100

20 400

20 500

I did exactly the same search a couple years back for a hotel catalogue. We used a BitMask for that, e.g. we stored a single number representing all the possible values, e.g.

HotelTable 
ID Name         …    Features
 1 SuperHotel   …    5

Features
ID Name
 1 Balcony
 2 Shower
 4 Barrier-Free
 8 Whatever
 … …

In the example above SuperHotel would have a Balcony and be Barrier-Free (4+1).

While this worked well, I am not sure I'd handle it this way again. Basically, all these features are the same as tags, so you could just as well use the known approaches to create a tagging table.

I would suggest creating a field for each criteria. This is going to allow you to have a the fastest search capability. With that being said you could always create a TEXT or a MEDIUMTEXT and store JSON in that field. So you could use json_encode on an array such as:

$amenities['bathroom'] = 1;
$amenities['balcony'] = 1;
$amenities['smoking'] = 0;

Then if you were looking for a home that had a balcony you could do:

SELECT * FROM `homes` WHERE `json_field` LIKE '%balcony: 1%'

And instead of a LIKE you could always use FULLTEXT searches if you have that capability on your server.

You could do a mapping like suggested before but include a value in the mapping

Hotel Table

create table hotel_table (
 id int(4) unsigned not null auto_increment primary key,
 hotel_name varchar(40) not null,
 ...other row info
);

Hotel Criteria

create table hotel_criteria (
 id int(4) unsigned not null auto_increment primary key,
 criteria_name varchar(40) not null
);

Hotel Criteria Map

create table hotel_criteria_map (
 id int(4) unsigned not null auto_increment primary key,
 hotel_id int(4) unsigned not null,
 criteria_id int(4) unsigned not null,
 string_data varchar(20) null, #use this to add in extra string information for criteria
 decimal_data decimal(6,2) null, #use this to add in extra decimal information for criteria
 #... either of the above or other extra info, just giving examples ...
 unique key (hotel_id,criteria_id),
 foreign key (hotel_id) references hotel_table(id),
 foreign key (criteria_id) references hotel_criteria(id)
);

You could then select those values:

select * from hotel_table where id={your hotel id}; #hotel info

select m.*,c.criteria_name from hotel_criteria_map m, hotel_criteria c where m.criteria_id=c.id and hotel_id={your hotel id}; #criteria info

There may be a better way to do this but just a suggestion. You would only enter the criteria for a certain hotel into the map if it pertained to that certain hotel (essentially not having any criteria map rows that would be bool 0).

You should read up on database normalization. IMHO the goal is to structure your tables in such a way that allow these queries to be run efficiently and with less logic on the php side. For example, if each of the above mentioned search criteria have a value associated with them, the values should be stored in a separate table, and if there are many options to many values, you will need to setup a different type of relationship. This initial time looking at the database from all angles will save you a lot of make-shift php code and possible prevent an entire rebuild of the database once you realized you've bottlenecked. Hope this was at all helpful. Good luck!