HCS Consulting Group.
We make Complex Systems simple


Why Pick.....continued

Communication of design, and structure is one of the largest challenges any developer, and/or IT department faces. The whole idea explained in the Mythical Man Month was that 12 developers X 1 month is *much* less then 1 developer x 12 months. This is due to the issue of increased communication *between* people (but hey, those meetings are fun!). While the concept of communication in the Mythical Man Month was referring to communication between developers, this general concept can be applied to all people in a organization. The problem of communication exists for any kind of procedure...be it software or otherwise. Hence, when one has to change the software, the company procedures, reports, and training on how to retrieve a phone number also will require a change. This *change* does not occur when data is in a mV model.

Anyone with a perfect design would have started out with two tables in SQL to take care of the phone number problem. My point here is that SQL takes more skill, and better planning from the start. Too bad we can't afford the best designer who does a perfect job the fist time around. Too bad business have to change!...as next year we will need 5, or 6 phone numbers!

In the mV system, you can just start adding additional phones numbers to that field...and away you go. The user who makes this decision does not need a huge amount of training in relational data design, nor even have to care about it. That is the beauty of a multi-valued system. It models the real world with ease. I want another phone number.....ok..done!

Picture of Macdonald Hotel taken from a Sea Do ride on the Saskatchewan river (C) Albert D. Kallal I had the challenge and pleasure of converting an application from a pick mV structure to a SQL/vb application. When you have some time, you can read more about this mv to SQL project . One of my *major* conclusions in this conversion project was that the SQL design is *much* less forgiving, and thus requires far more skill is required to get the *initial* design correct. Why? When you change the design in SQL, it is far more likely that the code, and reports, and screens that edit the data will have to be changed. This is not the case in a mv database.

Modifications over time represents a significant cost of an application. Since a mv database can change with less consequences, then it is obvious that the cost of maintenance is going to be less.


Simple questions are simple.

In a mv database, a single record can represent a whole invoice. In traditional environments, this is not possible and the only way to represent a invoice to create a invoice object (assuming you are using a language that lets you define and create objects). I found this out the hard way when I tried to convert a mv application to sql/vb. It was only when I started to create class objects could I approach anything near the flexibility that was common in the mv environment. In a mv environment it is common to pass around records from subroutine to subroutine. In a traditional system, the only way to do this is to create an object. In fact, the average mv developer does not think of a invoice as a object, but only as some record to play with.

There are also user interface ramifications when you have a structure that can represent a table. If the user makes a whole bunch of changes while editing the invoice and then decides to "cancel" or undo, you simply don't write the record back to disk. In a SQL system, this is huge pain. You either have to load up a screen with the data from *several* tables. Allow edits, then write back to the file, or wrap the whole screen in a transaction (assuming your SQL engine has that feature). What is a dead simple single record in mv becomes a real dance in a flat file system. By the way...just how do you un-do your changes to a invoice? Anyone....since it sure is pain for me!

Creating "objects" in a traditional system does let one manipulate the invoice via code quite well. You can pass it from routine to routine, and you can even add methods to the object. While these objects do enable one to bring together a complex data structure, this complex object structure does NOT work with the SQL query language. Thus, you get some gain with objects...but these gains in modeling data only occurs in code. The reason for this is that the invoice in a traditional system can only be represented by *several* tables. In mv, this data is in general in one record. By the way...xml also allows this type of table...

Here is a simple query example of what I mean.

Lets assume we have a car lot. We also have several car sales people. I simply want to know which sales man sold a blue, and a red car last month. In fact, lets keep this question real simple as I don't want to scare you about SQL anymore than one should. So, lets drop the month idea...and lets just ask what sales man sold a blue and a red car.

In mv land we will have a table called CarsSold. In that file it will contain a list of cars sold. This file may, or may not have a relational link to the sales rep file (the sales reps may actually be part of the file cars sold table). At any rate the syntax in a mv system to ask this simple question is

list tblCarsSold SalesRep Make Model Color with Color = "Red" and with Color = "Blue"

I mean, how simple can you get? The above returns the fields SalesRep, Make, Model and Color. Now, lets try the same thing in SQL. Nine out of ten times when I ask people how this is done in SQL, the user gets it wrong. In SQL, we would join the sales man table to the cars sold table. The approach is as follows:

select SalesRep, Make, Model, Color from tblSalesReps
inner join tblCarsSold on salesrepID = salesrepID
where color = "red" or color = "blue"

