Databases
Temporal Values.
The original concept was to try and write this so that is could work on as many different types of databases as possible. That is still the goal. However I am forced to drop atleast one popular database from atleast the initial version of this application. While looking at how the various data types work work for different databases, one major problem stood out. You can not store sub second values in any temporal data type with MySQL. In other words if I save "2010-12-10 14:12:09.019473" into a datetime field it will truncate the .019473 portion before saving the time and there is no way to get it back. For an application who's primary goal is to manipulate subsecond times that is a considerable problem.
There are some Klugey ways to get around that problem like convert all time values to strings and save them and then convert them back again when you extract the information. However with Oracle, PostgresQL, MSSQL and others able to handle sub second values nativley it makes more sence to drop MySQL from support from the initial release of this application. I will try to get MySQL working after the first release. Or maybe Sun will give it some decsent temporal support. For more information look here and here.
The other issue I rab across regarding tempral data types was that Postgres and Oracle both have Interval data types. MSSQL does not. Because of this and because of some other reasons related to when you move to wireless systems I have decided to store all interval times and the stop time as absolute values in a DATETIME data type. Storing times as intervals would make things a lot easier and would improve performance. However it would not be as cross platform. Again I could have kludged but have chosen not to at this point. Thios decision may change before the 1.0 release of this application.
Database Changes
My intention from the 1.0 relase onward is to only make database changes with full releases of the application. The should be no database changes with point releases with the exception of possibly adding columns. However even those I will try to keep to a minimum. Bewarned however I am a long ways away from the 1.0 release and until that time I will feel free to change the database structure as needed. So I would not use any version before 1.0 for production data.
Reference Platform
I am currently doing my work on a on a Postgres 8.2.7 database running on Fedora 8 i386. I am providing a .sql file for creating the table structure I am working with. Once I have what I consider to be a fairly likely final candidate for the database structure I will create Oracle and MSSQL versions as well.
The statements in the .sql file are fairly SQL95 compliant. The major thing that stands out is that the indexes which are created with the SERIAL command will have to be changed to work with what ever database you choose to use.
The .sql files can be found in the down load section of the Konez project page at Source Forge. See the project link on the left hand side.
Database Table Schema
My initial stab at a database schema looks like this.
I will start with a description of what the tables represent and how they relate to each other. After that I will drill down table by table and describe the intent of the various fields.
PostgreSQL is a free database and available for most platforms. You can find out more about it and down load it at their site located here.
