Proposal: Use JSON Sql Server features for Acumatica attributes?

  • 9 April 2022
  • 0 replies
  • 23 views

Userlevel 5
Badge +1

SQL Server 2016 introduced support for JSON objects as field values. As Microsoft explains, this allows the mixing of SQL and No-SQL methodologies in a single database:

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

It occurs to me that Acumatica might experiment with using these features to store attribute data. The biggest advantage of JSON data structures is the ability to store multi-level data structures in a single value. For example, let’s say we wanted to describe all the possible values of multiselect attribute in Acumatica. With JSON, this is a single object, and a single request… i.e. {colors:  [“blue”, “black”, “red”, “brown”] }. Getting more complex, let’s say we want to describe an inventory item class with widths and lengths that are unknown at the time of the creation of stock item. We can store the object as

{

ItemClassID: “MYITEM”,

 Dimensions: {

   Length: 0.00,

   Width: 0.00,

   Height: 0.00

 }

}, and then when we create stock items, they will use this schema to ask for the appropriate values.

This would dramatically increase the power of the system, while also potentially making requests faster by reducing the need for multi-way joins in certain contexts. I haven’t really used these features much in SQL Server, so I don’t know about query performance. But I think it would be interesting to play with this idea. It could also potentially increase the power of the xRP platform dramatically as well, especially with the prevalence of JSON for inter-system information exchange these days (way easier to integrate third-party API’s with native support for JSON Object consumption and use in the platform).

 


0 replies

Be the first to reply!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved