Learn to be humble from a JSON

A few of our web apps uses a primary databases to build scenarios with budgets using classic relational tables with multiple levels to define a budget with several variable budget items with categories and subcategories, the challenge to migrate of all this relational structure to use a single JSONB to represent the same data from creation to interpretation of our profit maps changed our view to use and consume the data to simplify it and speed up our process and UI with a custom single JSON document structure.

Transcript

1. Learn to be humble from a JSON JoseLuis Torres
2. JoseLuis Torres CS BS Made in Mexico imported To Iowa
3. Agsolver
5. Profit Zone Manager
6. Profit Zone Manager Our promise Our flagship product PZM enables farmers and their consultants to review Profit and ROI across an operation for an annual subscription fee of $2,000. If we cannot help you find at least $5,000 of misallocated working capital we will refund your money.
7. Disclaimers • Not every complex structure can be replaced with a JSONB column • You need to consider all scenarios to insert/update and query the data inside the JSONB • There’s no easy way to set a foreign Key inside a JSONB column • Check other databases before making any migration or changes
8. Lets Start
9. Context Multiple Projects Multiple DBs Multiple Companies Insert here Startups Logos
10. Context DB designs we have used: • Normalized tables • Circular references • Constraints • Foreign Keys • Indexes • Stored Procedures • Triggers • Materialized Views
11. Context • DBs with thousands of tables • Composite indexes • Multiple users with multiple access levels • Multiple apps consuming the same DB DB designs
12. Context DB designs • Business Intelligence • Multiple vendors • Queries with 1 million users • Tables with 90+ columns • External DB operations • Geospatial columns • Key Performance Indicators • Analytics
13. Comprende?
14. Our Team • A group of experts with many different backgrounds • PhDs in Mechanical Engineering • Electrical Engineers • Masters in Agronomics • Developers
15. What was the problem? The performance of the web app was slow in multiple places or not as fast as expected. There were a couple of new features coming that would further slow down the development process and also the time to display budgets in the UI.
16. Our Challenge The DB need to be updated to be able to handle a new feature where standard budgets from different States will be able to be imported to the scenarios.
17. Where do we start? Print Your ERD
18. PZM ERD 2015
19. Budgets id name budgetable_type state_id Budget_items id name budgetable_type state_id unit_id budget_id Items id name children range_max range_min sort type_item parent_id Units id name A zoom to the tables
20. Budget_items id name budgetable_type state_id unit_id budget_id Items id name children range_max range_min sort type_item parent_id Units id name Budgets id name budgetable_type state_id budget_json The Tables XX X
21. WHAT?
22. But what did we need to do?
23. The Budget JSON [ {"name":"Combine","value": 29.90,"category":"Operating","subcategory":"Equipment"}, {"name":"Commodity Price","value": 4.50,"category":null,"subcategory":null}, {"name":"Custom Hire","value":null,"category":"Operating","subcategory":"Custom Hire"}, {"name":"Drying","value": 39.93,"category":"Operating","subcategory":"Grain Drying/ Handling/Hauling"}, …]
24. The Budget JSON
25. Set the strategy for the migration
26. Mapping/integration • Map existing records from budget_item, items, units to migrate them to the new budget_json column. • Able to update the individual base budget items and sub-budget items using the new JSON. • Read from a static DB standard Crop/States based budgets to import them to scenarios. • Able to export any budget as a template for a user to use later.
27. • When Mapping is done, we created scripts to start migrating the data • Controllers and service classes are updated to handle the changes to use the JSON instead of multiple records attached to a single budget • UI changes to handle the transformation of a JSON to match the existing presentation.
30. What else? • Bug fixes while building the new code • Rake tasks to migrate the existing data to the new format • Run multiple tests with different budgets across multiple enterprises • Integration of the new Budget with ReactJS on the front-end
31. Is that it? • No. We had to be able to generate the same JSON document that was created based on the already deleted tables to be able to generate reports from our backend workers. • Several new attributes will include sub categories, unit ids and other foreign keys to represent the budget in the best possible way.
32. Enterprise Statistics
33. Enterprise Stats • Enterprises • Farms • Fields • Scenarios • Budgets • Zones • ZoneBudgets
34. Conclusion • The migration was a success • Budgets were able to be imported • Budgets were able to be manipulated like before • The UI for the modal budgets was now blazing fast • The bottleneck of slow performance in the app was moved to a different place • We migrated the budget HTML modal form to React/Redux in the same step.
35. Thank You •@joseluis_torres •joseluistorres Don’t forget to fill out evaluations: prairiecode.io/evals