Which object storage scheme to choose?

Question

PHP

The project belongs to the real estate sector.
Each property strictly corresponds to one of the types: apartment, house, garage, etc.
Each property has certain common properties (address, area, price, etc.)
And there are properties that correspond only to a specific type of object (type - house: plot area, presence of a pool, etc.)
Now each type of object has a own table in the database (MySQL).
It lists both common properties for all objects and unique ones.
In the new version of the project, it is planned to increase the types of objects.
If you work according to the old scheme, then you need to add a lot of tables.
Many tables are inconvenient:

• each table has its own ID
  
• you can’t just select all objects of one manager - you need to make sequential selections from several tables
  
• if you enter some common parameter, you need to enter it in many tables
  
• ...
  

The solution to these problems seems to be the creation of one common table of objects.
Now the question arose about choosing a data storage scheme:
One large table
Here, in one, all possible properties of objects will be listed.
Moreover, the field area of ​​the plot for an object of the apartment type will not be filled.
The option is simple but redundant.
Several tables Objects
table , it will contain only general properties Table of object types : apartment, house, plot... Table of all properties : number of rooms, living area, availability of furniture.... Table of properties of types will show a set of properties corresponding to the type (one type many properties)



The property table of objects is the value of the properties of a particular object (one object has many properties)
It seems like the right option, but is it worth it to complicate things like that?
How and is it worth implementing data storage in accordance with the data type: int, varchar, enum, text ....
Or should all values ​​be written as text? - Is there a loss of database functionality?
How then to sort objects by some property?
Please share your experience in implementing similar projects.
Maybe there are other solutions.
To date:
Number of objects more than 5 thousand
Types of objects 25
Properties more than 80

Answer 1

Store. for example, each property is a separate table with a link + a table of aggregated data for each object. So you get a selection of all objects by property + fast return of data. 5000 objects is not the number to bother about taking up space. And you can store links not in each table, but in a separate linking table. In other matters, I don’t see that there was any drawdown in speed even at 80 joins, if there is a primary key connection everywhere.

Answer 2

Actually, in the second option, you came to the EAV model, it is more universal, but at the same time slower than the classical one, requires more complex queries and is more difficult to control integrity.

Answer 3

Finished this site the other day. head-scratching stopped at eav with several types of fields
this is how it turned out
ads
category ads
common fields for ads from all categories
properties of categories property
values ​​for ads
the main jamb of eav is that it’s impossible to select with one request depending on the filter settings, you have to collect IDs for each parameter and then intersect them, something like this

private function get_offers($page, $per_page)
{
    $filter_fields = $this->get_current_filter_enabled_fields();
    $filter_values = $this->s('@filter~:values_by_cats/' . $this->cat->id);

    //

    $offers_ids_sets = array();
    $offers_extra_ids_sets = array();

    if ($filter_values) {
        foreach ($filter_values as $field_id => $data) {
            if (isset($filter_fields[$field_id])) {
                $field = $filter_fields[$field_id];

                $prop = $field['prop'];
                $common_prop = $field['common_prop'];

                if ($prop) {
                    $type = $prop['type'];

                    if ($type == 'bool' && $data['value']) {
                        $offers_ids_sets[] = PropValue::where('prop_id', $prop['id'])
                                ->where('bool_value', true)
                                ->lists('offer_id')->toArray();
                    }

                    if ($type == 'strings_list' && isset($data['selected']) && !is_null($data['selected'])) {
                        $prop_settings = _j($prop['settings']);

                        if (isset($prop_settings['strings_list']['items'][$data['selected']])) {
                            $offers_ids_sets[] = PropValue::where('prop_id', $prop['id'])
                                    ->where('string_value', $prop_settings['strings_list']['items'][$data['selected']])
                                    ->lists('offer_id')->toArray();
                        }
                    }

                    if ($type == 'numbers_list' && isset($data['selected']) && !is_null($data['selected'])) {
                        $prop_settings = _j($prop['settings']);

                        if (isset($prop_settings['numbers_list']['items'][$data['selected']])) {
                            $offers_ids_sets[] = PropValue::where('prop_id', $prop['id'])
                                    ->where('number_value', $prop_settings['numbers_list']['items'][$data['selected']])
                                    ->lists('offer_id')->toArray();
                        }
                    }

                    if (($type == 'numbers_list' || $type == 'number') && isset($data['min']) && isset($data['max'])) {
                        $offers_ids_sets[] = PropValue::where('prop_id', $prop['id'])
                                ->where('number_value', '>=', $data['min'])
                                ->where('number_value', '<=', $data['max'])
                                ->lists('offer_id')->toArray();
                    }
                }

                if ($common_prop) {
                    $type = $common_prop['type'];

                    if (($type == 'numbers_list' || $type == 'number') && isset($data['min']) && isset($data['max'])) {
                        $offers_ids_sets[] = OfferExtra
                                ::where($common_prop['field'], '>=', $data['min'])
                                ->where($common_prop['field'], '<=', $data['max'])
                                ->lists('offer_id')->toArray();
                    }
                }
            }
        }
    }

    $offers_ids = array();

    if ($offers_ids_sets) {
        $offers_ids = (array)$offers_ids_sets[0];

        for ($i = 1; $i < count($offers_ids_sets); $i++) {
            $offers_ids = array_intersect($offers_ids, (array)$offers_ids_sets[$i]);
        }
    }

    //

    $builder = $this->cat->offers();

    if ($offers_ids_sets) {
        $builder = $builder->whereIn('id', $offers_ids);
    }

    $builder = $builder->whereHas('extra', function ($builder) use ($offers_extra_ids_sets) {
        $builder->where('enabled', true);
    });

    $count = $builder->count();

    $offers = $builder->orderBy('created_at', 'DESC')->offset(($page - 1) * $per_page)->take($per_page)->get();

    return array($count, $offers);
}

and in general it is impossible to sort by parameters, if sorting by some parameter is needed, you will have to transfer it to the table of goods themselves
, but still, in terms of flexibility, it outperforms other options. the client himself through the interface can steer his categories of properties and so on. if there was a table in which everything was, it would be hung up with each such operation, for a time proportional to the number of goods