Pick, JSON and database design

[Peter McMurray]

It appears to me that N dimensional is more a position of view than actual fact. Here is my view of database design that I arrived at after listening to Dick and rereading GIRLS several times.

Useful Data is stored in an array/vector of related objects. Defining applications using these objects needs two views, top down and bottom up.

Pick and JSON use a system of stacked Arrays whereas DB2 has a more complex methodology - yes, DB2 has a JSON subset. The Pick VME is a set of stacked arrays. I will happily expand if people wish.

The approach I recommend is the Pyramid. An overall picture then segmentation until a single building block is discovered.

Pyramid - layers may be any shape but the final object is a block of material with shape and position.

Alpha Taxonomy - a myriad of options but the final object is an identifiable animal. 

Library of Congress - Reading material but the final object can be a book.

Peoples of the World - Many Countries, Continents, Races but the final object is a person.

IBM as always has a great description of the issue when dealing with Applications using tools:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.json.doc/doc/c0061319.html

"JSON documents consist of fields, which are name-value pair objects. The fields can be in any order, and be nested or arranged in arrays.

There is no enforcement of document structures. Other documents in the same collection might therefore have a subset of these fields, extra fields, or different representations of the same field."

The major issue with JSON documents is the same issue that exists with Pick files. An application requires structure. This structure can be provided by careful design of a system SCHEMA.

IBM have several methods of dealing with this as is demonstrated by the cost and size of their excellent Product.

However the cost and simplicity of Pick is a major benefit to companies provided the application designers understand how Pick Nested Arrays work. A Pick Array is unlimited in length and can contain any type of data. The particular ARRAY ROW an Object is stored in is calculated from an algorithm on the Object key. The Object contains ROWS of unlimited length known as ATTRIBUTES denoted by count.

The VME is a single Object consisting of an unlimited set of sets of arrays that can spread across multiple volumes and multiple machines. The particular power of this is that the system does not need indices to locate the objects. JSON achieves this at a significant cost and loss of structure if not controlled by a SCHEMA.

Pick achieves this by having an hierarchical array of sparse arrays all of known row size and each row contains an unlimited number of objects of any type. Each object is itself an array of arrays of unlimited length.  

AN APPLICATION SCHEMA is the control that the programmer must supply.

One designs objects that are contained in files. The SCHEMA contains an object that defines the files of an application. Each File Definition is a list of the ATTRIBUTE names that are described in a file that contains the definition such as type, length and files that it exists in. The Application is then built using these definitions that effectively sit above ACCOUNTS in Pick Terminology.

The Pyramid block object is defined by attribues for size, type position in the pyramid. The layers of the pyramid are described as sets of blocks and so on.

The platypus is a monotreme with a family genus and class plus many other possible collection descriptions. Each collection is described in an application file and referenced in an attribute.

The Object for Monotreme would have 5 values in an attribute describing animals. 4 types of Echidna and the unique platypus.

The Library would have a file of Objects know as Books identified by the ISBN number. Each object would have an attribute Author that might have several values that refer to individual Author entries. Another attribute might have type such as Romantic Novel. Another method of production - ebook, hard back,soft back in separate values.

The point is that the number of files is unlimited and the number of objects is unlimited and the number of values is unlimited.

It all comes down to presentation of the schema.

A view that I had not even thought of until it was in the news today is a database of books read in a place and period. What where Queensland miners reading in 1900 for example. All easily added to a library datebase that include attributes for where the books where held and who borrowed them at that place. Source ABC podcast June 18 2019.

[chandru murthi]

I'm not at all sure where you're going with this, Peter. As others have pointed out, the number of elements in an array is irrelevant to its dimensionality. So agreed that at each level, you can have unlimited instances, but the number of levels in Pick (formally) is limited, it is not "n".

You start with the top level and use accounts and files as the start of dimensionality, which itself is not what I'd use, but let's keep that. Beyond that, there are items and 3 formal levels - attributes, mvs and sub-mvs. That's all from a programming/formal viewpoint, as the language can directly deal with these elements. U2 and others "define" two or three other delimiters formally @TEXT (imsr) etc., but there's no syntax other that FIELD(FIELD(FIELD(... ))) to extract, for example, the sixth (or 9th in your hierarchy) level of data.

If you use char(250) etc, why stop there? You can use any of the extended characters to store data in, yes, "n" dimensions, but at what cost and aggravation? ... since there's no equivalent of <a,v,s> to reference these levels?

Chandru