In this meeting, the State Changers discuss the best way to handle inventory balances in an ERP solution. They consider two options: using queries to fetch inventory calculations from a sales table and a purchases table, or flattening out the data into a separate table that includes product information and transaction details.
The participants agree that it is important to keep the line items associated with sales in their own table, as there is a difference in the jobs of recording inventory and tracking sales. They also discuss the performance implications of both approaches, noting that querying the sales and purchases tables may be inefficient and slow, especially for a multi-tenant platform. To resolve this performance issue, the suggestion is made to create a separate product table that includes the current inventory numbers. This way, the inventory calculations can be done by querying this one table instead of going through the entire sales and purchases tables. The participants also emphasize the importance of updating the product table regularly to reflect any changes in inventory. Lastly, there is a discussion about logging and the creation of log tables. The State Changers mention that Xano, the platform they are using, automatically creates endpoints that allow external access to the data in the log table. However, it is advised not to enable these endpoints as it gives unauthorized access to the data. It is suggested to uncheck the box that allows external access and control the access to the data through function stacks instead. Overall, the meeting provides insights into the best practices for handling inventory balances in an ERP solution and highlights the importance of considering performance and access control when designing the system.
(Source: Office Hours 8/30/2023 )
