Home -> Usenet -> comp.databases.theory -> MV and SQL

MV and SQL

From: David Cressey <dcressey_at_verizon.net>
Date: Sun, 15 Jan 2006 14:19:14 GMT
Message-ID: <CRsyf.881$Ym3.637_at_trndny09> 





DonR responded to me, a while back with this:



> My guess is the proprietary MV program can be written quicker and with

> finer details than a combination of database constraints and SQL

> whatif's. I think the problem you and other SQL programmers have in


> understanding MV is that you are trapped in the SQL box. ;-)






I didn't want to respond to this, because I was more interested in what Don
had to say about Pick than I was in arguing about what box I might or might
not be in.  But now, maybe, it's time to bring this up.



I can't speak for others, but I can say, for sure, that I am not trapped in
any SQL box.  Before I learned SQL, I had gained some proficiency in
assembler, Fortran, BASIC, Lisp, Algol, Pascal, and Datatrieve.  I've used
SQL to good advantage in a certain class of data storage and access
problems, but I have by no means forgotten everything that I had learned in
the quarter century that preceded my involvement with SQL.  I have used SQL
because it is useful and not because it is pristine and pure.



The description of Pick that DonR has given is extremely useful, but it
skirts one aspect of the MV data model (if I can call it that without
starting a whole different argument).  I'm referring to the multivalue
feature.  If you take away the multivalue feature,  The pick model looks
very much like dozens of other prerelational models, and differs only in
trivial details.




Rapid access to a record in a file by means of a key is a "good thing"
(tm), but it's hardly unique to Pick.


Structuring records as hierarchies of values, with field separators to
indicate the tree structure is, in some contexts, a "good thing" (tm), but
it's hardly unique to Pick. Using indexes to tie together data stored in
different record types is a "good thing" (tm), but it's hardly unique to
Pick.  Even the people who are "trapped inside the SQL box" know how to use
foreign keys, and generally know how to use indexes, if only by relying on a
good optimizer.



What seems to make the MV model unique is the "multivalue" feature.  BFaux
alluded to that in a post where he shows some queries that illustrate how
handy it is to use this feature.  But the examples he shows are only
trivially different from examples that could be constructed using two design
methods commonly used by SQL people in the construction of application
systems.  (IMO, they are bad designs for bad systems).



The first is the Entity-Attribute-Value  (EAV) model.  In this model a
single table is used to store a three dimensional relation,  one of entity,
attribute, and value.  It's trivial to extend this model to cover
multivalues.  The EAV model has been much discussed in this newsgroup,  and
I think it's been demonstrated to be a "bad thing"  (tm).



The second design is the "one true code table"  (OTCT) design,  in which new
types of codes can be stored on the fly, without having to do any DDL.  That
merely moves a data management function from being reflected in the database
metadata, to being reflected in an off-line data dictionary, or to being
handled informally by programmers.  This has also been much discussed in
this newsgroup, and it think it has also been demonstrated to be a "bad
thing" (tm).



Without having written software in Pick or that family myself, I have only a
hazy idea of what MV consists of, gathered from what people have written in
here.



As near as I can make out, it consists of deferring a certain disambiguation
from file definition time, or data update time, to retrieval time.  I'm
talking about the disambiguation between a simple data item, and a set (or
list, if you prefer) that consists of a single data item.  Semantically,
they are not the same thing, and this can matter, in some circumstances,
although I can't think of one off the top of my head.



Now this deferred disambiguation can be construed as a form of "late
binding",  and late binding is, arguably, a "good thing"  (tm).  But there
are plenty of circumstances where the early binding of a relatively formal
data model to the SQL DDL execution time has been, in my experience a "good
thing", precisely because of the discipline it imposes.



This is the area where SQL and MV fundamentally diverge, IMO.  I'd like to
see more about MV, how the Pick system (and, perhaps, the rest of the
family)  exploits the MV feature.  Whether my guess of MV as being a case of
late binding is or is not correct.   And, in particular, I'd like to see how
Pick people avoid ceratin pitfalls that some other people,  including but
not limited to SQL programmers, avoid by living with early binding.
Received on Sun Jan 15 2006 - 15:19:14 CET












Original text of this message