Building a Pedigree Application in Alpha Five V5
I believe I have a unique or at least off the average beaten path use for Alpha Five V5. This unique use would be the development of a health tracking and pedigree database. I'll give you some background info and why I chose Alpha Five. I am not a developer, or a consultant. I am an end user who doesn't know much about databases who decided to embark on an ambitious project of developing a certification and pedigree database for the national dog club I belong to.
by Phil DeGruy
A little background:
My dog club's OFA Certification and Pedigree books consist of data on dogs that have Orthopedic Foundation for Animals (OFA) certifications. Handwritten pedigrees and photos of many of the dogs are included in these books. These record books have been kept handwritten in 3 ring binders with no backup of any type for a little over 30 years. The person responsible for keeping these records was thinking about retiring from the position and was concerned that since these records had no backup and bringing 3 dozen or so binders to the annual national show ran the risk of some of the books getting lost in transit and not to mention was a real pain for all involved, all or part of her 30 years of hard work would eventually be lost. I volunteered to transfer all of the information contained in the binders into electronic form and save the information on CD. This was to be done by scanning it, saving the image as a .jpg and transferring the images to CD. This was to be a backup to the handwritten records and not a direct replacement for them and was acceptable to everyone involved. Once I got deeper into the project of backing up the records, I could see that just scanning the records was going to be a lot of work. Other than having a backup and making the books easier to transport, I didn't really see any added value for club members for all the time I was going to put into the project. Around this time, since I was in possession of all the OFA books, it was suggested that I take over keeping the records if I was interested.
After my official appointment to the position of OFA Rep. for the club, one thing led to another, and instead of just converting the data to a static electronic form, I decided to design a database to hold the records. I felt that a database not only provided the needed backup of the records, but also would give added value to the records. Club members could use the database in their breeding programs as an aid in researching dogs that had OFA certifications, look at, compare, and print the various pedigrees. It would also eliminate the duplication of data in the pedigrees found in the original books.
Initial design phase
I worked with several club members and worked out the basic design details of what the database should look like, and contain.
It was decided that I should try to duplicate the look of the existing paper pedigrees, the format was already familiar to people in the club.
The top half of the pedigree form would contain the information on the dog, the certifications (if any) that it has, titles the dog has won, the breeder and owner and an approximately 2"x2" photo of the dog if one was available.
The bottom half of the form would contain a 4-generation pedigree of the dog. You should be able to click on individual dogs in the pedigree and go to that dog's pedigree.
If a dog has any offspring, then they should be able to be listed on a separate form or report.
If a dog has any siblings, then they should be able to be listed on a separate form or report.
Pedigree reports should fit on an 11 x 8.5 (standard, in landscape) sheet of paper.
Separate numeric reports that are based on the hip certifications, elbow certifications etc.
The same certification reports, hip, elbow, etc are to be listed alphabetically by dog name.
Data should be fast and easy to enter.
Data and pedigrees should also be easy to verify against various sources.
The actual database file format should be non-proprietary, and commonly used.
The database should be able to be distributed to club members in a cost effective way and for a nominal cost.
Information should not be able to be easily changed by just any club member. This is to insure the accuracy of the information distributed.
The database should be relatively easy to modify.
Information for publishing in the clubs quarterly magazine should be easily extracted so that this information can be placed and formatted in a Microsoft Word (.doc) format.
About now, you're probably saying to yourself, "why don't you just use one of the many good pedigree programs available". Well, I looked at and tried out many of the programs available, but they all fell short somehow. All programs I checked out prevent you from distributing the program with the data. None of the programs provided the necessary flexibility for keeping the particular information I need to keep and in the way it was desired. Most of the programs do a poor job when it comes to reports. Most pedigree programs also use some kind of proprietary data format, which means that the data would be hard to convert to another program. What I really needed was a custom database application that I could design myself and that didn't cost a fortune.
Now what do I do?
Not knowing a thing about databases, and already in possession of a relatively easy to program database, I was able with a little outside help, put together a basic database for the keeping of the OFA pedigree data. The database for the most part works ok and actually provides most of the specifications outlined above. Due to the particular database program I used, I found out I was not able to distribute the database. Combine this with a particular quirk on forms containing rtf objects, and I quickly became frustrated using this particular program. These problems quickly brought me back to square one, which is how to provide the information to club members in a way that would be useful to them. I have completed transcribing the books, and currently this database consists of over 3,500 certified dogs. Including the dogs contained in the pedigrees that are not certified to the equation, I have over 10,000 dogs currently entered by hand. Now I had a problem. I had committed over a year to the development and population of the database with data. People liked the result, but I was not able to sell a copy because of license restrictions and no runtime was available.
I recalled reading about a database called Alpha Five in a computer magazine, a quick search on Google brought me to the Alpha Five website. I read just about everything on the main site, and then looked at what kind of questions and responses were on the phorum. I decided that maybe I should give Alpha Five a shot, so I downloaded the trial.
The redesign in Alpha Five
Keeping the requirements in mind, I proceeded to get started on the new database and learn about Alpha Five at the same time.
Designing pedigrees in a database is a unique thing as I have discovered. Most databases are designed to work with data generated in a business environment and can have many separate tables linked in various ways to display the interactions between salesmen, customers, inventory etc. To display a pedigree, one main table is linked to itself in a set 30 times for what I call a 4-generation pedigree. While simple in concept, this special linking is foreign to most people familiar with database design for businesses. The way I want to display information and why I want to do it a certain way confuses and gets many people lost who are not familiar with pedigrees and breeding.
While I am not through with the database, the basic design functionality is there, and the database is almost ready for importing actual data into. While there is still more work to do, this is currently the way I have designed and set up the database application in Alpha Five. There are I’m sure, a number of different ways to set up the basic pedigree relationships, however I think that the way I present here is the most direct. I have used the collective wisdom and advise of several people who responded to some of my questions on the phorum. They are kind (and patient) enough to help me learn and provide solutions when necessary to get their point across to allow me to complete this project.
The Alpha Five database application.
Main table: Contains all of a dog’s information. Initially, I thought about breaking out some of the information and placing it in separate tables. Due to my limited knowledge in database design, I decided that it was easiest for me to keep all of a dog’s information in one table.
Pedigree set: Contains the main table linked to itself 30 times in a specific structure to generate a proper pedigree. The structure could basically be visualized as a pyramid on its side. Table linking is 1:1 from sire or dam ID to dog id.
Main form: It is a close duplicate of the original paper pedigree form. This is where I enter and verify a dogs information. This forms top half contains all the dog information to be displayed, as well as a 2 x 2 photo of the dog if one is available. In the middle of the form, I have a dropdown lookup for selection of the sire and a dropdown lookup for selection of the dam. The bottom half of the form contains the pedigree. While it would be simple to just display the dogs name field in the pedigree, this is not the way I did it. I created 30 RTF boxes and inserted all the fields I wanted to display for each dog in the pedigree into the rtf boxes. The rtf’s give me great control over formatting and design of the actual the pedigree. The rtf’s also allow me to display much more information formatted better than most commercial pedigree programs can display about one dog in a pedigree.
The addition of hotspots over each rtf box has provided an easy way to navigate to particular dogs contained in the pedigree whose records I may want to view or correct. This was a simple process, which works very well. The addition of a button on the form enables me to return to the original record I started from, even if I have viewed 5 or 6 different records contained in the pedigree. This makes for fast corrections and additions to a dogs record when I am verifying pedigrees while allowing me to easily return to the original record.
The form is basically duplicated in a report for printing and fits on a 11 x 8.5 sheet of paper. The form also includes buttons for viewing the offspring, and siblings of the current record as well as finding other dogs using a find form.
Mated form: This form is based on a set that contains just sire, dam, and mated id’s. It is basically just an embedded browse that will display all dogs with mated id’s that match the mated id of the calling record.
Offspring Form: This form contains an embeded browse that will display all offspring the calling record may have. This is different than the mated form which only lists any siblings the calling record may have.
Find dog form: Based on the form from the Alpha Sports customer information form. There is a multi-state button on the form so a person can quickly find a dog by any field in the database.
Test Mating form: This form is based on a different table so that data in the main table is not altered. Basing this form on a different table also has the advantage of being able to keep the last 3 selected test matings from one session to another. The form is based on a set similar in structure to the main pedigree set and is for doing trial matings between a dam and up to 3 different sires. Photos of the sires are displayed if they are available. Reports displaying the test pedigrees can be viewed and printed if desired by clicking a button next to each sire selection.
Report menu and reports: Selecting the numerical or alphabetical buttons from the report menu form pops up an xdialog box that enables selection of the various numeric and alpha reports available. The reports are sorted by the certification appropriate for the report selection, or on the dogs name in the case of the alphabetical reports. There are actually only two reports, one for numeric information and one for alpha information. I use an xbasic script on each button to display the report selection dialog box and to determine which selection was made in the dialog box and therefore which query and filter I should run to display the proper information. The title of each report is a variable in a text box set by the script. The field title on the report is contained in a conditional object whose condition is determined by a variable set by the script.
Main menu: This is where you will start from and end up when you are ready to quit the application.
Utilities menu: This form contains buttons to perform various database utilities such as compacting the database, packing tables, and backing up the database.
These forms and reports while serviceable, are still subject to revision, some are as yet incomplete. As I learn more and I can see what works for my needs and what doesn’t work, I can make revisions and additions to the applications forms, and reports.
In the future, I plan to integrate a separate contact manager to keep the breeder(s) and owner(s) contact information, and email addresses. This will be a feature that will allow me to send correspondence to various people directly from the database.
There are many breeders and owners who have more than one dog appearing in the database so by having the breeders and owners in a contact database, I believe this will cut down on data entry errors of people’s names, as well as redundant information currently contained in the breeder and owner fields. It will also provide an easy way to keep up with changes in addresses. I also plan to make specialized data entry forms, because I receive data from various sources that have their own way of reporting information. By making a form that follows the paper source data, I can enter the information quicker and more accurately.
What started as a simple favor for a friend, has turned into a rather ambitious but fun project. Thank you Alpha Five for a great product.