>I wanted to make sure that the procedures are displayed in ascending order of date. I created a persistent relationship between the patient table index and the procedure table index. The index on the patient table is based on the social_security number only. The social security number is of the character data type. The index expression for the patient table is: "Social_Security". The index on the procedure table is based on the Social_Security number and procedure date. This is the index expression for the procedure table: "Social_Security+DTOS(ProcedureDate)".
>
>Although the two indexes are not identical, I made a persistent relationship between the two indexes. Everything seems to work fine. Whenever I move to the next patient record, the correct procedure records appear in the grid for that particular patient, and in the correct order (ascending by date).
>
>My question is this: Could any problems arise from creating a persistent relationship between two indexes which are not identical?? I realize that VFP's automatic referential integrity will not work in this situation, but that really isn't a problem. I will programmatically handle the referential integrity issues. There may have been a better way to do this, but this was the easiest method for me, considering the complexity of the data and user needs. This is a very complex application, with many sources of data being displayed at once.
>
>The thing that is bothering me is that I have created a relationship between indexes which are not identical. Is there any possibility that this could cause the records to not appear, or the wrong records to appear in any particular circumstance?? Apparently, VFP is matching the patient table index to only the social_security portion of the procedure table's index expression. The grid seems to be using the entire index to sort the records correctly. It appears to be working correctly, but I'm worried that I did something wrong that may come back to haunt me.
>

I don't see this as an issue where you handle the RI issues yourself; I'd prefer a different approach - use a parameterized view of the child table, make the persistent relationship between the child and parent based on the common key, and then order the p-view by the composite key. This way navigation from the child table back to the parent doesn't attempt to use a key value that's longer than the key expression of the parent - the specification of a key longer than the key width supported by the target makes SEEK operations fail. seeking a shorter, partial key in a longer expression depends on the setting of SET EXACT - OFF succeeds and ON fails.

Even if you don't use a pview, adding a tag that is based only on the social_security field of the child distinct from the candidate key of social_security + DTOS(ProcedureDate) will prevent unexpected results from making the persistant relation unbalanced seems preferable.

BTW, the keys seem to be inadequate in this example - it's possible for a patient to undergo more than one procedure on a given day is the simple case. There's a lot to be said for using a surrogate pkey for the child table to avoid the use of data-bearing composite keys, to both simplify RI issues and to allow the business rules for the uniqueness of the composite key to change.

Using the SSAN as the patient's identity fails if the patient doesn't have one (ie newborn babies, unidentified patients or non-citizens, as well as duplication of SSANs due to fraud or error) or uses one assigned to another person. It also will simplify a change of SSAN - the newborn child or an alien granted citizenship that intially has none is issued one.