Difference between revisions of "Teaching Days"
DrewJensen (Talk | contribs) |
m (spelling ;)) |
||
| (7 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | This is a small self | + | This is a small self contained (Currently) database used by substitute teachers, in my local counties school system, to keep track of how many miles they have travelled 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. | The database uses a very simple table structure, much more a flat database then a relational one. | ||
| Line 13: | Line 13: | ||
[[Image:Workday_form_1.png ]] | [[Image:Workday_form_1.png ]] | ||
| − | 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 | + | 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 simply 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 immediately available on the list. The use of VARCHAR_IGNORECASE solves the most common data entry error with this kind of function, differences in case. Of course it does nothing for two separate 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: | ||
| + | <pre> | ||
| + | ALTER TABLE "CONFIG" ADD CONSTRAINT "ONEONLY" CHECK("CONFIG"."ID"=1) | ||
| + | </pre> | ||
| − | [[Category: | + | ====Release 2==== |
| + | As I mentioned a new person will be using the database this coming school year and they have made a request that will require an addition to the table structure. Also a current user has made a request that will require a change to existing tables ( requiring a small script to transform the data ) and a change to the main form. | ||
| + | |||
| + | The first change is the need to handle multiple school assignments in a day. Right now the database always assumes a staring location of home, and the schools table holds the distance from home to THIS school. Now we will need a table that has a starting location and an ending location. The first entry of the day will be from home, then from school1 to school2. | ||
| + | |||
| + | The other change is that one person would like to have comments associated with a teacher that are not tied to a particular work day entry. This will require that we pull the teacher information from a repeating entry in the workdays table to a separate teacher table. | ||
| + | |||
| + | One thing I need to keep in mind is that the change to support multiple assignments in a day is for one user. Granted there is only five total but still, I don't want to make the other four have to make extra choices for a feature that only one really needs. | ||
| + | |||
| + | The other thing is that I really don't want to make someone fill in all the values for a matrix of school to school. Again this is not a huge number as in this county there is a total of 23 schools, and I suppose it only needs done once for everyone. | ||
| + | |||
| + | Schema Changes - here is the first cut of the new schema to accommodate the requested enhancements. | ||
| + | [[Image:TeachingDays_Schema_2.png]] | ||
| + | |||
| + | [[Category:Database Scenarios]] | ||
Latest revision as of 13:09, 28 March 2007
This is a small self contained (Currently) database used by substitute teachers, in my local counties school system, to keep track of how many miles they have travelled 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 simply 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 immediately available on the list. The use of VARCHAR_IGNORECASE solves the most common data entry error with this kind of function, differences in case. Of course it does nothing for two separate 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)
Release 2
As I mentioned a new person will be using the database this coming school year and they have made a request that will require an addition to the table structure. Also a current user has made a request that will require a change to existing tables ( requiring a small script to transform the data ) and a change to the main form.
The first change is the need to handle multiple school assignments in a day. Right now the database always assumes a staring location of home, and the schools table holds the distance from home to THIS school. Now we will need a table that has a starting location and an ending location. The first entry of the day will be from home, then from school1 to school2.
The other change is that one person would like to have comments associated with a teacher that are not tied to a particular work day entry. This will require that we pull the teacher information from a repeating entry in the workdays table to a separate teacher table.
One thing I need to keep in mind is that the change to support multiple assignments in a day is for one user. Granted there is only five total but still, I don't want to make the other four have to make extra choices for a feature that only one really needs.
The other thing is that I really don't want to make someone fill in all the values for a matrix of school to school. Again this is not a huge number as in this county there is a total of 23 schools, and I suppose it only needs done once for everyone.
Schema Changes - here is the first cut of the new schema to accommodate the requested enhancements.
