Planning a new Database

[KarenABedeau]

Planning a new database

No Dave, the NEW single database for all years will have a unique ID field for each dog. The use of the same ID for different dogs is between my current
one year V1 databases. As dogs died or retired, I deleted them from the next year's database (IRISH (dogs).dbf database), and reassigned the DOG-nnn numbers so the most active dogs were the smaller numbers. That way I could either find them at the top of the table lookup or have them memorized and not need to look them up at all. Certainly not all dogs have changed numbers from year to year, and not all numbers have more than one dog using them over the 10 year span. As I mentioned to Cheryl, it wouldn't be hard for me to go through the current 10 dbf files and assign new unique numbers to each individual dog along with a logical field to indicate active or non-active. Of course, I would have to, as the same time, be sure the records in each SCORES parent had the correct Dog ID number as well.

Kay

[KarenABedeau]

Planning a new database

Cheryl,

Attached are my folders containing the V1 2001 and 2005 data. I went ahead and zipped the folders, because the file names in each are the same.

Kay

[Cheryl Lemire]

Hi Kay,

Thanks for the files. This may take me a while, so please bear with me and be patient while I think through this process :)

Cheryl

[davej]

Planning a New Database

Kay,

... the NEW single database for all years will have a unique ID field for each dog. The use of the same ID for different dogs is between my current one year V1 databases. As dogs died or retired, I deleted them from the next year's database (IRISH (dogs).dbf database), and reassigned the DOG-nnn numbers so the most active dogs were the smaller numbers. That way I could either find them at the top of the table lookup or have them memorized and not need to look them up at all. Certainly not all dogs have changed numbers from year to year, and not all numbers have more than one dog using them over the 10 year span. As I mentioned to Cheryl, it wouldn't be hard for me to go through the current 10 dbf files and assign new unique numbers to each individual dog along with a logical field to indicate active or non-active. Of course, I would have to, as the same time, be sure the records in each SCORES parent had the correct Dog ID number as well.Kay

OK, I see where I was confusing what you DID with the V1 application and what you are PLANNING on doing with the new application.

The simple way to look at it is - new, unique ID numbers for each dog is the way to go. Also, making sure that the new ID percolates through all related tables is absolutely critical in order to maintain the corrrect relationship between the various tables.

Whether or not you incorporate the year portion of the date in the ID, is, or course your choice. The reason I suggested it is that it would make filtering specific records (for a search or a report) a snap. The main point is that you need to be able to find the records you want when you want them.

Dave

[Cheryl Lemire]

Hi Kay,

I am unable to add the scores or trial tables from 2001 or 2005, I have no idea why. I did find this in the trace window:

ControlPanel.RightClick : Script:a5_select_tables_sets line:281
Variable type mismatch()

Not sure if it has anything to do with my not being able to add the tables to the db. Have you successfully created a new db in A5V7 and added these tables to it? If so, can you zip that db up and send it to me?

In A5V7, Tools -> Send Database -> Compact database before zipping -> Send File (You may want to click on the options button before the Send File and make sure that use default email client is selected).

Thanks
Cheryl

In the meantime, I am looking at the irish tables for 2001 and 2005 as I was able to add those to my db. Unfortunately I do not see any consistency with the dog name prefixes and suffixes so I cannot see a way to use any of the operations to move that data into a separate field. You will probably need to fix them manually (gosh I hate that word, lol).

[Cheryl Lemire]

Hi Kay,

While looking at the irish tables for the two years, I see that you have many things yet to consider since you decided to combine all the years into one database.

Obviously one of the major benefits here is that you can eliminate the need to have so many records within certain tables. However, you will need to, or should, create additional tables and take some of the fields from your existing tables and move them to the new table.

I am guessing that there may be the possibility that a dog could be given away or sold to a new owner. If this is in fact a scenario, you should keep a separate table for owners.

I would also create a separate table for the titles. This table you would want a field for the year, one for the titles, and one for the dog identifier linking field. You would then create a set with the dog table as the parent and the titles table as the child with a one to many link. By doing this, you do not need the titles field in the dog table.

I did not dig too deeply into this as I am not sure what your final design intentions are so I did not want to waste time doing something that would not be needed.

With the irish tables, I first added the irish table from 2001 to my db, renamed it to irish2001 once it was in my db. I then copied the irish table from 2005 to my db folder and in alpha I added the irish table again, this time being for 2005. I then renamed that table to irish2005.

I added the following fields to both tables: year, c, 4 - dog_prefix, c, 10, and dog_suffix, c, 10.

I then created an update operation 'dog_prefix_2001' that does the following:

1) Copies the CH, MACH, and OTCH from the beginning of the dogname field over to the dog_prefix field.
2) Removes the CH space from the dogname field.
3) Removes the MACH space from the dogname field.
4) Removes the OTCH space from the dogname field.
5) Populates the year field with the value "2001".

I then copied my update operation to the irish2005 table and named it 'dog_prefix_2005'. The only change I needed to make there was in 5) changing the 2001 to 2005.

