New database project: working towards a new version

Over the last 4 years I have used various ways of keeping track of the ringed gulls that I have observed.

Google docs

At first I started with a spreadsheet in Google Docs, but after 400 entries or so that became unmanageable and it didn’t allow me to do any proper searching and reporting.

My Google Docs spreadsheet which I maintained until it became unmanageable.

My Google Docs spreadsheet which I maintained until it became unmanageable.

OpenOffice

I then wanted to use a proper database and initially I looked at using MS Access (in my early 20’s I used to program Access databases using Visual Basic). However, since I didn’t want to invest in any payware applications yet and the fact that my main system was a Mac laptop, using Access wasn’t an option at that time.

I therefore built myself a database using Base from OpenOffice.org.

My OpenOffice database.

My OpenOffice database.

What a nightmare this was.

Although I was able to create some nice features, the one big thing that was missing was a proper search method. To include it I had to learn myself SQL which I was not prepared to do. On top of that, the application itself turned out to be incredibly unstable with frequent crashes and even when it didn’t crash, there was no certainty that entered data was actually saved. After a few months of struggling I finally gave up and started looking at an alternative.

FileMaker 11 Pro

After finding out what other database packages were out there I decided to invest in FileMaker Pro version 11.

My FileMaker Pro 11 database.

My FileMaker Pro 11 database.

This turned out to be the exact opposite of OpenOffice: very user friendly, stable, lots of useful features ready to be used without additional programming and easy customization through scripting. A bonus is that the database can also be viewed on an iPhone and iPad, so I always have my data with me when I’m in the field.

I have been using this database for the last 2 years now and it has been an absolute joy to build and use. It has evolved into a highly automated system which for instance requires only a limited number of steps for adding a new individual (by using templates with pre-filled data) or for adding a long list of observations (down to entering all details for a new observation by just a single mouse-click). It is full of other neat little features such as automatically filling in the age and plumage of an individual based on the observation date, as well as calculating the number of days since it was last observed.

Ways to improve it

Good as it is though, there is one main thing that I want to change. The way that the database is currently set up is that the unique field is based on the ring. This means that I am tracking the ring and not the individual bird and when the bird looses a ring or is fitted with new rings, I have no way of properly tracking that. Instead, I end up with multiple rings with separate observation histories for what is essentially the same gull. Not good.

Another disadvantage is that I can only enter a ring into the system once and when I encounter another bird with the same ring code & color combination I am forced to use a different notation method to enter it. Not very problematic, until you need to remember how it was entered when you search for it a year later.

The solution therefore is to give each bird its own unique ID. It then doesn’t matter if it is fitted with new rings or that another individual already exists in the database with the same code/color combination. It is quite a rigorous change though so this needs some careful preparation.

FileMaker Pro 12 Advanced

FileMaker has recently released FileMaker Pro version 12, of which I have purchased the Advanced version for developers. My intention is to use this to build a new database from scratch using the new setup and to later import the records from my current database. This will also allow me to clean up the code using the lessons that I have learned while developing the current version, to get rid of some features that I have stopped using, to better implement existing features and to give it a more modern look.

The first challenge though is to figure out a way to migrate my thousands of records from the current version to the structure of the new database. It should be relatively easy by using scripts but I will have get to grips with version 12 first and do some proper testing before I can give myself the go ahead and start creating the new database in full.

This time, I will also create this version in English and will regularly show updates over the next months.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s