A
A
Alexey Semyonov2018-04-16 11:52:06
MySQL
Alexey Semyonov, 2018-04-16 11:52:06

How to correctly design relationships of several tables?

Good afternoon, I work on the Yii2 framework, now I will briefly explain the essence of the problem.
I need to display 3 types of objects on the page: this is a house, quarters, commerce. (Naturally, some of their fields are the same, and some are different). And the question arises of how to implement this:
Option 1 - create 1 common table and somehow link 3 other tables by links (see house, apartments and commerce)
Option 2 - create 3 different tables and display them on 1 page (but so far it’s hard for me to imagine how to implement it)
Who understands this topic, please comment on this issue
, here’s roughly how it looks visually:
5ad46432c986c985672355.png
Explanation:
task: admin can create 3 types of objects (analogy with goods), these objects have common parameters, such as name, cost, location. But depending on the selected object (such as a house, apartment, commercial premises), additional parameters are added. The name is that the admin exposes these objects for "sale", and customers buy them (if you don't go into details).
And the main problem I have is how to connect these additional parameters (for a house it is having communications (gas, water, etc.), for an apartment it is the number of rooms or registered people, for commerce it is a legal status).
To put it even more simply, admins sell these very places of residence / workplace. And clients need all kinds of objects to be highlighted to them.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Maxim Timofeev, 2018-04-16
@iGyry

the table stores the entity, its type and general parameters. thing: id | name | type | cost, etc.
the second stores the differences things_params: id | thing_id | param_name | param_value
thing has a static method that returns an array of valid param_names for each thing. As an option, this can be put in the third table.
Thus, you can add crap (thing) and their parameters dynamically in unlimited quantities. The only downside is a slight complication of queries (filtering by parameters). But that is clearly correct.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question