A database for Christmas

Copyright Dr Alan Solomon (1986-1995)

In this series of four articles, I'm going to explain about the
practical problems of setting up a relational database to run a
business, from initial specification to operational use.

I'm going to assume that you've never set up anything more complicated
than a Christmas card list on PC File, and only vaguely know what
"relational" means.  And I'm going to show you how a major exercise
like this, can be done in four days, if you use the right short-cuts.
I know, because I did it - well, nearly in four days.


Part one, in which the database is specified and designed

First, let me describe the business for which the database was
written.  It is a small mail-order company with a handful of people;
to preserve anonymity I'll call them Sparkling Marsupials Ltd, and
I've also made changes to the names of their products.

Sparkling sell a range of goods via direct advertising;  there are
about 200 products in their catalogue (Sparkling call these the
Bits-and-Bobs).  One of the things that they sell, the Aardvark
Cleaning Kit can be added to, so they offer an add-on service, that
you can subscribe to year by year, and subscribers must be kept track
of, so that they can be mailed the new brushes and lotions for
cleaning your Aardvark.  Because they are such Aardvark enthusiasts,
they have also found that their monthly training sessions of care and
feeding of Aardvarks are of interest to their customer base, and they
have also just started publishing a newsletter called "Aardvark
World".  An in-house vet offers a specialist service for sick
marsupials of all kinds.  Sometimes, experts from Sparkling get called
out to advise companies on purchase and care of Aardvarks, Tarsiers
and related pets.

So you can see that the business is fairly diverse, but the different
parts of it are related.  Until now, Sparkling have used PC File to
keep track of customers, so they can mail old customers with
information about new products and services, but they have been having
problems.  Because they have lots of data to keep track of, they
upgraded to PC-File:dB about a year ago, so that they could use it on
a LAN, and have two people accessing the Bits-and-Bobs database at
once.  But PC File seems to occasionally truncate its .HDB file;
easily replaced, but worrying.  Also, indices seem to need replacing
regularly, and they've been getting occasional freezes when
multi-using.

Other databases have been added as they were needed, as it is so easy
to set up a new database in PC File - the Aardvark Cleaning Kit
registrations, the subscriptions to Aardvark World, and the other
parts of the business all have their own databases.  Things came to a
head recently, when Sparkling wanted to mail all the people who'd
bought the Aardvark Cleaning Kit but not registered for upgrades, to
remind them that this might be useful.  They found that, because the
original sales were on a different database from the registrations,
they couldn't do it without spending a lot of time going through it by
hand.  It was also clear that similar mailouts were impossible, so
they couldn't say who had been on an Aardvark Training course but
didn't subscribe to Aardvark World, and so on.

<>

That was the last straw, and Sparkling decided that all the parts of
the business should share a common database, which would obviously
have to be multi-user as several people would be using it at once.
And I was the one who would do it, since I already knew all about what
they did, being a bit of an Aardvark fancier myself.

I had advised them on the Lan, which was a token ring, using a Mission
386-20 as the fileserver, running OS/2 Lan Manager.  The network
interface cards were a mixture of IBM, Madge, Proteon and Western
Digital, and the workstation Lan software was a mixture of IBM PC Lan
1.2, and Madge's equivalent.  The Lan worked reliably (token ring is a
good system) even with this diversity of hardware and software.

First, I tried to find out why PC File was losing headers and indices.
The usual reason for that sort of thing is inadequate locking, so that
two users are writing to the same part of the same file.  But the PC
File manual said absolutely nothing about Lan operation except to put
/LAN in the command line;  there was no information about what the
program actually does to lock things, and no way you could change it.  I
gather that there is an upgrade (Sparkling used version 1.1) which may
fix this problem.

PC File was chosen in the first place, not just because it is
shareware, but because it is easy for users to use.  That meant that I
could design the data structure and set up a data entry form, design a
few reports (invoices, monthly sales summary), and then leave the
users to use the raw facilities of PC File, without having to do lots
of programming to make using it easy.  If it is quick for me to set
up, that makes it cheap;  the hundreds of pounds that a database like
dBase or Clipper costs is dwarfed by the cost of the programming
effort needed to build an application.