I was not able to determine the proper code to remove the UDX, JH, AX from the end of the dogname field .... hopefully somebody like Stan or Tom can help with that part :)

Below is the expression for step 1) above:

IF(UT(WORD(DOGNAME,1))="CH".OR.UT(WORD(DOGNAME,1))="OTCH".OR.UT(WORD(DOGNAME,1))="MACH",WORD(DOGNAME,1),"")

You would have to add an .OR. for every other possible prefix as I did not know all of them and was simply trying to give you something to start with. For every .OR. that you add, you will need to copy steps 2) - 4) with the appropriate prefix.

I stopped at this point because I did not know if you would realize that a separate table for the titles with a one to many relationship to the dog table would be a realistic idea for you.

The hard part here is to avoid duplication in your main dog table. I do not see any automated way to do this within Alpha. You will first need to remove the suffixes from the end of the dogname field so that the dogname field is truly only the dog's name. Once you do this you can compare the dog names as you add a new year to the table.

Obviously the first year will be easy as you will copy all records in the first years table to your main dog table. You can then assign a unique id for that dog.

Once the unique id is assigned to the dogs in the main table for the first year, you can copy that id, the year, and the titles over to your new titles table ... if you choose to go that route, which I would highly recommend. It is the only way that I can think of to truly have uniqueness for your dogs.

I would then remove the year and titles data from those records (not the fields, just run an update operation assigning a constant value to the field as ""). This will come in handy as you add new years to the table.

Please note, best I can tell, you have a lot of work ahead of you for the conversion process combining all years into one database. In my opinion though, it will be well worth the efforts in the end as you will have far more flexibility and far less duplication.

Now repeat this process with the 2nd years table. Add the table to your db, rename the table, add the three new fields to the table, run the update operation to copy the prefix to the dog_prefix field, delete the prefix from the dogname field, assign the year value to the year field. Copy the suffix to the dog_suffix field and remove it from the dogname field. This you will have to do manually unless you or somebody else can find the proper operation to do this for you.

I would probably add an additional field to the 2nd years table ... unique id field which will be populated from the new dog table.

The next step will be to compare the dogname field in the 2nd year table with the dogname field in the new dog table. If the dogname exists, then copy the unique_id field value from the new dog table into the 2nd year dog table. Then use the 2nd year dog table, take the unique_id, the year, and the titles and copy them to the titles table. Then delete these records from the 2nd year dog table.

If you have dogs left in the 2nd year table, which I imagine you will, you can now assign a unique id value to those records. Take the unique id value, the year, and the titles and append those to the titles table. You can now delete the titles and year field and append those records to your new dog table. At this stage I do not believe you will need the year or title fields in the new dog table.

As I think about it, you could assign the unique id field in the 1st year dog table and move the title data over to the titles table from there. Doing that you can eliminate a couple of steps I mentioned earlier :)

I have attached the work I did. It has the two years irish tables with the update operations for both of them. I have the before update and after update tables for both years so you can see what the update operation did.

I imagine the steps I outlined above would be similar for the scores and trials tables as well, considering you may need to create new tables from them as well.

Unzip this file to a directory on your pc that is NOT your working directory.

I hope this gets you started. Good luck.

Cheryl

[KarenABedeau]

Planning A New Database

Cheryl,

Here are the files for 2005 after I converted them to V7. I haven't done a lot with V7, because I realized quite early in the process that some very basic revision needs to be done with the V1 files; since I am more comfortable working in V1, I thought I should accomplish the many databases to one
there and then convert. My current v7 consisted of small dummy tables that I've been playing with to help me learn the differences in the two versions.

Your suggestions and attachment have given me much information which I shall have to ponder for awhile. There are many things about A5 that I am unfamiliar with, including writing code.

I will go through your last post, a piece at a time. I like your suggested simplified dog table with titles in a separate table. As I progress I'll get back to you with questions. I know all of the dogs pretty well, especially those with lots of prefix and suffix titles; it would not be hard for me to go through all of the year's dog V1 databases (V7 tables) and update to the latest titles.

Now you need to give me some time...I have dogs to train (my "real" job,) so can't keep spending all day everyday at my computer. I'll tackle this for an hour or two each day.

Kay

[Cheryl Lemire]

Hi Kay,

Thanks for the new files. I will take a look at these. The update operation that I used in my first sample was created using the update genie. I did not write any real code, just made some modifications to the expressions.

I would suggest, with the help of the forum here, that you make your conversions in A5V7 and not in V1 ... This will make your learning curve a little easier. By the time you have created and copied operations for all of your tables and years, you will find it much easier in V7 than V1. I have ideas for comparing the dog names etc and should be able to create master operations for you that you can then copy to the appropriate tables for each year.

For the prefix I simply did the following in A5V7:

Operations Tab -> New -> Update Records (on left side) -> irish2001 (on right side) -> Create Using Genie button

Character Tab -> Break a name into its parts -> Next button -> Dogname field -> Extract title and put into ... dog_prefix -> Next button -> update another field -> next button -> assign constant value to a field -> next button -> select year field -> set value to 2001 -> etc

