|HCS Consulting Group.|
|We make Complex Systems simple|
By Albert D. Kallal
Monday, December 10, 2001
Imagine for minute that you know almost nothing about database design. However, you have a screen that has customer information plus a place for *one* phone number. 20+ years ago, one phone number might have been ok. Now, lets assume that you need to have two phone numbers In a MV (Multi Valued) database, we simply *decide* that the phone number field is going to have more than one phone number. In fact, most editors in the MV world will simply allow you to start adding additional phone numbers. More amazing is that these additional phone numbers will appear as one column in the database (we don't have to even define an additional field).
The benefits of this:
- We did not have to create another table to hold the additional phone numbers (as we *should* in a traditional sql database system create a new table)
- We did not have to setup a relation between the two tables (since we did not have create another table).
- We did not have to add a new index....not for the relation (which is not needed)..nor for searching.
- We did not have specify any changes to our data dictionary (our fields definition database). In other words, we simply have a phone number field, and we just decide to allow more than one entry in that field. A mV database lets you do this.
- We do not have to re-write existing code that reads/writes the record structure. In a sql system, if we add another table, then any code that updates, or modifies the customer info must be extensively modified to deal with the additional phone number(s).
- Since mv systems are driven by a data dictionary, then modifications such as adding new fields can actually be done when the running live applications.....that means you can add new fields...*while* users are updating and running software. Do I need to say anything about reducing down time?
- Even more amazing is that most of the reports etc will handle this data change *without* any re-coding. Reports and query's will simply show the additional phone number(s) right below the first.
Any query, or condition that searches for the phone number will match for *either* of the phone numbers that belong to that customer. In other words, we do not even have to modify our *existing* search routines (this is really amazing!). In SQL, this simple little phone search becomes quite complex, as we are now talking a join, and working with two tables. Thus, our original select in pick would be un-changed. You would have the following:
list tblCustomer CustomerName Phone with Phone = "780 446 9137"
Note that pick has it's own query language. Many of the mv based systems are able to re-map map this data to a sql schema. (thus, if you use sql to work a multi-valued db, then the above in fact does become two tables).
In sql, our origianl select would look like:
select custID,CustomerName, Phone from tblCustomers where Phone = "780 446 9137"
Now, assuming for the above sql that we allow more than one phone number, and thus add another table we get
SELECT custId, CustomerName, Phone FROM tblCustomers
INNER JOIN tblPhoneNums ON tblCustomrs.custId = tblPhoneNum.custId
WHERE Phone ="780 446 9137";
Gee..that sure was a bit of hoops. Not only that, but all the existing reports and screens will have to be re-designed just to add a stupid phone number. We have not even mentioned code that manipulates the record...it too will need to be updated. In the mV system, none of the above problems occur.
One could argue that in the SQL example, we *could* just simply add another field called phone2. This is true, but then all our reports etc. will still have to be modified. Even worse is any search for a phone number now must operate on two different fields. And perhaps even more important, is that anyone who uses the data will have to be informed about this change data structure change.
The Pick database system is certainly one of the top computing inventions of the industry.
* p-code engine (portable cross platform code interpreter just like Java).
* Loose data typing. All data in the database is stored as a ASCII string. Just like xml!
* Demand paged virtual memory system.
* Everything in system is a database. Even a dir. command is actually a data query. Microsoft is talking about doing this for the next version of windows.
* Did I forget to say the system is 30 years old?