Hi Bob,

What I am about to say will put a smile on the face of the technically
challenged and horrify the technocrats in our group.

Don't count on it! ;-)

Even though I have been a systems analyst and programmer with major
companies like IBM, Lucent Technologies, and Avaya for over 32 years,
I still keep it simple.

I haven't been at it quite as long as you. I started programming only
about twenty two years ago, but the first programs I wrote were in
fortran iv, written first on paper and then on punch cards; and then
we got to wait half a day for the output. The same programs now might
take all of thirty seconds to compile and run. But I like to keep
things simple too. As simple as possible, but no simpler.

All of the data is entered by me typing it into an ancient DOS editor
that never even made it to product status. It was a prototype written
and used internally at IBM where I worked years ago.

For simple stuff, I still use Notepad on windows. It is quick and
easy and cut and past is trivial. But for much of what I do, it can't
match a decent programmer's editor, like emacs.


After I have entered enough new data to make it worthwhile to upload a
new version of the database which is created by merging the old flat
file with the flat files of new data (5 to 10 thousand records in up
to 5 separate 2000 record files). I open the main file and all the
files of new data with ultraedit.
I cut and paste the data from the new data files into the main one and
use ultraedit to sort it all on the name field so I don't have to have
the retrieval code do it.

I then open the database, which is in MS Access/97, delete all the
data, and import the new complete text file into Access. I use the
Access data maintenance tool to compress the database and then upload
it to the website.

Since I only have a slow dial-up connection and the database is now
just under 17 megabytes, I do not want to upload it without a good
bunch of new data.

It is here where a little more complexity would serve you well. You
can combine a decent database with a scripting language (such as php
or perl), so that you can enter the data directly, rather than as a
single large chunk. If you set up a web based data entry page, you
may end up sending only a few hundred bytes at a time. You have a
head start on this because you already have a web interface for
querying your database. You only need to extend it now to allow data
entry. This would be a little more complex to maintain and program,
but it would eliminate merging the data files, and then sorting the
data, and then deleting the existing data and importing it into Access
and compressing it.

I have found there is always a tension between making a program's
interface simple and easy to use and the amount of work required of
the developer. Making a user interface simple for the user, so simple
that it is hard for the user to make a mistake, can easy increase the
programming effort by a factor of ten. Sometimes, no-one is willing
to pay the extra cost. But when it is done, we end up with happy
clients.


Currently it is one totally un-normalized table. I tried normalizing
it by creating a book name table and referring to it from the main
table but that only cut the file size down by a couple of megs so I
decided to leave it as one table. I think that a large portion of the
physical size is in the indexes since I have them on the plant name
field, the hybrid or species field, the photo or drawing field, and
the colour or black and white field.

You may need to think about why you need each index. I can see the
need for an index on the name and species/hybrid fields, but why have
an index on a blob (your pictures)? I am not questioning your design
decisions here, but just pointing out that if your indeces are a major
contributor to your size issue, some analysis may help.

If there are any MS Access wizards out there that can tell me how to
make a significant reduction in the file size with what is really one
very simple table, I would love to hear from you. The space for out
club website is donated by my ISP and I keep worrying that when they
see how much space it is using that they may stop being so generous.
If that happens, our club will no longer have a website or the
database. Cutting the file size down will not only save me time
during the upload and allow more frequent updates but it may make my
space usage less noticeable by my ISP.

What I would suggest here is a bit more proactive. Instead of waiting
for your ISP to complain about the space you're using, take the bull
by the horns and contact them and express to them your concerns about
the space taken up by your database, and discuss some alternatives.
Your ISP will be happier with you if you do this than if you wait for
them to notice the amount of space required by your database, and I'd
expect them to be more favourably disposed toward helping resolve it.
If you wait for them to complain, they may just choose to stop
supporting you, whereas if you contact them proactively, they may be
willing to find a solution you both can live with. One alternative
may be to arrange for a subsidized or free (if they're willing)
permanent DSL connection so that your society uses its own machine as
the database server, and then configure it so that the web page,
hosted on the ISP's server, would submit queries to your own database
sitting on your own machine. You can then, with your ISP's help,
configure your server to respond only to queries from your ISP's
machine (e.g. by properly configuring your firewall). I am sure it
would cost your ISP less to provide a DSL connection than to provide a
lot of disk space. And your ISP may well have some better ideas both
for managing the space requirements as well as how you get your data
into the database. After all, I have seen a number of ISPs that
provide support for web based databases (a service for which some
charge a pretty penny), so they have the expertise.

HTH

Ted