The above of course is wrong, since the above returns any sales rep who sold a red OR a blue car. I am looking for a sales rep who sold both a red AND sold a blue car (note, all cars in this example are ONE color. So, we are NOT looking for a two color car here! A single red car, and a single blue car). This type of question comes up all the time. The same problem exists if we ask who sold cars in each of the last two months. Or it could simply be what customers bought something in each of the last two years. This type of question is one that requires multiple conditions on a *related* table, but somehow must be grouped via a particular parent record. SQL is terrible at this type of query.

The solution in SQL is to select a set of data for red cars, and then test that set of data against people with blue cars (yup...we are talking set theory here). The solution is not very pretty. While there are number of ways to do this...below is the general approach. You really need to use a nested select. If you come up with something substantially better...please email it to me!

select SalesRep, Make, Model, Color from tblSalesReps
inner join tblCarsSold on salesrepID = salesrepID
where color = "red"
and salesrepID in (select salesrepId from tblCarsSold where color = "blue")

Of course if we need 3 conditions....then things start to get totally out of hand.

I in no way want anyone to think that I don't like SQL. In fact SQL ranks near the top in my in personal favorite list. The reason for this is that I first learned to use SQL with FoxPro about 10 years ago. It is the ONLY thing in computers that I still use 10 years later. In other words...SQL is a true data standard very much like HTML. Every new database system that comes out will use, and support SQL. I have used SQL on a ton of platforms, and languages. I see no chance of this changing. Learning SQL is a long term investment in education. If you want to learn something useful learn SQL. Learn it now, and you will still be using it 20 years later.

Notes on mv-basic.

The programming language in mv is mv-basic. While the mv-basic programming language is not extensible like vb is, it has a number of things going for it.

As we continue the march towards splitting data out of the application, the idea of a end of file becomes totally outdated. In other words, we request a list of records...read the list till exhausted and we are done. We really don't know, or even care where those records came from...let alone if they actually have anything to do with the idea that we reached some end of file. While much of the modern new languages that pull data from a server are moving away from the idea of end of file, the mv system is 30 years old. Way back then the idea of data processing was to read punched cards to the end of file mark. As systems transformed from cards to tape, and to *active* databases we have moved farther and farther away from the idea of eof. This system had this idea correct, and that was in a punched card era.
This has always been a pet peeve of mine. If you open a file, most languages generate some type of trappable error. To me, when a file open fails, that should not generate a trappable error. The system should certainly have a means for informing one that the file does not exist...but a trappable error?...that is crazy!. Trying to open a file that does not exist does make a error in my book. No less than then a search for name is not a error...but simply that the name does not exist. The elegant approach that mv uses is to wrap all open commands in a nice then/else structure.
The same goes for reading a record. Again, in pick when a read for a non existing record occurs, it is the ELSE clause of the command that executes. All records are written by a string key id of YOUR choosing. There is NO auto number scheme built in. Whatever id you use, this becomes a primary key.


The processing speed of a mv system is really that of how fast it can process strings. In the 1980's many mv systems vendors had custom built micro code processors that would execute pick code directly. Ultimate computers was one well known vendor that built boards that you would shove into DEC and Honeywell machines that worked on this principal. General Automation also played around with a what they called a Vulture board. However, by the 1990's the speed of the Intel chip was closing the gap between custom built "string" processors and a standard of the shelf chip. Soon, there was no need for these custom built solutions as the standard Intel PC chip became *much* faster.

The concepts and ideas contained in the design of multi-valued systems really was a future vision of what a ideal data system should be. Anyone who has experienced a mv database first hand will at first react with very low impression of the system. As one begins to use, and understand a mv system, one gets the sense of how simple, and yet how incredible powerful such a system is. Anything that so is simple and yet so powerful for sure marks the sign of a genius at work.

Some current popular mv vendors are:

Pick systems (now raining data) with their "d3" line of data products. They are the original mv company that licensed their technology to many vendors. www.picksys.com

IBM has a line of mv products http://www-3.ibm.com/software/data/u2/

jBase A windows/Linux mv database clone that compiles Pick code into C++ www.jbase.com

Pick newsgroup: comp.databases.pick

Newsgroups Faq: http://www.jes.com/cdp/cdp_faqx.html

You can contact me at:

Albert D. Kallal    kallal@msn.com