Planning a new Database

[KarenABedeau]

As a new V7 user with an old V1 application, I have decided to spend some additional time improving my application on paper before actually putting it into V7. My application is not like most of the examples, and my first dilemma is whether to keep a separate database for each year, or to combine all years (eventually from 1936 to the present) into one database. As my reports are based on the calendar year, my original application has a folder for each year. Each new year is started by copying and zapping all of the databases to a new folder with the year as title.

Here's what I need to do: Each month a large book comes from the American Kennel Club with results from all shows held during the month. I am interested in the obedience scores, titles and other obedience awards earned by Irish setters only. I manually go through the book, and first enter all Irish setters appearing for the first time into a table called Dogs. Fields are Dog_ID (C3), Dog_Name (C40), Owner_Name (C40), and Titles (Memo). For each Irish setter that is published as earning an obedience title, I record the title abbreviation, date earned, and month/page of the awards book where the title is published in the Memo field. Next, I enter, into a table called Trials, information for each Obedience trial where an Irish Setter is listed. Fields in the Trial table are Trial_ID (C3), Date, Club (C40), City (C16) and State (C2). These are the two child tables for my set.

The parent table is called Scores. For each recorded Irish setter qualifying score, I enter the Dog's ID (from the Dogs table lookup), the Trial ID (from the Trial table lookup, and the information about the dog's performance (such as class, score, placement, points for various ranking systems, judge's name, number of dogs defeated, and month/page where the result is published).

The data entry form is defined for the set obedience. Once this is working, I will have many reports (by class, by total points, by average score, and by other criteria) to generate from this data. Only one report, the proofreader's report (which I mail, but would love to e-mail) to my proofreader in another state is generated monthly. The other reports are generated yearly for that year's trials only. I also need a report to print all results for an individual dog for the year. At this stage of the game, the reports are the least of my worries; they can wait.

I think the first decision is whether to have one database for the entire time frame of 1936 to the present, or whether to have individual databases for each year. The drawback to starting with an empty bunch of tables and sets each year is having to re-enter dogs that show over a period of many years. But, the nice part is that I can eliminate those that I know are no longer competing at the beginning of each year and keep my lookup tables
smaller. This would also be true for the trials table, as the same clubs host the same trials year after year, although Irish setters do not always qualify at every one, and there are also new clubs coming up with new trials as well as old clubs folding. One plus for combining them all is that it would be much easier, should I want to do so, to generate a report of one dog's lifetime results.

So, can anyone give me advice on whether to have only one database or to stick with one for each year?

[Dan Blank]

Hi Kay,

Many other A5 users will have many suggestions. My recommendation is to continue with one database but create a back up for each year.

Of course a lot depends on how your database is designed. The only real draw back that I can see, is "flirting" with the 2 GB size limit on the tables. If your tables are not near the 2 GB limit (this includes any memo files) then you should be fine keeping "One" database.

Otherwise you may need to consider keeping one for each year.

[Cheryl Lemire]

Hi Kay,

This is just my opinion, but unless the combined total of records since 1936 is 2gb, I would never create a new db for each new year.

Think about this: I sell thousands of products a year, but I have a new db every year .... I repair hundreds of cars a month, but I have a new db every year.

The only draw back I see from keeping all data in one db is when the file size gets too large and there are literally hundreds/thousands of users trying to query the db.

With the data you provided, I would use one db with all data. Provide us with more detail as to why you would need a separate db for each year and convince us why this is a better way.

Good luck
Cheryl

[KarenABedeau]

Planning A New Database

Thank you for your advice Cheryl and Dan.

It tempting to go with what has worked in the past, just because I am used to it. However, if I can make changes that will make all of this data entry more efficient, now is, obviously, the time to do it, while I am switching from V1 to V7.

Simplicity is certainly a good argument for having it all in one database; the year-end juggling would end. I could access data for an individual dog across it's entire career. I wouldn't have to try to figure out how I did the new year setup last year every time a year ends...because I forget.

One thing very different about this application than most is that I am the only person who will be accessing or entering data. The data is to be used, eventually, in a book or books (perhaps by decade), but is now used in a yearly report to the Irish Setter fancy.

I just checked my old V1 data; a folder contains subfolders for years 1996-2005, and another subfolder containing the application with all but the Dogs table zapped. The entire folder is less than 6 MB. Although the 60s and 70s will probably have more dogs competing, it certainly shouldn't bring everything to even 1 GB, let alone 2.

If I do this as one big database for all years, will the fact that I am entering data from random prior years (as I get my hands on the books - it's a loan from libraries all over the country...I'm fiddling with 1973 while testing different tables in the new version, just as a learning process) be a problem?
If I decide to save data by the year, this should be easy, should it not?
Should I have a field that indicates the year? Or is the fact that I have the
date in the Trials table with each trial going to be enough to extract a single year to archive or to just deal with while entering that year's data? And, to label dogs as "Current" or "Retired" should I have a separate field in the Dogs
table, or should a letter indicating this be included in the current Dog_ID field?
It would be nice to have to enter only new dogs and the trials for the year in the child tables. I expect some sort of system to mark those dogs that are currently competing and those that are retired would enable me to see only the current dogs (and the current year's trials) in the table lookup for data entry?

Lots of questions here, but now I need to go to my computer and play with
the "draft" tables (with pretend data) to see which questions I should be asking next. Although I've learned nothing about using Code, I am a long ago Fortran programmer (hired straight out of college in 1960 as a math major and on the job trained in 4 days), so was able to accomplish a nested IF statement to automatically fill one of the items on the Data Entry form. I am not sure what is the best approach to learning all of the little details I don't know about A5V7. The thought of plodding through the AlphaSports example is a tad daunting, because so many of the features are things I will never need to use.

Your help is very much appreciated!
Kay

[davej]

Planning a New Database

Karen,

I would use one table file & memo file (.dbf & .fpt, respectively). Add a field called 'Year' (C4) and simply enter the calendar year, or you could extract the year of the Trial Date (CYEAR(TRIAL_DATE) returns a character representation of the year of the trial date. This way, you can easily access data on a particular dog across as many years as necessary.

Dave

[KarenABedeau]

Planning A New Database

Dave,
How do you get a Memo file separate from the table file, or does A5 automatically do this for Memo fields?

You mean to use the table file for Dog ID, Dog Name, ownername only?
And then a separate table linked to it with just the dog ID field and it's titles in a Memo field?

Thanks for telling me how to extract the year. I've put this info aside to use when I get a tad smarter.

Kay

[davej]

Planning a New Database

Kay,

There are as many thoughts on how to do any one thing in Alpha as there are users of Alpha (at least it seems that way).

When you create a field and declare it's type as 'memo', Alpha creates the .fpt file.

Memo fields are usually created to store 'free form' text of unknown length, like notes or reminders. (ex: called on xx date, he said blah blah blah, she said blah blah. Told me to call back tomorrow and they would blah, blah). Some people like memo fields, some don't. Some users have reported problems with the linkage between the .dbf and .fpt breaking and all kinds of problems popping up, others have never reported a problem.

If you don't want to use a memo field, just create a character field and set it's length to something like 200 characters. This method bloats the record size, but the advantage (to my way of thinking) is that the pseudo memo field is part of the records and will probably never get trashed. I say 'never' because one never knows what a computer will do from day to day. Under normal circumstances, the retrieval time for the data won't be any different either way. I've done it both ways and just in the off chance that Murphy will strike, I would probably use the character field rather than the memo field.

Dave

[KarenABedeau]

Planning a new database

Here is something I've run into trying to convert my old data (V1), which is 10 1 year databases, to the new plan of one database for all years.

In my parent SCORES database, the dog ID is the link to the dog table. The dog ID currently (in V1) is a character field DOG-001 etc. Likewise the trial ID (character field 001, 002, etc.) child database in V1 is the link to the paraent database scores.

Here is the rub - the dog DBF and the Trial DBF are not the same from year to year. The trial DB is a whole new set of trials, beginning again with the ID 001.
The dog DBF may be the same for dogs that are currently competing that year (or are likely to compete again ie not dead or permanently retired). I often moved the most active dogs from say DOG-252, up to DOG-003 at the beginning of a new year if the prior DOG-003 was no longer active. So, a dog's ID number could change over the course of it's competition career. Likewise, a given dog ID (say DOG-056) could belong to three or four dfferent dogs over the 10 year period 9changes occuring only at the beginning of a new year.

I have, for starters printed out all of the 10 dog.dbf files. They are not so large that I can't assign new ID number to the lot, giving each dog that has competed it's own unique number. Then I need to somehow, change the link with the SCORES database, so the correct socres are then linked to the correct dog. I think I can do that by creating a new link (the new ID field) between SCORES and DOGS. I probably will need to go in manually and change the data for each year.

Do you see any easier way to accomplish this? I will have to do the same process for the TRALS, but I have a hunch this will be an easier process.

I do like the idea of having it all in one database in order to look at an individual dog's entire competiltion career, so I think making this change is worth the additional time and thought. Shall I make the conversion to one database in V1? Or shall I import the data to V7 first?

Kay

[Cheryl Lemire]

Hi Kay,

There are a couple of ways to address your concerns. You could keep the existing dog id but do not use it as your linking field. Add the year to the id, so you would have something like dog-001-1998, assuming you do not change the dog id within a given year. Create a completely separate field for linking purposes and keeping all dog data together even though the individual dog id changes.

I would copy my dbf files from v1 to the db location of the new db, be careful with the filenames. If the filename is the same each year, copy the dbf to a separate folder and rename it, something like dog_1998, trial_2005.

Create your new adb, create your dog and trial tables. Once all old dbf files have been renamed, move those tables to the new db folder and add the tables to your new db.

I would keep a copy of the old tables that have been renamed elsewhere, JIC you mess up in the new db you do not lose your data.

Take one of the old tables, dog_1998, and run a series of updates, ie update the dog_id field to add the year to the end of it (if you choose that route). Then append dog_1998 to your new dog table. Repeat this process with all of the old dog tables. Once completed, run an update operation in your new dog table to populate your new unique identifier field.

Repeat this process with your trial tables.

This is just one way, others may have more suggestions for you.

Good luck
Cheryl

FYI It is easier if you start a new thread when you have a new question or issue. It can sometimes be difficult to follow a long thread when there are different issues involved.

[KarenABedeau]

Planning a new database

Thank you, Cheryl.
If I just add the year to each DOG-nnn number, my resulting combined database may have 5 or six entries, each with a different ID. (This for the dog that competed over a period spanning several years). Added to the problem is that there are name changes on the dogs from year to year with the addition of title abbreviations (CH, OTCH,MACH, for example at the beginning of the name, and UDX, JH, AX for example, at the end). I can go through and manually update all of the names, sort on the name, and, finally delete the duplicates after making sure all of the related scores have the one unique ID number for that dog, and that no other scores are incorrectly attributed to it.

I expect, I should keep the new ID field "used entered." After this process is completed and I have only one dog table with only one unique ID for each dog, is it possible to change the data entry to "auto increment?" Or, will this change the ID's that are already in place?

Kay

Thanks for the tip on starting a new thread. I will for my next question that isn't related to this issue - converting from many old databases to just one. Is that OK?

[Cheryl Lemire]

Hi Kay,

Converting many old databases to one is a very general issue and really should not have all the different possible questions tied to one specific thread. That is no different than my working on one particular application with 10 different questions. Each question should be a new thread. It is ok to continue with this thread for your current question, but as you get new questions that would not be tied to say the dog id issue, you should post it to a new thread.

As far as the id's go, etc ... It would be a lot easier to assist if we could see actual data. Could you possibly zip up a couple of the dog tables and scores tables etc for a few different years so we can have a better idea of what you are trying to describe. I know that for me it is easier if I have the visual in front of me.

If you are willing to attach a zip with the tables from a few years, I would be willing to look at them and see if I can find more concrete answers for you.

Perhaps include a brief description of each of the fields and the data they include. As far as unique id's, there are multiple threads throughout all of the forums with many ways to accomplish this task. In the end, I now use the REMSPECIAL(API_UUIDCREATE()) in all of my applications. You can always keep a separate id field that makes sense to you, but it really is not necessary.

Example: instead of a dog id field with dog-001-1998, use a unique identifier field with the uuid and perhaps you can have a field for the year.

As far as dog names are concerned, the dogs name should never change. You can add a prefix field and a suffix field for the dog name, and in reports or wherever you need it, you can concatenate the three fields: prefix + name + suffix

Good luck
Cheryl

[davej]

Planning a New Database

Kay,

The two basic assumptions of using any field as a linking field between multiple tables are that the value in linking field won't change and secondly, the linkage value won't be reused. From what you have added, neither of those two assumptions are valid.

Adding a year field won't help, given that the Id value is changed and reassigned on some basis. It sounds like you need to go to seperate tables for each year, even though the majority of the table fields are (probably) the same. You could name the tables something like DOG_XXXX and TRIAL_XXXX, where XXXX is the year. That way you can copy the table structure to the new year tables when the time comes.

If you could change the numeric part of the dog ID to 5 numeric characters, you could accomodate an id ranging from DOG_00001 to DOG_99999, which gives you 10,000 values to play with. You could even add a year component - DOG_XXXX_00001 (again, XXXX represents the year). Then change the labels of the trial fields to something like TRIAL_XXXX_01, TRIAL_XXXX_02, the in the input field, simply enter the description of the trial.

Dave

[KarenABedeau]

Planning a new database

Cheryl,
If I just attach the three dbf files for two years, 2002 totals 95.0KB, and 2005 totals 46.5KN. (I cleaned out the Dogs.dbf file in 2005). Actually, in version one, the Dogs file is called IRISH.DBF. Can I attach the six files in two folders without zipping? I am sure I can figure out how to zip them, but it's not something I've done before.

My data for any given year is not so huge that I can't make changes manually. I like your idea of having suffix and prefix for the dog's titles, so the name never varies. I could do that manually, as well as changing the ID fields.

Dave,
What I'd really like to do is dump the DOG-nnn system of identifying dogs and just go for a straight 00001, 00002, etc. ID field. I would then add a logical field that is T if the dog is currently showing, and just use the Ts for my table lookup.
Could I access only a given year's data from the obedience set, since the field TRIAL_DATE is in the child table TRIALS?

Kay

[Cheryl Lemire]

Hi Kay,

Yes you can attach the six files separately to one post without zipping. If you are using xp, to zip them you can highlight the two folders with the six files, then right click and send to compressed file. Either way will work.

What I'd really like to do is dump the DOG-nnn system of identifying dogs and just go for a straight 00001, 00002, etc. ID field. I would then add a logical field that is T if the dog is currently showing, and just use the Ts for my table lookup.

This is a good idea. I would keep the dog-nnn system in place until you have all data transferred with a new unique identifier field for all records. If you go with the 00001, 00002, I would suggest you make it a character field and pad it with leading '0's. The logical field is definitely a better way to address that particular issue. Of course, that is my opinion.

Could I access only a given year's data from the obedience set, since the field TRIAL_DATE is in the child table TRIALS?

You can certainly run a filter on a report using the trial_date field to show only a given year's data.

Good luck
Cheryl

[davej]

Planning a New Database

Kay,

What I'd really like to do is dump the DOG-nnn system of identifying dogs and just go for a straight 00001, 00002, etc. ID field. I would then add a logical field that is T if the dog is currently showing, and just use the Ts for my table lookup.

Could I access only a given year's data from the obedience set, since the field TRIAL_DATE is in the child table TRIALS?

You can use the straight 00001 idenfitier. However, if you do that, you won't be able to differentiate from year to year. If you include the year, you can search for all fields where the year component equals whatever year you are working with.

Also, I am nervious about your comment that you would reuse the same number from year to year for different dogs. That could lead to mass confusion, or at least it looks like it to me.

Dave