So, first I needed to choose a database product to build on.  I was
looking for something that would be very easy for the enduser to use
the raw product, so that I wouldn't have to wrap it up in menus and do
lots of coding.  I also wanted it to be relational.

The word "relational" has come to be as misused as "user friendly",
and it is a brave database that doesn't claim to be relational.  Let
me explain the main points of relationality.

In a database, you don't want to store the same data in two different
places.  If you have a customer's name and address stored once, then
if you store it a second time, then that:

1.  Wastes space, sometimes a lot of space.

2.  Wastes time in rekeying it.

3.  Causes big problems when the address changes, because you have to
find each instance of that customer and update it.

4.  Worst of all, the second time you key in the customer, you'll
enter it slightly differently, and if you do a mail out, you'll send
that customer two letters.  And if you try to target your mail out to
all the people who have bought the Aardvark Cleaning Kit but not
registered for upgrades, you won't be able to, because you won't be
able to tell that the two apparently different customers (because on
lives on Sloth St and the other on Slothe Street) are actually the
same, so you'll send him a letter suggesting that he buys something he
already has, and he'll think he's dropped off the registration list,
and you'll get a worried phone call.

So what you want to do, is give each new customer a unique number, and
against his number, you store his name and address in a separate
table.  When you set up a table to store all your orders, you store
his number instead of his name and address.  When you print out the
invoice by running down the table of orders, you look up each customer
number in the customer table to find the address details.

Whenever you get an order, you look up the customer's name and
company, to see if he is already on your database;  if he is, you use
his existing customer number.

That is the main point of a relational database.  Data is divided up
into tables, and the tables are related.  In the example above the
orders table and the customer table are related by the customer
number, in a many-one relation (many orders per one customer).
Different products use different words to describe the same thing.
Tables are also called files (in dBase-speak) or forms (by Dataease).

