3

SchemafreeSQL

 2 years ago
source link: https://schemafreesql.com/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SchemafreeSQL

Problem:
Storing, Querying and Modifying Disparate Data Structures is difficult to do efficiently.

Example problem:
We want to store products with vastly different attribute sets from one another into the same product database. We want users to be able to query across all available product attributes (e.g. CPU's CPU by speed, Dog Toys Dog Toy by breed size). We will continually be adding new products and types, each with a combination of common attributes (e.g. price) and uncommon attributes (e.g. flavor). We want to efficiently 'reference' certain attributes (e.g. 'Brand') as Objects, because otherwise we'd have duplicate 'Brand' attributes (e.g. Brand 'Support Address', 'Phone', etc.) embedded in-line with their parent attributes across our data store. We want to efficiently 'reference' products as objects in order to include them within queries that reference them (e.g. sales reports by various product attributes) And of course we want queries to run at indexed speeds regardless of what product attributes are searched on.

Did we mention that we want all of this as pain free as possible?



We considered using:

A SQL DB

A SQL DB with a JSON Column Type

A Document Store like Mongo

We're happy to say that we developed a solution - that met all the needs mentioned in the above example - by using optimized statically-structured SQL tables & indexes over an HTTP JSON API. We are hoping that other's find it useful as well.

Clicking on actions below will display HTTP Request & Response Payloads to give you an idea of how we added, modified and queried the data within the example problem.

Insert Data Query Data
Explanation
  • Attributes of the 1st product were set
  • The Nerf Dog 'brand' was automatically added as an object
NOTES:
JSON Request Payload
[ { "modify": { "data": { "product": { "id": "ball_a", "name": "Nerf Dog 2.5in Rubber Sonic Ball", "price": 7.08, "breed_recommendation": "S", "flavor": "none", "brand": { "id": "b-nerf", "name": "Nerf Dog" } } } } } ]
JSON Response Payload
[ { "success" : 1, "cmdname" : "modify", "result.oid" : "1" } ]

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK