Re: [tlug] Simple database application recommendation?

["Keith Bawden" <keith@example.com>]

> Context is that I have an ancient book database application. This was
> actually something I started creating about 25 years ago, and I'm
> still using it.

How many records are there in the current DB? Knowing this may help
you to refine your list of possible solutions. For example if it is
smallish then Sqlite may be the way to go, if it is a large number of
records spread accross multiple tables then something like
MySQL/Postgresql may be more appropriate... Either way something that
you can use SQL to query it would be the way to go.

> (so help me), but I also have a query front end written in Perl/CGI
> (hosted on Tripod).

# SNIP

> Any recommendations?

I'm making assumptions here on how your "query frontend" may be put
together, so please disregard this if it is not relevant.

If you are going to move to a new backend DB you may want to take the
chance to look at the "design" of your existing DB and maybe make it
"better". For example using normalization techniques [1].

Once you have your shiny new DB structure setup. I would then work on
importing the data from the old db. You may have to do some scripting
ecky thump[2] to get the data cleaned and ready to be imported, but
once you have it in the new DB life *should* be easier.

Once you have your data migrated over I would then look at the old
application and determine if there is any processing you can move from
the front end application to the backend DB.

For example, if you had a query something like this:

SELECT * FROM managers WHERE clue > 0;

And then you used perl to decide which records from the result set to
use (note in the above case you are likely to have very few records
returned :-). You may be better off refining the original query to do
as much "culling" at the DB level. The above may become something
like:

SELECT * FROM managers WHERE ( clue >=0 AND department  like '%'
+:search_string+ '%' );

This way you will get a smaller result set from the DB. Which will
mean there will be less for the front end app to chew on, less data
being sent between the DB and so forth.

Regards, Keith

[1] http://en.wikipedia.org/wiki/Database_normalization
[2] Scripting ecky thump is a åæé