An important feature of the design of any good data application, is to
try to avoid large records.  Large records are the enemy of
performance.  If your records are 10k long, then every time you access
a 20-byte company name, you drag in 9.98k of unwanted information.
This means many unnecessary disk accesses, and fewer records held at
any time in memory.  The slogan to use in the design of databases is
"Lotsa little records" (this is Jim Hackett's phrase).  There is an
incomprehensible jargon way of saying the same thing, which sounds
much more impressive, but I can never remember it.

A flat file database makes this impossible - you have to put
everything you might possibly need into the record.  Many of the
fields will be blank most of the time;  for example, if you need to
record up to twelve similar items in the record, you'll need those
fields repeated twelve times.  In a relational database, you can break
these huge records up.  In particular, a group of fields that repeats
N times, can be a separate table, with a column in that table that
relates the items to their "parent" record.

The first task was to choose the product that I was going to use for
this project.  There are dozens of databases on the market, and I
didn't look at every possibility.  I used PC Plus as one source of
information, and also spoke to friends.  In this way, I drew up a
short list, which consisted of dBase (and the compatible products),
Dataease and Paradox.

dBase was ruled out, because it looked as if a considerable amount of
programming would be needed to produce the sort of end-result I was
after.  Paradox was ruled out because Borland refuse to disclose the
file format, and I would feel uncomfortable using a database, knowing
that if anything went wrong and the data recovery utilities provided
didn't fix it, there would be no way to dive in and sort things out by
hand.  Dataease International don't give the file format in the
manual, but did send it when requested.  Furthermore, it seemed to
offer the necessary ease of use to the end-user, without needing to be
wrapped up in elaborate programs.

I was also impressed by the technical support staff at Dataease, and
with the speed with which it is possible to get through to them.
Borland, on the other hand, at one point farmed out support for
Paradox to third parties, which leaves one slightly concerned even now
(and they've permanently farmed Basic and Prolog back to the people
who wrote it).  And since Dataease won the PC User Show Database
Challenge, the product must be sufficiently powerful and flexible.  So
on the basis of necessarily incomplete information, I chose Dataease.

The next thing to think about was the data to be stored and the
relationships between them.  The obvious unit to think about is the
invoice.  Businesses revolve around the invoice;  they record
everything about a transaction - what was ordered, how many, price,
customer and so on.  The invoice is the thing that you actually get
paid for;  sometimes it seems as if the goods or services are almost
incidental.  Indeed, a common scam is to send out an order form that
looks just like an invoice, in the hope that it will automatically be
paid.

The invoice divides into several portions.  There is stuff that is
about the customer - name and address, for example.  There is stuff
that is particular to that order - the customer's order number, the
discount, the net amount, the VAT.  There is the detail of each thing
that is being invoiced for - description, quantity and cost, and there
may be additional messages to the customer, or comments (not for his
eyes) that you want to attach.

That gave me three tables;  the invoice, the customer and the invoice
item tables.  The relationships were many-one for invoice to customer,
one-many for invoice to invoice item.  In addition, for some of the
products, extra information would be needed that is particular just to
that product;  for example, for the Aardvark Cleaning Kit, it is
necessary to know whether the customer has long-haired or short-haired
Aardvarks (or even both).  So the initial design called for additional
tables for those products needing extra information, such as the date
of expiry of the subscription to Aardvark World.

Figure 1

Order entry table
-----------------

Invoice number              indexed
Customer number             indexed
Customer's order number
Item 1
Item 2
.
.
.
Total
Discount
P&P
Vat
Net
Paid
Outstanding
Method of payment
Date of invoice
Expiry date (for Aardvark World)
Aardvark hair length (short/long)

Customer table
--------------


Customer number         indexed
Title
Initials
Name                    indexed
Company                 indexed
Address1
Address2
Address3
Address4
County, Postcode
Country
Phone
Fax

Accounts address (pretty much as above)


Invoice item table
------------------

Invoice number          indexed
Item code
Item description (virtual; i.e., not stored here)
Item price
Quantity
Cost

The data that would be stored in Invoices, Customers and Invoice Items
is shown in figure 1.  In the Invoice Items table, you can see that
each product has a product code, and the idea is that you just need to
type in that short code, and the database will find the description
for itself.  But there is no point in storing the description in that
table, as you would be storing the same information again and again.

Products table
--------------

Item code                 indexed
Item description
Unit price
Royalties
Royalty receiver


So I decided to have a Products table, with the product code,
description and unit cost.  And since some of the products are
produced under licence, it is necessary to pay a royalty to the
inventor.  In the past, this has always caused a lot of work for
Sparkling, as the royalties have to be worked out each month from the
invoices, and the old database needed a lot of manual help to do it.
I decided to put the licence fee in the Product table, and the
inventor's name.  I didn't want his other details, though, as some
inventors have patented several of Sparkling's products - remember the
principle of storing data in only one place?  So I needed an Inventor
table, with the inventor's name and address, plus a field for how much
he was owed (some inventors don't get their royalties each month,
because for obscure items like the Spiny Anteater Carry Case, there
are only a few orders each month, so the royalties are allowed to
mount up until they reach a sum worth paying.

Inventor's table
----------------

Title
Firstname
Lastname
Address1
Address2
Address3
Address4
County, Postcode

After writing all this down, I looked at what I had.  The tables for
the separate products were now rather small;  for example, Aardvark
World only had expiry date in the table.  I decided that it wasn't
worth having a separate table for them, and so added a few fields to
the Invoice table for this information.  The idea was that the users
wouldn't bother filling in those bits that weren't applicable, and
that they would use common sense to do that.

What should be indexed?  That needs thought.  Some people index
everything in sight, but that can actually slow things down a lot, as
every time a record is added, all the relevant indices must be
updated.  Also, unused indices take up memory space that could be used
for a larger number of useful ones.

Indices make searching a large database very much faster.  It's like
the difference between having an A-Z of London without the street
finder, and having it with.  It is indexed by street name, and a
computer uses an index in much the same way as you use the street
finder.

The Invoice table would be searching the Customer table on Customer
number, so I decided to index Customer number.  Also, users would be
searching the customer table on Surname and Company name, so those two
needed indexing.  In the Invoice Item table, it was clear that the
Invoice Number field would be used for searching by the Invoice table,
so that was indexed.  The last index used, was for the product codes
in the Product table, as they would be searched for each Invoice Item.

Next, I made a brief list of the reports that would be needed.  The
first was the invoice, of course.  Also labels for the packages,
delivery notes, and a daily printout called Sales Day Book, which
looks just like the paper system that Sparkling used donkeys years
ago, when Aardvarks were just starting to become fashionable.  This
provides comfort to Sparkling's MD, who doesn't completely trust
computers (quite rightly) and nourishment for Sparkling's accountants,
who don't use computers at all.  It also would give the monthly VAT
return, and a summary of it would give the MD useful information about
how well each line was doing.  Aardvark World would need a monthly
printout of labels for current subscribers, and the Cleaning Kit would
occasionally need a run of letters to accompany the latest additions,
together with instructions to the packer on whether the Aardvark was
long haired or short haired.  Each of these reports would need to be
run on either a range of dates, or on a range of invoice numbers.

So, the database has its design.  At this point, and not before, it is
time to switch on the computer.  The alternative approach is that you
don't make a plan, but just sit down at a computer and go to it.  This
is called "hurling yourself at the keyboard", and it isn't a good
idea.  Also notice, that at this point, we haven't worried about what
facilities are and are not available in Dataease.  I assumed that it
has everything that a database should have.

<>


Part 2, in which we have the initial skirmishes with Dataease

Did I mention that I was proposing to do this without ever having used
Dataease?  With a colossal act of faith, I made the heroic assumption
that it would give me all the facilities I needed.  Actually, that
wasn't too brave, because my design so far would work equally under
any relational database, so I wasn't committed yet.  However, at this
point, it was time to find out.

I installed Dataease 4.0 on an XT clone with 640 kb of memory and a 10
mb hard disk.  Installation was very straight forward, and I opted for
installing the tutorial as well.  There's nothing to beat learning how
to use your package before doing the serious work.  I went right
through the tutorial, typing in all the data, and working all the
examples.  I didn't do the DQL exercises at this stage.

Finally, it was time to code.  I started up Dataease, created a new
database, and started designing the Customer table.  To do this, you
paint the data entry form on the screen, which you do by positioning
the cursor and typing in text.  Data entry fields are created by
positioning the cursor at the start of the field, and hitting F10.
This brings up the form you fill in to design that field, and you can
choose the type of field (text, numeric string, numeric, date and so
on).  You choose the size of the field, and whether it is indexed.  In
used character fields for everything except phone and fax numbers;
unfortunately the predefined format for these is American, so I used
numeric strings.

<>

This turned out to be a mistake.  When I tried to use the database, I
found that it wanted to fill in the field with leading zeros, which I
wanted for customer number, but not for phone number.  I set up
Customer number as "unique";  this is important, because the invoices
would refer to customers by their number, and we'd better not have two
customers with the same number, or one will get the invoices and
statements intended for the other.

Next, I started on the most important table, Order Entry.  This starts
off with invoice number, which had better be unique (or the VAT man
will want to know why) and the customer number.  At this stage, you
can see the point of a relational database.  There would be only one
James Hopkirk on the database, and his address would only be stored in
one place, even though he has been steadily collecting Aardvarkiana
for years.  So disk storage space is conserved, and more importantly,
every time Sparkling mail their old customers to tell them about some
great new Aardvark-thing, Mr Hopkirk only gets the one letter.

<>

We had to tell Dataease about this relation;  this is done with
another Dataease form, in which you say that Customer Number in the
Customr table is related to Customer Number in the Order Entry table.
This is because in one of the tables, this field might have been given
some other name, and later on, you'll see that that happens.

We also put some duplicated data into the order form.  When I'm
filling in an order, I want some reassurance that it is going to the
right person, and a customer number doesn't give me that reassurance;
I want to see the name and company.  I could have made that what
Dataease calls a "virtual" field;  in other words, it isn't stored in
the table but is calculated or looked-up as required.  But for two
reasons, I made it a real field, stored in the Order Entry table.  The
first reason was security - if the whole thing fell apart except the
Order Entry table, I'd still know the name and company that each order
was going to.  The second was searching - if a field is virtual, you
can't search on it, and I wanted the user to be able to search for
customers already on the database while they were entering orders, by
putting the cursor onto the Surname field and pressing F3 (Dataease
calls this View, but it is also the key used for searching).

The next part was probably the only slightly tricky one.  I wanted an
invoice to be capable of handling a variable number of items.  One
ofthe limitations of PC File, had been that I had space for eight
items on the invoice, and usually most of those were empty (which
wastes space).  Worse, if an order needed to carry over onto two
invoices, there were all sorts of complications when the invoice(s)
were paid, plus the old mailing difficulty (see Mr Hopkins above).

Another problem I'd had before, was that people didn't always type the
product names in exactly as they should, which meant that I couldn't
use the information there as a guide to what was selling well.  Worse,
it meant that the royalties couldn't be done using that, but needed a
lot of laborious manual work.

Dataease has these nice features called Multiforms and Subforms (many
other databases have a similar feature).  That means you can define
sub-form in your main form, which can be a repeating item, exactly as
I wanted.  It took me some time fiddling aroung to get the neat boxes
the way I wanted them, but it gave me the following:

The invoice items are in a separate table, consisting of:  Invoice
number, product code, product name, price, quantity and total amount.
I made the product name a virtual field, because it was quite long,
but the rest were real.  Ass soon as the user types in the product
code, the product name is looked up, so that if a product name doesn't
pop up, they know that the code is wrong.  If you don't know the
product codes, you press Ctrl-F10, and up pops a pick list (you can
cursor up and down, and press enter when you've found the right one.
You don't have to pop up all 200 products either;  if you know it
starts with A, then you put A* into the product code box, and only the
As pop up.

I didn't have to do any coding to get this effect - it's part of
Dataease.  When I saw how that worked, I felt really good about my
choice of database.

Designing the rest of the invoice was very simple;  you subtract a
discount from the total, add P&P, 15% VAT (15% by default, but it can
be typed over, as there are a few exports), and show the payable sum,
amount paid (if any) and the amount owed.

Next, I designed the report that would print out and be the paper
invoice.  It was almost the same as the thing I've just described, but
I left out the things that the customer needn't see (like the customer
number) and included things like the delivery address, which was a
lookup in the Customer table.  At the bottom I put the usual text that
you have on invoices.  At this point, I felt that I'd probably done
the hardest part in the whole exercise, and I'd better check that it
worked.  So I ventured for the first time into DQL.

<< picture of me venturing into DQL>>

Part 3 - in which Dataease strikes back

If it sounds like it had been going very easily so far, that's because
it had.  But things now started looking darker.  When I went through
the tutorial, I hadn't done the stuff on DQL (Dataease Query
Language), as this was the language in which you program in Dataease,
and I'd hoped to avoid doing that.  Instead, I hoped to use "Quick
Reports" to do things.  But it turns out that "Quick Reports" is just
elementary DQL, so I decided to go through the tutorial.

It left me a bit confused.  Most of what I do is done with procedural
languages, in which you tell the computer what to do one at a time,
and to do lots of things, you write for-next loops and repeat-untils.
DQL is non-procedural;  you write code that operates on entire tables,
and produces sub-tables as a result.  There is no for-next or
repeat-until or do-while, let alone a GOTO.  This is a great idea, but
it means that you have to think differently when you program.

I needed to write a DQL that took data from the Invoice table, looked
up the relevant Customer information in the Customer table, and the
Invoice Items, and brought it all together to make an invoice
printout.  I started off by creating a new table (Form in
Dataease-speak) called Invoice Print, and I wrote DQL code to gather
information from all the other tables and post it into there.  I wrote
another DQL to print it out.  At this point, I found two very severe
problems.

<>

**FATAL STACK ERROR** cannot reload COMMAND

<>

The first very severe problem, was that if I did some DQLing in the
DQL editor, came out of that, saved it, and ran the DQL, very often
the machine would hang.  When you're developing code, you go round
that loop very often, and I found that the machine was crashing
several times per hour.  And this was the program that Sparkling
Marsupials was going to rely on for their administration?  Gulp.  I
tried using other computers, and other hard disks - no difference.
For example, I found that every now and then, Dataease said that it
couldn't read DEUTILTY.OVL, one of it's overlays, because of a disk
read error.  I offered the usual Abort, Retry, Ignore options, and the
only thing that worked was to abort out of Dataease.  But when out of
the package, I copied the file around the hard without any problems,
so it was obviously some sort of Dataease problem.

Another nice little habit it had was, if you tried to do something and
didn't have enough memory for it, Dataease would tell you so, give you
a message that I guess is the amount you're short of, and ask you to
press any key.  When you do, it dumps you back to the Dos prompt;
tough if you've been working on a DQL and not saved it.  This happened
at one point when I was working on a 512 kb machine, and if you think
that I was silly not to have the full 640 kb, remember that when I
made this system operational, it would be running over a network, and
a network can easily 128 kb away from you.

The other big problem I had, was that I simple couldn't get the
invoices to print out with the repeating Invoice Items.  I could make
it print one invoice per invoice item, and I could make it print just
the first invoice item each time in a run of invoices, but I couldn't
persuade it to print an invoice with several Invoice Items.  I
searched through the manuals (which are very well organised into one
for Users, one for people setting up a database, and one for DQLers),
and I found an example of what I wanted.  I typed the example in,
exactly as in the manual, and it still didn't work.

<>

This was my darkest hour.  I'd wasted three days learning Dataease,
and setting up what I'd got so far, and no way would it print out a
simple invoice, and the wretched thing crashed if you used it.  And I
hadn't even tried it multiuser yet, which is what had been the undoing
of PC File.  I switched off the computer, and went off to think
whether I should chuck it in now, or was I doing something silly?

My reasoning went like this.  Thousands, or possibly even hundreds of
thousands of people are using this product.  So it can't possibly be
as crashy as it seemed.  Secondly, the repeating invoice item print
was shown in the manual, so there had to be some way to do it.

I decided that the problem of crashes, dumps to C> when out of memory,
and occasional inability to read DEUTILTY.OVL would somehow be
resolved, since lots of people were using Dataease somehow, and what I
saw must therefore be solvable.  The best thing there, I though, was
to ask tech support at Sapphire about it, as they are very
knowledgable and helpful, and this could wait till after Christmas.
Did I mention that this was my Christmas project?

<>

After having a bit of a think, and my Christmas fish-and-chips (there
was a lot of flu around, and Susan really wasn't up to a turkey), I
returned to the fray.  This time, I reasoned that as I'd followed the
DQL example in the book quite faithfully, and yet couldn't get it to
print out repeating Invoice Items, the problem wasn't in the DQL.  I
had a look at the Format instead - this is a screen that tells the DQL
how to format the data that it has extracted.  After staring at it for
a while, I had an inspiration - I needed another .ITEMS command to
tell Dataease to list all the Invoice Items I put that in, it worked,
I did a little dance, and felt that I could see light at the end of
the tunnel.  As I went to bed that night, I could see my way to
getting this thing finished.  I could also see that I didn't need the
Invoice Print table at all;  it could be eliminated from the design,
and I could create the invoices on the fly by relating the necessary
Order Entry, Customer and Invoice Item tables.  As I lay my weary but
triumphant head down, I decided to do that tomorrow.

<>

Part four - in which that database is finished, installed and used

Once the invoice report was finished, the other reports were a doddle.
I did one for labels, one for VAT, management reports, delivery notes
and other useful summary reports.  I did a report for the mailout of
Aardvark Cleaning Kit upgrades, that even reminded the packer whether
the customer had a long or short-haired Aardvark, a label run for
subscribers to Aardvark World provided they were still paid-up, and a
suite of reminder letters to pay unpaid invoices, in an increasingly
sharp tone of voice.  There were about 20 reports altogether for
various purposes, with fairly descriptive names.

Dataease allows you to set up menus for users, and I thought that if
this made my system easier to use, it was a good idea.  You set up the
menus by choosing an option on the Dataease main menu, and to set up
the menu, you simply fill in a form that tells Dataease which
procedure (or whatever) to run for each option on the menu.  That
meant I could use very meaningful options, like "Enter a customer" or
"Enter an order".  You can also control who accesses which menus, so I
arranged it that each person logged in by typing their first and last
name, and the first menu they saw was the one that they were most
likely to want first.  So the order entry people saw a menu with
options to enter data and print invoices and labels.  The person
looking after the whole system, could add products, run off management
reports, Aardvark World labels and all the other stuff.

Next, I wanted to import some data.  I took a chunk of the old PC File
database, and told Dataease that it was a dBase file (PC File uses
that file format).  Dataease imported it like a dream, and I was ready
to install it on a Lan, using the multi-user version of Dataease.

I started up the Lan, and installed it on a local hard disk, as I felt
that I would rather Dataease did its overlaying without using the Lan.
I started it up, and it crashed.  I started it up again, and it
crashed.  I re-installed, started it up and it crashed.  It let me
type in my name and password, but when I had done that, and told it to
create a new database (so that I could import the one I'd written), it
said that it couldn't open the files.

I set up a network monitor so that I could see what was going on.  It
would get as far as opening a few files, then crash.  I tried varying
the number of available file handles, the number of available locks,
and lots of other things.  Nothing made any difference.  I began to
feel that perhaps my choice of Dataease, without checking that it
actually worked, was not wise.  Sick at heart, and wondering how
difficult it would be to convert to Paradox, I crawled home with my
tail between my legs.

<>

Next day was supposed to have been the day that the users started
using the new database;  I had written it over the Christmas/New Year
holiday (you enjoy yourself your way, I'll enjoy myself mine) so that
Sparkling could start using it on January 2nd.  But it didn't work,
and I was getting some rather accusatory stares.  I phoned up Sharon
at Dataease, and told her my tale of woe.  She asked me waht Lan
software I was using, and when I told her Lan Manager from Microsoft,
she said "Simple - you need the 4.0R maintenance release upgrade, its
a free upgrade".  Barely concealing my joy, I was round at their place
within minutes, and sure enough, when I installed this new version and
made the small patch that you make for Lan Manager, it worked.  Within
an hour, I had the system that I'd put together over the last few days
up and running.  Hurrah!

<>

After that, all the problems were solvable.  We discovered that the
Madge 16-bit token ring card works in very few computers;  Madge
thought that their new Smartcard would solve that, but we found that
it had the same problem.  It worked in an AST Premium 386, and in two
286es of uncertain origin, but not in a Walters 386, a Mission 386-20
and an ELT-386SX motherboard.  However, the Madge 8-bit cards seem to
work in everything - Madge say it is because their 16-bit card uses
bus mastering, and not everyone has implemented that properly.
However, in the course of talking to Madge about this problem, they
suggested that we try the new version of the software that comes with
the card, and we found that it worked beautifully, and even better
took up considerably less memory (Dataease needs as much as you can
give it).  Even more wonderful, the 16-bit Smartcard, which we
installed in the AST 386, had its own memory for the protocol stack,
and used very little Dos memory.  By the way, I really must recommend
the AST 386;  it really flies.

When we used PC File, the big database ran on a Walters 286, and the
smaller ones on turbo 8088 machines.  Dataease needs a faster machine,
because it uses more MIPS to do the same job as PC File.  So we
upgrade upgraded the people who did most of the data entry, giving one
the Mission 386, another a machine with the ELT-386SX, and using the
old Walters 286 for the person who did least on the database.  The
file server was upgraded to a Samsung 386 with 4 mb of memory.  This
didn't cost as much as you might think;  for example the ELT-386SX
motherboard only cost #430, and the AST was acquired second hand for a
song from a stockbroker who had stopped broking.

<>

At this point, I handed the whole thing over to the users.  It wasn't
that I thought that the job was finished, but that I thought that at
this point, it was usable, and could be refined after I got their
comments.  The first comment was an emergency request to make it
easier for them to search for existing customers.  There were some
5000 on the database, and it was very tedious to see whether this new
order was from an old customer.  First you checked the surname, and
waited for the search, and then the company, and if neither of them
were matched, you could type him in.  So I set up a DQL to search on a
customer's name OR company, and to enter a batch of orders, first they
would check each one to see if the customer was already there, and if
he was, they would write his number on the order.  Then, they would go
through the batch again, entering the customer data if he was new.

The users of the database soon started discovering features that I
hadn't realised were there;  features which made it even faster and
easier to enter data.  For example, they discovered Alt-F5, which lets
you search on any field, and some of the cunning things you can do
with the F10 key, that lets you search related tables.  For example,
with Alt-F10, you can display all the invoices that a particular
customer has had.  It was like they were in a sandbox, and they were
digging down, and they didn't reach the bottom.

<>

One problem they had, was that the key to save a record is F2, whereas
in PC File, it is F10.  On top of that, if you modify a record and
want to save the change, PC File also uses F10, but Dataease uses F8
for that - if you use F2, it creates a new record that is different
from the old one, but the old one is still there.

I made a few minor changes to the system as a result of their
suggestions;  for example, I had made the Title field a choice field,
so that it was forced to be Mr, Mrs and a couple of others.  But they
told me about Captains, Professors, Majors, Sergeants and so many
others that I changed it to a text field.  There were a few other
small changes like that.

Connecting the printers was a big hassle.  We needed three printers;
one laser for invoices, one dot matrix for labels and one dot matrix
for wide management reports.  The Samsung had two parallel and two
serial ports, and the wide carriage printer (#85 from Morgan Computer)
could do serial or parallel.  But after spending three hours with a
PC, the printer, a serial cable and a breakout box, I decided that
life was too short for messing about with serial printers, and I
installed both dot matrix printers on the same parallel port, using a
push switch from Comcen to toggle between them.

Conclusion

It mostly works.  The database doesn't crash as much as it did,
although I'm not convinced that going to the new release has entirely
fixed it;  I think it is because I don't do much DQLing any more.
When I do, I still find that I get crashes - a couple of times, after
I've done a DQL and then exited Dataease, I get a "Stack failure" at
the Dos prompt.  But the users are also getting system crashes, at the
rate of about one per day, and this is rather bad.  It looks like a
problem with Dataease's memory management.

Would I have gone this way if I had known then what I know now?  Yes,
I think so.  I could even have met my original target of a database in
a week, if the copy of Dataease that I started off with had worked on
a Lan Manager server.  The product is deep, in that it is possible to
do a lot with native Dataease, without writing a line of code.

The approach of spending a day designing before touching the computer,
is one that I'd recommend - I believe that it was time well spent.
The most important thing was designing what tables were required, and
what went into each table.  If you get that badly wrong, it will
affect the whole operation, and will be very difficult to make right
later.

By the way, if you want an easy way into Dataease, have a look at
Infomaster, which is a slightly old version of Dataease.  You can get
it from Amstrad for #70, which has to be a wonderful bargain for a
relational database.

The future

There is going to be an OS/2 version of Dataease, in the fullness of
time (a friend of mine already has a beta test copy).  This will, I
hope, deal with the memory limitation problems that we had.  It will
also mean that you can be using Dataease for working with one table,
and start it up again as another task, to work on another table, and
flick back and forth between the two.  This would be very convenient
when you are looking up a customer to see if he already exists, and
then entering him if he doesn't.  At the moment, reloading the
different overlays takes a few seconds;  with OS/2, it would be
instant.

There is also going to be an SQL version.  SQL is a way to implement a
client/server system.  The way the current system works, if you have
to search for a record, the entire database must be read across the
Lan, even if all you want turns out to consist of two records.  With a
client/server system, if you ask for all the Joneses that bought
Tarsier combs, the server is sent a request for those customers, the
search is done down on the file server, and only a few records are
sent across the network.  It is faster, and more secure (in the sense
of the prevention of data collisions).

The database is up and running, then, but it isn't static.  All the
time, some new report is needed, and now there are plans to put the
purchase ledger (currently done on paper) on to it as well.

In the words of the Chief Executive "Sparkling Marsupial helps you
make your Aardvarks sparkle".  Now they can do it even more
efficiently.

<>


Footnote: Some of you have probably realised that Aardvarks are
not marsupials. You know this and I know this, but the Founder
and Chairman of Sparkling Marsupials doesn't. And I, for one, am
not prepared to destroy the cherished delusions of a nice white-haired
old gentleman.

[Note to editor - I'm willing to offer an Aardvark Cleaning Kit to
whichever reader does the best guesses which company it is really, and
what the products actually are, if you want to run a small contest.]