0

I had run in an case where I need some help, I have a web application where admin can create forms from the dashboard, each form has fields and properties where fields consists of image, name, type, required, public and properties consists of only name

So I created the tables as below:

forms_table:

ID | Name | status

forms_fields_table

ID | form_id | name | slug | type | options | required

forms_properties

ID | form_id | name | slug

And on the other side other users can create a real-estate where they will have to fill the data of the real-estate category selected form, So i save filled form data in a column in real-estate table

the data saved like this 

[
  {slug_value}: {
    "name": "xxxx",
    "value": "xxxx",
    "slug": "ssss",
    "value": 42
  },
  {slug_value}: {
    "name": "yyy",
    "value": "yyy",
    "slug": "aaaa",
    "value": 42
  },
]

where all these data are dynamic and the key of each json is the same as slug, and the user should be able to filter the real-estates by these dynamic data by slug and value.

So the question comes here, I know that I can filter data by nested JSON like this : DB::table('realestates')->where('attributes->{slug_name}->value', 'red')->get(); but I didn't find a way to index dynamic nested JSON so it will take some time in filtering process.

And as I was thinking, it came to my mind what if I store these dynamic data as string and filter the string like this attributes = "value":"search_query" but I don't want to change the structure for nothing.

I'm using laravel

Shreif A.
  • 39
  • 1
  • 10
  • What MySQL likes: structured data, e.g. tables with proper relations, fixed columns, one value per column and every information stored once (aka normalized data). You have dynamic forms and storing data of those virtual tables in a dynamic json structure. That's flexible, but won't be fast, as MySQL cannot use the tools a relational database provides for structured data, e.g. indexes. A compromise (apart from using a different dbms) might be to use an eav-model, see e.g. [here](https://stackoverflow.com/q/11779252), it's what you would end up with if you pursue your idea in your last sentence. – Solarflare Jul 01 '18 at 09:33
  • Are the possible values of `slug_value` limited? – Jonas Staudenmeir Jul 01 '18 at 13:31
  • @Solarflare thanks for your response, I read your reference but actually there are a lot of things that I didn't fully understood. I will try to search more. thanks again – Shreif A. Jul 02 '18 at 11:36
  • @JonasStaudenmeir unfortunately no :( – Shreif A. Jul 02 '18 at 11:37
  • The only possible way I see: After a few weeks/months of people using your app, you analyze the slugs and look at the most popular ones. Then you add indexes for these slugs. – Jonas Staudenmeir Jul 02 '18 at 14:08
  • Thank you @JonasStaudenmeir, I think that is the only solution right now, Will try to search more. – Shreif A. Jul 03 '18 at 08:02

0 Answers0