Sub-values vs. multi-valued fields?

[dave....@gmail.com]

Hi!

I am in the process of designing an application to learn more about MV systems. I have been able to build multi-valued fields and use associations, etc. I have been looking at sub-values and trying to understand how, when and why they would be used. From what I get of it, they are values attached to a field and if the field is multi-valued each entry could have sub-values. I also got the impression you could have up to 9 levels of sub-values (a sub-value of a sub-value?). I think my application could benefit from sub-values but am not sure that I understand them correctly. Currently the value I would store as a sub-value I am storing in the next field.

Any hints, tips, examples, reasons when and when not to use sub-values would be greatly appreciated.

I am sorry if my explanation is poor, but I am trying to learn. :)

Thanks again for everyone's help and patience!

Dave Venus

[Martin Phillips]

Hi Dave,

> I have been looking at sub-values and trying to understand how,
> when and why they would be used.

A simple example...

Imagine I have a very simple order processing system in which the data I
record for each order is the date, the customer, the products and
quantities. The product and quantity fields will be multi-valued and
associated.

So far, so good. Now imagine that every time I sell something, I need to
record the serial numbers of the items. This new field will be multi-valued
by product, each value containing the serial numbers as sub-values.

> I also got the impression you could have up to 9 levels of sub-values
> (a sub-value of a sub-value?).

No. The original Pick definition of the data model has fields (attributes),
value, and sub-values. There are two other mark characters defined; the item
(record) mark that is used in real Pick systems to separate records in some
way internally but is almost unused in other systems, and the text mark that
is typically used to break long text fields into short parts of line
wrapping, etc.

UniVerse defined four more mark characters to allow sub-sub-values,
sub-sub-sub-values, sub-sub-sub-sub-values and sub-sub-sub-sub-sub-values
but I have never seen this used.

We do have an application that we produced for a client that effectively
uses text marks to create sub-sub-values but this level of separation of the
data is rare - probably largely because you need to think in five
dimensions!!! There is, of course, no reason why you cannot go on breaking
the data down with your own delimiters in any way that fits your needs but
the QMBasic language will not have anything better than the FIELD() function
to tear it apart.

Martin Phillips
Ladybridge Systems Ltd
17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB
+44-(0)1604-709200

[ash.c...@gmail.com]

Hi Dave,

You could be given a long rambling history of why sub-values (and even
sub-subvalues) were invented. I'm not going to do that as it has
little real value.

What you need to know is that they were a creation of the early days
of multivalue, which has not been fully carried through. Most of the
data manipulation tools do not fully support these low-level
delimiters. For instance, the QUERY processor only properly handles
Fields and values; It does not go lower. The BASIC lanaguage provides
some support, but it's sporadic. There is a command to read an
individual value (READV), but no command to read a subvalue.

In short, do your best to stick to the normal 3 dimensions. Use
multivalues with a clear conscience, but keep clear of sub-values or
lower. This is equivalent to the Oracle nested tables and VARRAYS if
that helps you to visualise things.

I've seen many systems, and the ones that have sub-values were all horrible.

Hope that helps, and is not to disagreeable to the Pick old-hands out
there (Tony). :-)



2009/1/16 <dave....@gmail.com>:

--
Ashley Chapman

[Martin Phillips]

Hi Ashley,

> For instance, the QUERY processor only properly handles
> Fields and values; It does not go lower.

The QM query processor does try to get the subvalues properly paired up. I
haven't looked at this recently but I think that we do a better job than
some of our competitors.

> There is a command to read an individual value (READV), but no
> command to read a subvalue.

In many ways, I really wish that READV did not exist. Without understanding
that the file system works at the record level and hence READV actually
reads the whole record, extracts the item and discards the rest, it is very
tempting for a programmer who wants two fields from a big record to think
that using two READVs will be the best way to do it. WRITEV is even worse!

[ash.c...@gmail.com]

Martin,

2009/1/16 Martin Phillips <martinp...@ladybridge.com>:

>
> Hi Ashley,
>
>> For instance, the QUERY processor only properly handles
>> Fields and values; It does not go lower.
>
> The QM query processor does try to get the subvalues properly paired up. I
> haven't looked at this recently but I think that we do a better job than
> some of our competitors.

Perhaps I should have used "fully" instead of "properly" in my
statement above. As far as I know, no multivalue implementation can
do an exploding sort on sub-values, but all can do it on values. That
sort of thing.

