Hi Steven,
>If I want a one table to many table relation, would it be better to have a field that holds a unique index of the particular document, than to index on the doc, rev, sub no comination.
Yes. The parent table should have a unique ID field that serves as a primary key and nothing else. In VFP 8 you can use an AutoInc field for that, in previous versions you need to write your own code to create such a unique number. The child table uses this ID in its foreign key field.
The reasoning behind using a surrogate key that you never know what happens to data with business information. For example, when writing banking software, you might think that the account number is a good primary key. But then the bank merges and you need to consolidate data from two different banks with lots of common account numbers.
Maybe in your case a future request will be to include a language flag, because the same document cannot only exist in different revisions, but also different languages. If you use these three fields as a primary key, you can now revisit all your existing code and change the expressions to include the new key field, as well. With a single field without business purpose, you wouldn't have to change anything in the code that deals with the relationships.
--
Christof
--
Christof