The model has many parameters: serialization or a separate table?

Question

MySQL

I have a question of the following nature.
At the moment, I am designing a web application in PHP/Yii. It has a structure of models connected to the MySQL database using Active Record.
For each model, I have already registered the fields in the database that it definitely needs and for which it will need to search for records. However, each model will have many other parameters in the future that I don't know about yet. Moreover, in the process of development, there will be more and more of them.
Therefore, I want to extend Active Record so that I can add arbitrary parameters to the database, even if there is no field in the database for this parameter.
And I see two solutions for this:
1. Put all these parameters into an array that will be serialized / deserialized when writing / receiving from the database and stored in a separate field.
2. Add these parameters to a separate table in the database, with fields like id, model_name, model_id, param_name, value.
What are the advantages and disadvantages of these methods, and which one is "more correct"?

Answer 1

It seems that an important point is missed when describing the problem, but how these fields will be used. Will data from them always be needed or only in certain cases?
Let's see the pros and cons of the solutions. Well, as I see them.
For option number one.
Pros:
- from the PHP side it will be easy to expand AR
Cons:
- unserialize is slow, of course there are json and igbinary, but it's still time
- if there are "more and more" fields, then in MySQL serialized data will need to be stored in a TEXT / BINARY field , if we are talking about standard MySQL ENGINEs, then when fetching, such fields are not loaded into memory, but read from disk, well, you understand that disk I / O operations are expensive (in the case of InnoDB, the situation may be better there)
- if we have a case when we rarely need data from "unknown" fields, then with this approach, either it will be necessary to draw workaround in AR so that these fields are not dragged, or the data will be constantly sent to the m / db and PHP, plus in memory PHP hang
- if the database grows and you need to pull the "unknown" field into the search / filtering, changing the table schema will take time, pulling data from the serialized field and filling in the new field will take time, building an index on the new field will take time. Lots of data - lots of time, service downtime.
For option two.
Pros:
- if the data size in the fields is limited and fits into the VARCHAR type, we solve the problem number two of the first method
- scalable
- implementation of a search/filter by field will not entail reworking the structure of tables
Cons:
- if data is always needed, JOIN will turn out, if there are many fields with data, then JOIN will be slow. If the field type is TEXT ... well, you understand :)
- one problem can be solved by caching, but the implementation in PHP within the framework of AR will no longer be the easiest task
- yes, in general, within the framework of AR Yii, the implementation of such an approach with lazy loading will require such a normal understanding how AR in Yii works and where it needs to be changed so that everyone is
happy There is no right option, as you understand, you need to weigh all the pros and cons, understand how much it makes sense to do "cool" now and waste time or you can get into technical debt, but quickly launch tomorrow.

Answer 2

What's wrong with a many-to-many relationship?
when there is one table, this is some entity, the second contains some parameters, and the third, connecting, contains FK for the first two (it is possible that the values ​​​​are stored in the third, so I think even better)

Answer 3

Did EAV (many to many) at the initial stage in one project. Then he refused. Because the amount of hemorrhoids is only greater. Anyway, if you want native validation, you need to create a variable in the model, set up a getter and setter, handle beforeSave, etc. I also used a third-party EAV component, where it also asked for metadata. As a result spat, made fields in the table and I am not worried.