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

Question

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 service, 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.

Answer 1

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

Answer 2

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.