Hello, I have a real estate ad site. When adding a client, you can specify several rubrics that the client is interested in:

  1. House rent
  2. Commercial real estate for rent
  3. Rent a room

Each type of property has its own set of parameters that the client enters:

  • Rent a house (area, number of floors, amenities (tv, wifi) .. and so on
  • Rental of commercial real estate (area, type, etc.)
  • Room rental (area, number of rooms, amenities, etc.)

Here I do not know how to organize the database to store all the data. While the idea is to make 3 tables - object_type, attributes and attributes_value

object_type ('id', 'name') attributes ('id', 'type', 'name', 'add_options', 'object_type_id') attributes_value ('id', 'attribute_id', 'value', 'client_id')

In the object_type table, for example -

name = 'Продам дом'

In the attributes table, for example -

 name = 'Количество комнат' type = 'text' object_type_id = object_type.name = 'Продам дом'

In the attributes_value table, the attribute entry id, and value. Those. will be offered to the client by attributes.name, depending on the object_type we chose earlier. And the field for the record is the value from the attributes_value table.

But I somehow don’t really like this approach, maybe someone faced such a problem ?? How best to organize a database, and how easier it will be to work with it through PHP, using the yii2 framework.

    2 answers 2

    Use the Class Table Inheritance approach (Inheritance with class tables)

    Select the common table

    1. rent - General ad table
      • id - Ad Number
      • title - Ad Title
      • description - Ad description
      • area
      • type - type of advertisement (RENT_HOUSE, RENT_COMMERCIAL, etc.)

    We make tables with additional parameters for each declaration, we will join them depending on the type field:

    1. rent_house - Additional table of parameters for renting a house

      • rent_id - Advertisement number
      • number_floors - Number of floors
      • land_area - Plot area

    2. rent_commercial

      • rent_id - Advertisement number
      • type - Type
      • ceiling_height - ceiling height

    Select the common table with amenities , because There are several types of ads:

    1. rent_comfort - Amenities table
      • rent_id - Ad Number
      • comfort_name (or comfort_id) - The name of the type of convenience (you can use varchar or create a separate table that will point to the list of amenities and use a foreign key)

    The remaining tables can be designed independently based on a similar structure. Your first approach is similar to EAV (Entity-Attribute-Value) and it is good to use it on small amounts of data, otherwise in the future the table with attributes may grow and this will severely affect performance, the cost of implementing data type checking and casting will increase, working with they, and in fact, this approach is often called the inversion of the relational structure inside out, but no one says that they cannot be used, everything depends on the task, it is very convenient to store for example system settings.

    system_settings

    • setting VARCHAR (255)
    • value VARCHAR (255)

    You can highlight the main features of these entities in the main table, I think there are quite a few of them (for example, area, etc.). The rest is in dedicated tables for each type.