I then selected Don't run the update now (3rd and last option) and finish. This showed me the expressions. I modified the first line for the prefix removing the Mr. Mrs. etc and replaced them with the prefixes you provided. I then saved the operation and ran it.

I will look at options today for the suffixes, moving the title data to the new titles table, comparing the dognames and assigning unique ID values, etc.

When you are finished with your 'real job' ... you can come train my Sammy for me. Since I work on my computer all day here at home, she has absolutely NO social skills when people come to visit :)

I will get back to you hopefully later today with a complete conversion operation for you for the dog/titles table. I am going to assume that you want to keep the owners as if there is only ever one.

Good luck
Cheryl

[Cheryl Lemire]

Kay,

If you get a chance today, can you do the same thing with your tables for 2001 in V7 and zip them here for me. It will be easier for me if I have two valid years of all three tables to test with. This way I do not have to create test data to verify that my operations do as I need them to.

As I too have to get some real work done on my own application, I am not sure how far I will get today. But I will update this post when I am done for today.

Thanks
Cheryl

[KarenABedeau]

Planning A New Database

Cheryl,
I have spent all morning trying to send you the 2001 files converted to V7 (which I had not yet done). I had the same problems you did. So, for the TRIAL table, I re-created it in V7 and then re-entered all of the data. That's why you haven't heard from me in awhile. <G>

Now I have IRISH and TRIAL both in V7 in a database for 2001 (in it's own folder). However SCORES is another matter. I assume, since there was a table lookup specified for SCORES from both IRISH and TRIAL, that changing IRISH to IRISH2001 and TRIAL to TRIAL2001 messed it up. However, changing them back to just IRISH and TRIAL doesn't solve the problem, and now my original won't open in V1 either. I am attaching Irish2001 and Trial 2001 from V7. I'm going back to mess with SCORES some more - if I eliminate the table lookup and set the fields as user entered, perhaps I can move the table over to V7, and then reset the lookups.

Kay

[Cheryl Lemire]

Hi Kay,

Since I had to get some work done on my application today, I will only have time to work on the irish tables today, so don't kill yourself on the scores table. After I finish my work on the irish tables I will post my results for you.

I will not be able to work on this again until after 3pm tomorrow so you have until then to try and get scores working for me :) Sorry this has caused you so much trouble. May I suggest the following, for all other years that you will be converting over to V7, do NOT use your original tables, copy them to a different directory so that you do not have to re-enter data.

Thanks for these files for 2001.

Cheryl

[KarenABedeau]

Planning A New Database

Cheryl,
All of my fiddling with the SCORES database (V1) for 2001 has not gained me a thing. I can open it and see the data. However, when I try restructure (to check to be sure all variables are still the correct type) or the access the field rules (to eliminate the TRIAL and IRISH lookups), I get the message:
Error Unable to open compound index occurred while trying to start application.
Checking the indexes, there is an index on DOG_NO+VERIFY. I was not able to delete that index, so I went out to "My Computer" and moved the file SCORES.MDX to a temporary folder, think that, if there were no longer any indexes associated with SCORES, I could get past that error message to change the Field rules. Not so, I still get the error message.

I also tried creating another SCORES database (called SCORESX) which is empty and appears to have no Indexes. I tried to append to that new database from the old SCORES and that doesn't work either (unable to open compound index),

Any suggestions? You have my SCORES 2001 file, so there's not much sense in my sending it to you again. I can rebuild it in the V7 2001 database, but will I have to enter all of the data again?

Kay

[Cheryl Lemire]

Kay,

I can open it and see the data.

The error I am seeing is there is a variable type mismatch when I try to add the scores table to V7. I also tried in V6 and get the same error. If you can open the scores 2001 table in V1, can you check the table structure? The error I see tells me that maybe one or more of the fields in that table has a field type that was valid in V1 but no longer valid in V7?

Let me know if that helps.

[Cheryl Lemire]

Kay,

I was able to add the 2001 scores table to my v7 copy of the db for you. I will look at it and see if I can figure out what caused the initial problem. When re-reading your last post I noticed you mentioned .mdx .... and the light bulb went on because V6 and V7 use .cdx files and not mdx. So I did as you mentioned and deleted the .mdx from the files you sent me and the table added without issue.

[KarenABedeau]

Planning A New Database

Cheryl,

I'm not sure how that worked, but I'm glad my table isn't trashed. Now, whe I try to access my V1 2001 data, I can get browses, but when I try to look at the structure or field rules, still get the compound index error (for all three databases (tables). When I try to move the scores to a V7 table I get that line error with variable type mismatch.

Fortunately, I have everything from V1 backed up on a thumb drive as well as on another hard drive. My e: drive data lets me access the restructure and
field rules features...however if I move the files to my c drive, I am back to square one with the compound index error. And, when I try to move them to
the V7 database, I get the line type mismatch error.

Today I've only messed with 2001 data, fearing to mess anything else up!
Kay