Assist with a unique indexed file

[FoxEgg]

Thanks for your advice:

Background:
I have a database of my patients.
I create an individual file number. (Based on first two characters of last name and reverse dob... called FILE_NUM)

This file number can have an extension (especially if they have a surgery)

Example: John Smith 1/1/1965 is SM650101
His surgery 1 file is SM650101SU1
and this may have several entries (lines)
eg
1. RADIUS fracture surgery
2. ULNA fracture surgery
3. Assistant

etc


I have created an index on LASTNAME+FILE_NUM.. and made it UNIQUE so that when I search for a name and browse... I type in ...SMITH

..It gives me all the SMITHS (that is fine),,, but only one line per episode

eg

SMITH SM650101
SMITH SM650101SU1
SMITH SM650101SU2

and so on...


The problem:

If I edit the SU1 account eg

1. RADIUS fracture surgery (<-- SAY delete this one )
2. ULNA fracture surgery
3. Assistant

so I now have

1. ULNA fracture surgery
2. Assistant

AND then I RE-RUN a search for SMITH ...

..It still gives me all the SMITHS (and that is fine),,, except it loses the SM650101SU1 extension file.
eg

SMITH SM650101
SMITH SM650101SU2

I presume it is because I have not re-indexed,.. and have lost the relevant indexed and stored SM650101SU1 recno()...

HOWEVER altho I could fix it with a REINDEX ....I cant re-index unless I have EXCLUSIVE control of the file... and I cant have EXCLUSIVE control because it is a multi-user environment.

I can fix it by tricking around and adding another line to the entry.. so it looks like this (for example)

1. ULNA fracture surgery
2. Assistant
3. Note - added line to make file visible again

DO a search and SU1 re-appears

SMITH SM650101
SMITH SM650101SU1
SMITH SM650101SU2

I suppose that I could just enter a dud line every time I delete an entry.. but that isnt very slick...

Any more appropriate suggestions ????


Thanks in advance

Foxegg

 

[JRB-Bldr]

I'll start off with admitting that I am not clear on what you describe above.

If you DELETE one (or more) records for SMITH, that will not invalidate the previously existing INDEX.

So I cannot see where or why you think that the problem is in the INDEX.

From what you say, I'd guess that the problem you have is due to a bad data architecture design.

You should have one PatientID field which is consistent (EXACT SAME VALUE - for John Smith 1/1/1965 is SM650101) through all of your patient-related tables to use to relate the various records - not some derivation of the 'core' PatientID.

Then you can have a secondary field in the 'child' tables which represents the secondary meaning SU1, SU2, etc.

Maybe with a more simplified and more clear explanation, we would be able to offer better advice, but I'd recommend looking HARD at how the data is architected in the first place - it appears to be problematic.

Regardless, you should find a way to periodically PACK and Re-Index your data tables on a regular basis - weekly, monthly, etc. Maybe you create something which will automatically do this after-hours.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

To clarify my confusion - you say:
It still gives me all the SMITHS (and that is fine),,, except it loses the SM650101SU1 extension file

Either the results are 'FINE' or they are not.
And what do you mean that it 'loses the extension file'?

Does your DELETE patient record routine also DELETE a Windows file as well?

If not then what is going on?

You either want to DELETE the patient record (which appears to be working) or you don't.

Again, clarification is needed for me to offer better advice.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

What you describe is documented and predictable behavior, and it's one reason we generally recommend NEVER using unique indexes.

If you delete a record included in the index, the index is not updated to include the next record that qualifies for the index. Unique indexes have behaved this way since the beginning of xBase. It is behavior by design.

Unless your file is quite large, creating this index should be rather quick so you could use a free-standing (non-structural) temporary index file with the UNIQUE clause and create it when you need to do a search. Otherwise you'll always have this problem.

It also sounds like you have a normalization problem. Fixing that would obviate the need for a unique index, but it would introduce a boatload of other complications you may not be ready to deal with.

 

[JRB-Bldr]

I agree with Dan in that your data architecture is problematic. It should have been normalized from the start in a much better manner.

PatientMaster with basic patient demographics
PatientVisits with patient visit info (schedules, etc.)
PatientTreatments with patient treatment info
PatientNotes with patient physician notes
PatientFiles with patient file info, etc.
etc.

Additionally it sounds like you are using a problematic method of establishing and using your PatientID

I have created an index on LASTNAME+FILE_NUM.. (with the file number based on first two characters of last name and reverse dob)

This is a problem waiting to happen (if not already happening).
It may work with a very small number of patients, but eventually you will have different patients with the same LastName and the same DOB - PROBLEM!!!

You should have made your PatientID an Integer value (incrementing and unique). Integer so that it has a LONG way to go before you run out of unique numbers to use.

Then with that as the KeyID field for all of your tables (Parent and Child tables) you can have any other fields to designate the table info specifics (e.g. treatments, etc.)

And using a Unique PatientID assignment from the start, would have eliminated the need to a UNIQUE Index expression.

Good Luck,
JRB-Bldr

 

[TamarGranor]

The obvious case where this algorithm fails for creating a unique ID is for twins, who has the same last name and the same birthdate.

But basically, any algorithm where you try to create a unique ID based on data in the record is doomed to failure.

Tamar

 

[FoxEgg]

Thanks to all... and of course you are all right... I should have chosen a different data structure...(Hangs head in FoxPro shame)...

But I created this database years ago... and it is like so many things.. and as one so often reads... I should have got it right from the start... and not do quick fixes.

Thanks for all the correct comments and apologies if my question wasn't transparent.. but Dan was spot on with his observation that

What you describe is documented and predictable behavior, and it's one reason we generally recommend NEVER using unique indexes.

If you delete a record included in the index, the index is not updated to include the next record that qualifies for the index. Unique indexes have behaved this way since the beginning of xBase. It is behavior by design.

... and jrb is right to suggest a data rebuild... which I can do .. but I should do it in VFP 8 or 9 from scratch.

In the mean time...I suppose I could detect a change in my file_num and if so add some dummy record which will appear in the unique index... It is not an elegant solution... but until I do as you all advise (ie to build a proper data set) then it will do.

I will no doubt see you all in the VFP forum... as I wrestle with the OOP


Thanks to all.

FoxEgg

PS I seem to have a duplicate post... dunno how I did that either.