database questions

[Alex Rice]

On Friday, August 1, 2003, at 10:17  AM, John R. Brauer wrote:

> b) is there an internal way of doing this instead of one of the above 
> databases?

John, your requirements sound very similar to my current Rev project. 
It's a Facility Calculator for the National Park Service. It is a 
lengthy survey about facility usage and planning. There are several 
facility types, and each type has it's own substack with dozens of 
cards. So each "record", we call record a "project title", literally 
does have several hundred fields. There is no apparent way to break it 
up into some kind of normalized SQL table structure that would seem 
more organized.

The requirements were that no networked database server was available, 
so mysql and postgresql were not options (otherwise I would recommend 
postgresql because it has foreign keys, triggers, and transactions).

For a project like this, and maybe yours, a SQL database just doesn't 
make much sense. The fields in the survey change a *lot* as the app is 
developed. There are many, many fields for each record. We are 
developing and updating the app while users continue to use old data 
from previous projects they have entered. So it has to degrade 
gracefully as fields are added and removed, whole cards changed, etc.

The solution I am using, thanks to this list, is to use Rev custom 
properties in a "data stack". Custom properties can be attached to a 
stack or any other Rev object. I create a file called UserData.rev and 
save it into the user's Application Data folder. The custom properties 
are accessed as an array using a key/value relationship. Custom 
Property SETS are used to separate the data of the various facility 
types I mentioned.

Here are a couple of accessors for getting and setting data into the 
data stack:

--
-- dataField(key)
-- accessor for the value of the slot in the current session
--
function dataField pKey
   put the customProperties of stack kDataStack into tData
   put sessionID() , pKey , "value" into tFilter
   return tData[tFilter];
end dataField
--
-- setDataField key, value
-- accessor for setting data
--
on setDataField pKey, pValue
   put the customProperties of stack kDataStack into tData
   put sessionID() , pKey , "value" into tFilter
   put pValue into tData[tFilter]
   set the customProperties of stack kDataStack to tData
end setDataField

I have also written some handlers to automatically load and save 
UserData.rev to and from fields and buttons as the user navigates 
through the cards. I used custom properties to do that as well: The 
routines scan all fields and buttons on the card picking out ones that 
have the custom property "ud" (stands for user data), which is the 
"key" in the above handlers.

In this method the number of custom property array elements is = 
(facilities) * (projects) * (answers) -- in other words (sets) * 
(sessions) * (data keys). At the sets level they are completely 
separate arrays (custom property sets). But at the sessions * data keys 
level, it's just a single array. It's extremely fast until you get up 
into many thousands of array keys. However I haven't needed to do much 
optimization yet though. I suspect that dataField(key) function above 
could be written better.

So using a "data stack" is fast, portable and doesn't require any 
additional software. It's much easier programming than say, a delimited 
text database, and it's not as difficult as a SQL implementation.

I also recommend looking at Serendipity DB. I don't think it uses the 
approach I described above, but it is a native Rev implementation also 
and would have similar benefits.

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
http://ARCplanning.com