not sure of the best method to do this:
3 parts.

I appologize in advance, this is rather long, and not exactly the simpelest of question you'll find in here. any and all responses will be appreciated, even if it only covers part of the issues in question.


2 ( possability of 3 ) tables. part and part_dim, may add part_dim_old

part table: holds common information about a part ( id, current revision levels, etc )

part_dim table: hold all dimensional data for a part at a specific revision level.

all information is entered in a grid contol. Will require separate cursor to drive the grid as the information in it and desired presentaion of data are not identical.

problems:

1)
the dimensions are broken out into operations ( for production ). lets say there are 3 operations ( 10, 20 ,and 30 ) that control the same dimension on a part. rather than having three entries on a grid that when being changed they all need changing individually, the user would see something like <10, 20, 30> in the operaion field of the grid, change it one time and automatically all the records in the (main) table are updated ( for operations 10, 20, and 30 in this case).
not sure of the best way to acheave these results ( hold record numebrs in display cursor ..? ) as the main table holds the operations as ints, and in order to store "10, 20" you would need a char field

2)
there are fields that will be linked to other fields in the same part ( internal dimension poining to the finshed drawing dimension it creates ). this on its own is rather simple. what I was thinking about was holding the record number in a field on the internal dimension. that record number would be the record number of the finished dimension.
The problem is that when a part is being changed ( goes through a revision process ) all the information of the old revision needs to be copied exactly so that in the future one could call up all the information as it appeared in an old revision. if this information is copied, then the stored record numbers would be wrong ( new rev pointing to old finished dimension ). The I was thinking about storing an offset value ( the linked record is 10 behind me ), but once again if copied, all the new records will one after another; what if the 9 records inbetween the links where for a different part? Aswell, When there is a revision change I need to be able to quickly identify the differances between the old data and the modified data as the client wishes these variations to be highlighted during the revision change cycle.

3)
regarding above, not sure weather to copy old revisions to an obsolete table or keep it in the current one ( identified by the revision number ). When this system is in full use, I expect there to be 1M+ records within 3 years if there is not an obsolete table. not sure which method would be better.

thank you to anyone that is willing to help me out with this one. the information that this will control is very sensitive and cannot fail.

- jer