How to properly store large objects with nested entities?

Question

PHP

There is a real estate object, it has many internal entities:

{
    "id": 1,
    "kind": "house",
    "location": {
        "latitude": 55.7178497,
        "longitude": 37.1396065,
        "country_id": 1,
        ...
    },
    "rent_offer": {
        "rub": 668687000,
        "usd": 10500000,
        "eur": 9438000,
        ...
    },
    "sale_offer": {
        "rub": 668687000,
        "usd": 10500000,
        "eur": 9438000,
        ...
    },
    "communication": {
        "sewerage_supply": "septic",
        ...
    },
    ...
}

There will be selections for these nested fields.
How are nested objects usually stored / combined into one?
So far, only the following ideas have come up:
1) Create a main objects table and divide it into additional objects_locations, objects_communications, ... then connect them with joins.
2) In the main table, make a large number of fields, but I do not consider 50-100 fields to be a convenient option.
3) ???
The experience of solving similar problems is very interesting, I will be glad for any answers :)

Answer 1

Very similar to a multidimensional cube. Look towards OLAP and, accordingly, towards the "star" database schema . Although, in your case, "snowflake" may be more acceptable.
Create a single feature table with a complex key containing fields with links to dimension tables. Measurements are location, offers, communications, etc. Normalization will suffer, the cost of registering/changing/deleting an object will increase, but the analysis in such a cube will be more optimal.

Answer 2

This is what foreign keys and one-to-many and one-to-one relationships are for.

Answer 3

1) +1 to the previous speaker;
2) The level of data normalization is up to you to choose how to assess the damage from redundancy.
3) You can take MongoDB if you are having trouble with relational databases. You can just put your JSON right there.

Answer 4

mysql 5.7 (and later) has the ability to use JSON and search it natively.
Of course, you do not need to cram the entire structure into one cell.
Your example can be written in one line in a table and spread across different cells starting from the 1st level: id, kind, location, rent_offer, sale_offer, etc.