How to properly build a database to store list, multilist and text values?

Question

MySQL

Hello people of Toaster!
There is a migration for "transactions" and a transaction can have a "child" entity attached (Link: many to one).

public function up()
    {
        Schema::create('leads', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('status_id');
            $table->foreign('status_id')->references('id')->on('statuses');
            $table->unsignedInteger('child_id');
            $table->foreign('child_id')->references('id')->on('children')->onDelete('cascade');
            $table->timestamps();
        });
    }

Also, the transaction has mandatory properties that do not change (for example: status_id).
The task is that it is necessary to write a migration to store additional transaction properties, and the properties can be of different types (list, multilist, text, etc.).
You also need to write a migration to store the settings for these very properties with the ability to add, remove and change (except for the type) properties for the deal.

Answer 1

Your project is interesting. Deal, child ... I hope not to sell them))
In order to do this, you need to create tables
1. properties in it you will need id, property name (child's gender, height, weight or whatever), units of measurement (kg , pcs.), property type (text, number, date, list, button, checkbox, etc.)
2. Depending on the property type, you may need to offer options, this just applies to lists
. you will keep them. You can create a table, you can store it in JSON in the property itself.
If the option is with a table, then
property_values
​​id|prop_id|value
2. This is a table linking a child or a transaction with a property. In the example, let's take the deal
lead_property
id|prop_id|lead_id|value
You can use such package. I didn’t work with him, I don’t know if he is good or bad
https://sunel.github.io/eav/