I took my personal finance tracking spreadsheet and reorganized it into a set of normalized database tables each in a CSV (comma-separated values) file. They imported into Budibase fine but I could not define declare logical relationships between them. Looking for another entry point for Budibase built-in database, I found JSON was another supported format so I had my Python code output my spreadsheet data as JSON. Each entry looks something like this:

{
  "Date": "12/18/2016",
  "Vendor": "Target",
  "Total": 41.24,
  "Items": [
    {
      "Price": 26.27, 
      "Category": "Food"
    },
    {
      "Price": 14.97,
      "Category": "Durable"
    }
  ]
}

This is no longer a set of normalized database tables. It's closer to how things were laid out in my spreadsheet. A date, a vendor name, and a total that may be divided across multiple items each in a different category. Except each category had a column in my spreadsheet and now it is a nested array in my JSON.

I don't like this layout as much as my normalized tables. The vendor name ("Target") is repeated over and over in this JSON format, instead of split off into a dedicated vendor table and referenced via ID. This is vulnerable to data entry errors like a misspelling, which could be found all over my original spreadsheet. The nested item categories are likewise repeated instead of sitting in their own table of categories.

Despite my dislike, I was willing to give it a shot. This data schema is vaguely familiar from the time I briefly played with MongoDB, another NoSQL database. Now I built this table with my Python processing code to see what CouchDB (what Budibase built-in database is based upon) can do with this data. The answer: CouchDB can handle this just fine, but building interactions for this data is too far off the beaten Budibase path.

To Budibase's credit, it was very easy to build UI to interact with all the top-level items: date, vendor, and total. But then I ran into a brick wall with those nested Items remaining opaque. I could only get my list of two items to show up as:

Items: [ (object), (object) ]

Which is not very useful at all! Searching around Budibase GitHub discussion for help, I found #3394 How to create form to accept an array of items which pointed to #2568 JSON (Key / Value) type. It is possible to define a schema for JSON data that includes nested arrays. However, there's a catch: this option is only available for JSON data tables newly created within Budibase. This is not available for existing data that was imported. Darn! This is probably on Budibase to-do list for the future, but today I will have to abandon JSON approach and try something else.