Re: Dreaming About Redesigning SQL

On Fri, 2003-10-17 at 22:52, Christopher Browne wrote:
> Nobody seems to have been prepared to explain the MV model in adequate
> theoretical terms as to allow the gentle readers to compare the theory
> behind it with the other theories out there.

I'm not convinced that there was a great deal of theory behind Dick
Pick's database design. It has always struck me as very pragmatic.

In terms of storage, the substantial difference between MV and
relational databases is that each MV file (=table) holds, or can hold,
what would be the result of a join in a relational database.
Wherever we use arrays in PostgreSQL, we effectively do the same thing.

The advantages of MV are that it is very simple to program and to define
your data. If you want another attribute (=column) you simply define a
new dictionary entry with a new attribute number; data output formatting
can be simply done by defining new dictionary items which present the
same data in a different way. The MV characteristic makes it very fast
to get (for example) a list of invoices for a particular customer, since
the list of invoice numbers can be kept as part of the customer record.

The disadvantages (at least of original PICK) are: there are no
constraints (not even by typecasting); there can be no relational
enquiries -- everything must be defined in the dictionary; the
environment is utterly undisciplined -- anything can be changed at will
with a simple text editor; even more than in MySQL, all data validation
must be done by programming; there is no requirement for a record in a
file to correspond at all to the structure defined in its dictionary;
finally, the security model was laughable.

The effects of this can be seen in many places whose applications are
based on PICK. There is usually a mass of programs of various ages,
with no certainty that all have the same view of the database
structure. The database design is often very amateurish; frequently it
truly is amateur, because the simplicity of programming makes it easy
for users to roll their own systems, but they usually lack the necessary
experience in data analysis. Security usually depends on user
ignorance; in UniVerse migrations of old PICK databases, I have often
seen entire directories of important data with 777 permissions, and with
everyone using the same login.

Good use of MV requires the imposition of disciplined programming in an
environment which is profoundly hostile to such discipline. It is not
really possible to guarantee data integrity.

There are some advances on this in some implementations. I know
UniVerse: it provides SQL and adds it on top of the existing MV
structure; it also provides transactions. These features give some of
the advantages of a relational database, provided that only SQL
facilities are used, but I doubt if many people have used UniVerse to
build SQL systems from scratch. I feel that SQL was provided more to
satisfy the box tickers who compare tenders than with a serious
intention of providing data integrity.

Having used both SQL and MV, I would not now design in any MV
implementation known to me a system whose data I valued.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Delight thyself also in the LORD; and he shall give
thee the desires of thine heart." Psalms 37:4