The combining of JSON & SQL is mostly inconsequential here. It is just a convenience for sending things in 1 file vs multiple.
The big takeaway is that SQL is a really great tool for exposing any sort of customizable logic over arbitrary datsets.
The only major caveat with this - You will typically have to perform some degree of transformation over source data in order to achieve a form of normalization well-suited to the writing of business logic (SQL). Assumptions (i.e. denormalizations beyond 3NF [0]) made in your schema here will inevitably hamper or kill the ability of the business to write flexible queries which can satisfy real-world needs. Today, a customer may have multiple accounts. In a few years, maybe we decide it goes the other way too. If you made an assumption, such as nesting these complex types in any way whatsoever, you are probably stuck with a steaming bag of shit that you now have to refactor and retest top-to-bottom. Using relation types to decouple nested complex types is the core tenant in my mind. Identity is foundation, but you can fudge your way around that a little bit.
This one caveat is why a lot of developers look at this idea as a bad one at face-value. It takes a lot of work & iterations with the business stakeholders to get this correct. You can't sit in a silo and expect to completely figure out the abstract nature of business types or learn how they might be related and in what ways. There really is a "correct" and "incorrect" way to go about it if you are being properly academic. Worry about webscale and performance later. Get it correct first. Nothing is too complex for a SQL schema.
If you can satisfy the academic gods of normalization and achieve a stable schema, then you are in for a wonderful ride. Between using views to compose higher-order functions, and UDFs [1] to wire into customized SQL functions, you can satisfy literally any degree of complexity required by the business. The only limit is your ability and discipline around modeling the domain types & relations, and willingness to get your hands dirty with some views and functions. You will find your business experts loving the power they gain by being able to write queries in a domain specific language they inherently understand. Giving them higher-order functions (views) and iterating on that side of the fence is yet another gigantic value play that is invisible if you are looking anywhere other than SQL.
bob1029|4 years ago
The big takeaway is that SQL is a really great tool for exposing any sort of customizable logic over arbitrary datsets.
The only major caveat with this - You will typically have to perform some degree of transformation over source data in order to achieve a form of normalization well-suited to the writing of business logic (SQL). Assumptions (i.e. denormalizations beyond 3NF [0]) made in your schema here will inevitably hamper or kill the ability of the business to write flexible queries which can satisfy real-world needs. Today, a customer may have multiple accounts. In a few years, maybe we decide it goes the other way too. If you made an assumption, such as nesting these complex types in any way whatsoever, you are probably stuck with a steaming bag of shit that you now have to refactor and retest top-to-bottom. Using relation types to decouple nested complex types is the core tenant in my mind. Identity is foundation, but you can fudge your way around that a little bit.
This one caveat is why a lot of developers look at this idea as a bad one at face-value. It takes a lot of work & iterations with the business stakeholders to get this correct. You can't sit in a silo and expect to completely figure out the abstract nature of business types or learn how they might be related and in what ways. There really is a "correct" and "incorrect" way to go about it if you are being properly academic. Worry about webscale and performance later. Get it correct first. Nothing is too complex for a SQL schema.
If you can satisfy the academic gods of normalization and achieve a stable schema, then you are in for a wonderful ride. Between using views to compose higher-order functions, and UDFs [1] to wire into customized SQL functions, you can satisfy literally any degree of complexity required by the business. The only limit is your ability and discipline around modeling the domain types & relations, and willingness to get your hands dirty with some views and functions. You will find your business experts loving the power they gain by being able to write queries in a domain specific language they inherently understand. Giving them higher-order functions (views) and iterating on that side of the fence is yet another gigantic value play that is invisible if you are looking anywhere other than SQL.
idolaspecus|4 years ago