Database design for multiple models?

Question

MySQL

models table:
id - primary key
title - varchar(256)
model_instances table
id - primary key
model_id - foreign key to app_models.id
title - varchar(256)
model_fields table
id - pk
model_id - foreign key to models.id
instance_id - foreign key to model_instances.id
title - name of the field
type - enum [text, checkbox, radio, select, 'etc']
table model_field_values:
instance_id - forein key model_instance.id
field_id - foreign key to model_fields.id
value - text
Can be multiple values ​​for the field (for example, when the field is select multiple)
Problem: value is a field of type text, so that you can store different types (text, date, number), but searching by them will be slow and inefficient (if we search by number, it's still a text field).
For example, for 10 models, where each one already has 1000 instances with 10 fields each, the model_field_values ​​table will contain 100,000 entries, and if there are multiple fields, then 120,000 entries.
If separate tables were created, then there would be 10 tables with 1000 records each + several tables for storing multiple values.
Solutions:
For each model, create a model_field_values_{model id} table with the same structure.
Pros: any field can be made multiple (for example, several phone numbers, several emails for text fields), more tables - but fewer records in each, the cons are the same - value is always a text field).
Or, because we know that in model_fields, we can create model_field_values ​​for each model in the form
model_fields for model.id=1 (by primary key): 1 - text, 2 - integer, 3 - datetime, 4 - smalltext
Fields for model_field_values_1: field_1 text, field_2 integer, field_3 datetime, field_4 varchar(256)
model_fields for model.id=2 (by primary key): 1 - text, 2 - integer, 3 - integer, 4 - integer
Fields for model_field_values_1: field_1 text, field_2 integer, field_3 integer, field_4 integer
Again, this solution is not suitable for multiple values
​​PS Base design for a CRM system, where users themselves can create models (analogous to a separate table in the database) with different types of fields (analogous to columns in the database).
Can eat where to peep, what design to apply?

Answer 1

Try to smoke db sugarcrm