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

Question

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

Answer 1

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.

Answer 2

If you are planning to search, sort or aggregate on any of those values, you need to store them in tables. For example:

• You want to make a feature where the end user can filter out properties that don't have internet
• Your property manager wants to know what percentage of properties have air conditioning

Extracting and analysing all the JSON for these kind of tasks would be needlessly difficult. If you know you will rarely have to do anything like that, it's fine to store the data in an unstructured way.

That said, try to be aware of "feature creep". It may be better to expend the effort to build a fully capable schema now, than find yourself locked in to a suboptimal solution much later in the project. But if you just need to hack out something that works, then just do that.