This is a small self containted (Currently) database used by substitute teachers, in my local counties school system, to keep track of how many miles they have traveled over the course of a year. It also keeps track of the total out of pocket expenses made in fulfilling their teaching assignments.
The database uses a very simple table structure, much more a flat database then a relational one.
Currently the database is intended to only track assignments where the teacher is sent to one school during any one day.
The database is setup to use the two entries, FirstSchoolDay and LastSchoolDay, in the config table as lower and upper bounds for all queries. This limits queries to a time period representing the current school year.
One item of interest in the database is the use of the data type VARCHAR_IGNORECASE. This is datatype is unique ( or almost ) to HSQL. The datatype was used explicitly to take advantage of a combobox on the main input form.
The form uses three drop down lists. Two are list boxes, both display data that is not expected to change often, if at all during a school year. The combo box highlighted in the screen shot on the other hand is used to select from a list that is expected to continue to change often as the year progresses. The combination of the VARCHAR_IGNORECASE datatype and combo box is an example of a good way to handle this type of circumstance with no need for a macro or any special instructions to the user. To add a new entry simple type it into the box and save the record. The combo box is automatically reloaded when the record is saved and the new entry is imediately available on the list. The use of VARCHAR_IGNORECASE solves the most common data enrty error with this kind of function, differences in case. Of course it does nothing for two seperate users where one likes to put Smith, Debbie and the other Debbie Smith?
There is another small point of interest, perhaps, with this database. The use of the config table. Since this table will store information that is used to drive much of the logic in the queries I wanted to insure that I always had exactly ONE copy of the information. This requires the person creating the database to enter a constraint, currently this means that one must enter the command in the SQL window. The command is this:
ALTER TABLE "CONFIG" ADD CONSTRAINT "ONEONLY" CHECK("CONFIG"."ID"=1)