Depends what do you want to optimize for.
For smallest space consumption the easiest way is to create a field where you store the features as bits. For example:
Cuisine - 1
Internet - 2
TV - 4
Then having internet and TV would be 110 = 6. But this is going to be hard to search for and requires some magic to do bitwise operations to extract the features.
A middle ground would be to use SET and store the features which the real estate has aka. values that are set to yes. The downside is that each time you need a new feature it requires schema change and
A SET column can have a maximum of 64 distinct members. A table can
have no more than 255 unique element list definitions among its ENUM
and SET columns considered as a group. For more information on this
limit, see Section C.10.5, “Limits Imposed by .frm File Structure”.
The most flexible and probably the best performing option for search is to have a feature table where you store all the possible features. And there's a table to store what property has what feature. The downside of this approach is space. Assuming your using InnoDB every row will have a 13 bytes overhead + the two columns (property_id, feature_id) ~ 6 bytes. So you can expect something around
20 bytes * number of properties * avg number of features per properties. (To compare the first option will need
number of unique features bits * number of properties)
create table feature (
id smallint unsigned PRIMARY KEY AUTO_INCREMENT,
name varchar(64) NOT NULL,
UNIQUE KEY (name)
create table property_feature (
property_id int unsigned NOT NULL,
feature_id smallint unsigned NOT NULL,
PRIMARY KEY (property_id, feature_id)
As per your example assuming 100000 properties the theoretical calculations would be:
30 bits * 100000 = 366kb
20 bytes * 100000 * 18 = 35MB (still not something to worry about though)
Note: in reality you would probably store the 30 bits in an unsinged int so it will take 4 bytes. So on disk it would consume around 390kb.