More of a database structure question...
In this scenario have four tables.
Table A: a pre-populated table linked from a different database; it contains the fields CustID and Name; this data is read-only.
Table B: an empty table; it contains the fields CustID, Name, CustData001 and CustData002
Table C: an empty table; it contains the fields CustID and CustData003
Table D: an empty table; it contains the fields CustID and CustData004
All tables are linked through the CustID field.
Table B is the entry point to the database. When a user creates a new record and enters information into [Table B].[CustID], one of two things happens:
(1) If the CustID doesn't exist in either Table C or Table D, records are created in C/D with the associated CustID.
(2) If the CustID does exist in the either Table C or Table D, these records become associated with Table B.
Here's the problem. The information in either Table C or Table D may change, but in event it doesn't, I'd like to have that information associated to the correct record.
Having a single unique identifier (CustID) won't get me what I need... because it's basically no longer unique. I thought about creating another unique identifier (ArtifactID), but I'm not sure if this is the way to go about it.
I probably didn't explain that in the best manner. See the attached image.
Record6 would continue to use the results of the last associated [Table B].[CustID]