0

I am trying to build an application, where users can create unlimited "parsing fields", that will contain either a:

  1. String
  2. Array
id | name  | type
-------------------
1  | name  | string
2  | table | array

The actual content of the fields is coming from different sources, such as PDF files (pdftotext), OCR etc.

For each field, I need to be able to transform the content data, for example:

#Example of string field transformation.
$content = Field::Find(1)->content; //For example "Foo"
$Method = return str_replace("Foo","Bar", $content);

Ultimately, I need to serve this output to 3rd party API's with JSON, like:

{
 "name":"Bar",
 "table": [{
      "0": "Cell #1",
      "1": "Cell #2"
 },{
      "0": "Cell #1",
      "1": "Cell #2"
 }]
}

I am unsure on how to design my MySQL database to allow for such a setup. My original thought was to store the original field content in a table field_content like below - where the content column is of type JSON:

id | field_id | content
1  | 1        | {"text":"Foo"}
2  | 2        | [{"0":"Cell #1", "1":"Cell #2"}, {"0":"Cell #1", "1":"Cell #2"}]

However, I've found that storing it as JSON in a MySQL database complicates things a bit - especially if I want to query and transform the data.

So I am wondering - what other options do I have here? I can't quite figure out the correct setup for this. If the output was simply a string, I could juse store each transformed string in a new row.

My concerns:

  1. The content of a field can either be a string or array. In my current setup, I store this as JSON but ultimately with two different structures.
  2. When sending my webhook, I am interested in sending all the records for a specific field by default. However, my users may (de)select certain records not to be included in the webhook (for example they can deselect the name field).

Because of this, I am not able to see how I can create the most optimal database structure that allows me to save field content, allowing me to transform it and re-save it - as well as allowing for strings and arrays?

  • A JSON column is handy for storing miscellaneous data and data structures. But, for searching, you should consider columns, JOINs, etc. – Rick James Jul 11 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.