All this talk about DataBases - but how to select?

[J. Landman Gay]

Peter Alcibiades wrote:

> Here is what I don't get though about Richard's approach.  Obviously it works, 
> but I can't see how to do it.  Two cases, maybe will beg for help on the 
> second one later.  Case one is a data set of about 3000 records, each with 
> about 40 fields. Some of the fields are a few lines of text.
> 
> There seem to be three ways of doing this:  
> 
> (1)  put the lot into one field on one card with the records one per line and 
> the values separated by tabs   This is Richard's approach, yes?  Except that 
> sometimes he puts them in custom properties also.

Or you could store it in a text file and just read that in. In any case, 
it's all the same approach; store the data as a single text variable. 
With this method, you use offset() or lineoffset() to find the record(s) 
you want, and use a display card to load in each record as needed. The 
display card  would have the same number of fields as record items (40 
in your case) and parse out the record data to fill each field 
appropriately. You need to write your own navigation commands with this 
method, re-filling the fields with the next or previous line of data on 
demand. When the user changes cards, you need a closeCard handler that 
gathers the field data, inserts appropriate item delimiters, and writes 
it back to the correct line in the text variable.

This is the method I used for a 40,000 record data set. The data was 
stored in a text file on disk and the application was only a single card 
that displayed the current record. This has the advantage of keeping the 
data separate from the interface, and the client only needed to backup 
the text file.

> 
> (2) put each record in one field on a separate card, ie 3000 cards each with 
> one field on.  This is Jacque's?

No, I use (3) usually. I don't see much advantage to this approach, 
except that the "mark" and "find" commands would bring you to the 
appropriate card easily. But you'd still need to parse the record into a 
display card if you wanted a nice display and easy user editing. You 
would also still need to collect modified data and write it back to your 
storage field. And if you don't want the user to see the data storage 
field, you'd have to do some lockscreen juggling to bypass the raw 
display and show the data on the user-friendly card.

> 
> (3) put each record on one card, and have one field per value, or 3000 cards 
> and 40 fields on each.  This seemed to be the approach that Richard was 
> criticising as being too much overhead? 

This is what I do if I am going to use a stack-based database. It has 
some overhead, but with 3,000 records I don't think that would be a 
problem. Make sure you use a group with background behavior when 
creating cards; that will keep the overhead to a minimum. This approach 
allows the find and mark commands to work well, and editing is automatic 
because each of the 40 fields can be editable as-is. You don't have to 
write any navigation or display scripts and everything "just works."

The disadvantage is that the data and interface are all together, and if 
you ever make changes to the stack and redistribute it to others, you 
will need to create export scripts to move the data out of the old stack 
and import scripts to move it back into the new stack. If the stack is 
only for your own use, then you won't have to do that, of course, 
because you'll probably just rescript your original. I have a lot of 
personal databases written this way and they work fine, but I'm the only 
one using them.

> Find and Mark will evidently let you produce a scrollable set of cards very 
> nicely in case (3)

Right, though note that to navigate marked cards, you need to use "go 
next marked card" rather than plain "go next". And if you want a list of 
all marked cards, you'll have to loop through them and grab the IDs or 
names (i.e., "repeat with x = 1 to the number of marked cds").

I think for a small data set like you describe, method 3 is the easiest 
and most versatile. It will allow you to do the most with the least 
scripting.

-- 
Jacqueline Landman Gay         |     jacque at hyperactivesw.com
HyperActive Software           |     http://www.hyperactivesw.com