Thanks both for the advise.
In this app, the user is only updating one record at a time. So I really have two choices:
- Have a large table with all my data (e.g., 100 columns of data)
or
- Have 1 parent table, then 9 additional tables linked by foreign key, all tables with 10 columns
In both scenarios, I have 100 columns of data (-ish, I realize we have added the keys in there too). If I post updates to two columns, will the operation be faster for option 1, option 2, or will they be the same?
I ran a quick test
Option 1:
Option 2:
Other than the response size, these appear to be similar performing queries.
If that was the case, it would seem to make sense to do this:
- Have one large table that stores all data (Option 1)
- Have 10 separate "get" queries - one for each form in my app. That query acts as the data source for each form.
- Have one "post" query - it searches for and only posts changes, then I rerun the applicable "get" query from that form (I already have a transformer that checks to see if the form is "dirty" by comparing form.data to form.initialData - I could just as easily filter out the matches and if length > 0 then dirty.
When I re-open the app and jump to the form I was last on, I can trigger the get query for that form, then trigger the other queries once the first one loads so that the data is then ready for use.
Does that strategy make sense? I really appreciate the help and advice here as I'm really new to this. I'm willing to explore other database solutions, but right now I'm in MVP stage and want to just get a well performing tool for UAT.