I looking for a solution to design database for property real estate

I looking for right method how can I design part of database schema for my real estate website

I have table "proprety_real_estat" contain Country, city, town and type of property (screenshot 01)

screenshot 01

my problem:

I want to add many others fields home features checklist below

Wifi (yes/no)

TV(yes/no)

Gas(yes/no)

Electric(yes/no)

Garage(yes/no)

Parking(yes/no)

Balcony(yes/no)

...

...

and many others (screenshot 02)

screenshot 02

my quesitons :

01- if I add all this fields in the same table "proprety_real_estat" ??

02- if I make in JSON file after that I will save the path of this file in field ??

else if there are a better solution than mine ??

thank you in advance

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”.

(from http://dev.mysql.com/doc/refman/5.7/en/set.html)

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.

StackExchange Database Administrators Q#150923, answer score: 2