I bet that somebody chimes in to say that Jbase, D3 or whatever can do it! ;-)

>
>> There is a command to read an individual value (READV), but no
>> command to read a subvalue.
>
> In many ways, I really wish that READV did not exist. Without understanding
> that the file system works at the record level and hence READV actually
> reads the whole record, extracts the item and discards the rest, it is very
> tempting for a programmer who wants two fields from a big record to think
> that using two READVs will be the best way to do it. WRITEV is even worse!

Yep, it's pretty useless. I just picked it as an an xample of how the
system support falls away as you go deeper down the delimiters list.

In general would you agree that it's good practice to try to keep the
database within the standard 3 dimensions?

>
>
> Martin Phillips
> Ladybridge Systems Ltd
> 17b Coldstream Lane, Hardingstone, Northampton, NN4 6DB
> +44-(0)1604-709200
>
>
> >
>

--
Ashley Chapman

[Martin Phillips]

Hi Ashley,

> As far as I know, no multivalue implementation can do an
> exploding sort on sub-values, but all can do it on values.

Dare I say it..... QM does. (Though a quick experiment shows something
slightly odd going on with a single valued I-type in the query that I tried.
We will investigate this).

[Martin Phillips]

Hi again Ashley,

The "something odd" turned out to be a fault in a dictionary item that used
SUM() where SUMMATION() would be needed if there were both value and
subvalue marks in the item to be summed.

So, QM does do exploded sorts on subvalued data.

[mdsi2000]

Dave Venus,

My rule of thumb for the last 22 years is to use multivalve, CHAR
(253), and stay very far away from sub values, CHAR(252). This rule
applies to new file specs. Two reasons to stay away from subvalues.
First, it could get pretty ugly if the file layout needs to be
modified in the future. Secondly, when it comes time to export the
file to Excel or a flat file using a program wizard like (Accuterm/
Wintergrate/others...) IT WILL be almost impossible, unless you write
a utility to flatten the file.

The only times I have used subvalues is on existing files. You do not
want to rearrange the file structure, but you need to add new logic.

Peter G.

[Tony G]

Commentary follows, worth even less than the 2 devaluated cents
that you didn't pay for it. ;)

> From: ashley.chapman
> As far as I know, no multivalue implementation can
> do an exploding sort on sub-values, but all can do it on
values. That
> sort of thing.
>
> I bet that somebody chimes in to say that Jbase, D3 or
> whatever can do it! ;-)

To my knowledge, D3 AQL is completely oblivious to subvalues.

> Martin wrote:
> > In many ways, I really wish that READV did not exist.
> > Without understanding that the file system works at
> > the record level and hence READV actually reads the
> > whole record, extracts the item and discards the rest,
> > it is very tempting for a programmer who wants two
> > fields from a big record to think that using two
> > READVs will be the best way to do it. WRITEV is even
> > worse!
>
> Yep, it's pretty useless. I just picked it as an an xample of
how the
> system support falls away as you go deeper down the delimiters
list.

The rule of thumb is to use READV for one atb but READ for any
more. I haven't seen the assembler code in D3 for READV but I
have to believe that it's been optimized to scan frames (SIDD
mnemonic?) and to retrieve only the attribute required. A full
GetItem operation followed by a parse would be an obvious waste
of resources. I would hope other MV products are similarly
optimized.

> In general would you agree that it's good practice to try to
keep the
> database within the standard 3 dimensions?

MV people know this but Dave may not: For item keys which
can't/shouldn't include system delimiters, almost all
applications make use of alphanumerics as delimiters. Examples:
ORDER*SEQUENCE
GLACCT_MONTH_TRANSID
TRANSCODE~TIMESTAMP
Apps also include these structures in multi-valued data:

001 CUSTID
002 DATE]DATE
003 TRANSID~TIME]TRANSID~TIME

That structure should have TIME as a separate dependent
attribute, or use subvalues instead of the tilde, but when
structures get more complex there's absolutely nothing wrong with
getting REC<3,2> and then using FIELD or OCONV functions to parse
out the desired sub sub sub parameter.

Most MV developers do stick to system delimiters as much as
possible but if you need to go deeper, just pick a delimiter that
doesn't clash with data and make sure the app understands the
structure.

