AnsweredAssumed Answered

More of a database structure question...

Question asked by wbanks on Jul 13, 2011
Latest reply on Jul 13, 2011 by philmodjunk

Title

More of a database structure question...

Post

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]

Screen_Capture.jpg

Outcomes