Can one field in Master Table refer to >1 Child Tables?

[jwol]

Hi everyone, I have a table design question.

I have a Master table tblData:

DataKey (PK)
CalibrationKey (foreign key)
Various data fields
.
.

I want the CalibrationKey to be able to refer to >1 possible calibratioin tables, since there are several possible calibration protocols. Each protocol requires a different table structure (ie different fields). So I'll have tblCal1, tblCal2 and so on. Then I envision having one query for each calibration table (qryDataCal1, qryDataCal2 etc) which will then be used to pull together tblData and the appropriate tblCal#.

Is there a good way to solve this type of problem? It seems non-standard to me. My two ideas so far are to either have a huge, non normalized tblCal with all possible cal fields for all protocols or have a separate foreign key in tblData for each possible tblCal#. I'm not thrilled with either of those solutions.

Hopefully I've explained clearly, thanks for any help or insight.

John

 

[jwol]

Thanks Pat,
Your solution is elegant in it's simplicity - the best ones always are! I was thinking about how to work a CalType table into the mix, and you showed the way.

Again, many thanks,
John