In general, subvalues are usually used for data that will not be
queried by the command processor. As mentioned for D3 above,
this is because the query processor most MV variants does not
understand subvalues, and it's a bit frustrating to store data
elegantly for BASIC but not be able to query it in a report. QM
does not have this limitation. YMMV but for cross-platform
support I still don't use subvalued data for queries. However,
subvalues work well for many other purposes, including
"internal-only structures" and for data that needs to be used
with a GUI and you want to provide metadata to external clients
on a per-field basis. Again, YMMV.

HTH

Tony Gravagno
Nebula Research and Development
TG@ remove.pleaseNebula-RnD.com
Nebula R&D sells mv.NET and other Pick/MultiValue products
worldwide, and provides related development and training services

New version of NebulaXLite now available for QM.
Makes extensive use of sub-values :)
Generate rich, attractive spreadsheets from BASIC.
remove.pleaseNebula-RnD.com/products/xlite.htm

[eppick77]

I do want to mention that Unidata does a fairly good job with
subvalues and their query language. I was very surprised when I saw
it at one site being used that way.

Eugene

[Ross Ferris]

On Jan 16, 8:12 pm, "Martin Phillips" <martinphill...@ladybridge.com>
wrote:

> > I also got the impression you could have up to 9 levels of sub-values
> > (a sub-value of a sub-value?).

You CAN, but as noted, support out of the base environment gets "weak"
beyond "normal" multi-values, so you end up either having to write
your own for this, or find a tool where someone has already done the
bhard yards

>
> UniVerse defined four more mark characters to allow sub-sub-values,
> sub-sub-sub-values, sub-sub-sub-sub-values and sub-sub-sub-sub-sub-values
> but I have never seen this used.

FWIW, we do. Our standard process definition is made up of potentially
many pages (MV's). Each page can have a table of actions that can be
processed (SMV's). We also enable you to "trap" specific functions
(aka "subroutines") that are run by the system (SSMV's), and further
trap specific values for parameters being passed to these routines
(SSSM'V's - [eg: trap change of value for a specific database element,
regardless of how/where changed)

Anyway, once you have your "trap" in place, you may then call 1 or
more functions (subroutines - SSSSMV's), and also pass values to the
paramaters of the subroutine (SSSSSMV's) - may sound "messy", but in a
point & click environment you don't think about it. Obviously this is
a structure that doesn't necessarily "print well" with standard tools,
yet "strangely enough" if you throw this out as XML and point a
browser at it, it "understands" & displays well

I know that there are other ways we could have done this, but it
really isn't as bad as having to think in 5 or 6 dimensions .... once
you get your head around "nesting" (or embedded tables as we tell the
SQL people) as a concept, the the number of levels shouldn't present
you with a problem.

YMMV, but if you are going to limit yourself to "just multi-values",
why not just use flat SQL tables?

[Dawn Wolthuis]

On Sat, Jan 17, 2009 at 5:15 AM, Ross Ferris <ro...@stamina.com.au> wrote:
<snip>

> YMMV, but if you are going to limit yourself to "just multi-values",
> why not just use flat SQL tables?

I limit myself to "just multi-values", not sub-values, because "lists"
are a very, very powerful data type. If we are going to add to the
simplicity of a single value in a "slot" then it makes sense to choose
one or more powerful data types that really do make software
development easier, more maintainable, etc.

Adding lists (multivalues) can definitely make things easier,
particularly if you compare the implementation of an ordered list
(some would say that is redundant, but we know it isn't) in MV to an
ordered list in an RDBMS. If we have a wide list -- require an arity
to our list that is > 1 -- then we could make another table, but
associations can make it easy enough to have a nested table or nested
"wide list" easy enough.

If we add subvalues to that mix, it might add simplicity, but can also
add unnecessary complexity. At that point it it is likely, but not
necessarily, easier for the long haul to split out a table.

Lists? Yes. All other types of structures, including associations and
sub-values? Maybe, but choose carefully or you can dig yourself into a
more complex, rather than simpler, solution. I give some associations
a thumbs up, but to date have never chosen to use sub-values. cheers!
--dawn
--
Dawn M. Wolthuis

Take and give some delight today

[ash.c...@gmail.com]

Ross,

>
> YMMV, but if you are going to limit yourself to "just multi-values",
> why not just use flat SQL tables?
>

Because "flat" SQL tables don't support multivalues! Well, some do in
the form of VARRAY or nested tables, but the query language support is
very painful. QM and other multivalue products support multivalues
very well, but not so well for sub-values.

--
Ashley Chapman