Table design to match imported data to stored data

[wvandenberg]

I am still trying to work out a few design issues before I start loading data into my db. I included this question as part of another thread but did not receive any feedback. So, I am, posting just this issue.

I have a table that stores lab test names (analytes) and associated information:

tblAnalytes:
pkAnalyte_ID
fkLab_Group_ID
fkReporting_Units_ID
Analyte_Name
Detection_Limit
fkAnalysis_Method_ID

I would like to be able to "link" similar analyte names to a standard analyte group and analyte name that my company uses. tblAnalytes.Analyte_Name will contain data like:

Mercury (Hg)
Mercury
LL Mercury
Trace Mercury
Hg DISSOLVED
EthylBenzene
Ethyl benzene
ethylbenzene
Tin (Sn)
Tin

I would like to store the original Analyte_Name (so we always have a record of the original). However, if the analyte is a variation of one of the company's standard analytes, the analyte name should always be displayed (viewing/exporting) with a standard spelling, capitalization, etc. For the above sample data,

Analyte_Name[tab][tab]Standard_Analyte_Name
Mercury (Hg)[tab][tab]Mercury
Mercury[tab][tab]Mercury
LL Mercury[tab][tab]Mercury
Trace Mercury[tab][tab]Mercury
Hg DISSOLVED[tab][tab]Mercury
EthylBenzene[tab][tab]EthylBenzene
Ethyl benzene[tab][tab]EthylBenzene
ethylbenzene[tab][tab]EthylBenzene
Tin (Sn)
Tin

All the standard analyte names will be in tblAnalytes (in addition to all the non-standard iterations) so I was thinking I could add a field in tblAnalytes that would reference the pkAnalyte_ID of the matching iteration that is the standard Analyte_Name. Like this:

tblAnalytes:
pkAnalyte_ID
fkLab_Group_ID
fkReporting_Units_ID
Analyte_Name
Std_Analyte_ID
Detection_Limit
fkAnalysis_Method_ID

Not all analyte names are in the standard list, so Std_Analyte_ID would be null for these non-standard analytes. For the analyte iteration that is the standard, it would reference itself.
Here is how the data would look:

pkAnalyte_ID Analyte_Name Std_Analyte_ID
1 Mercury (Hg) 2
2 Mercury 2
3 LL Mercury 2
4 Trace Mercury 2
5 Hg DISSOLVED 2
6 EthylBenzene 6
7 Ethyl benzene 6
8 ethylbenzene 6
9 Tin (Sn) Null
10 Tin Null

Am I totally going at this problem incorrectly or will this work? By "work" I mean, will it allow me to display and export all the different Analyte_Name iterations as one standard Analyte_Name?

Any advice or criticism is greatly appreciated.

Wendy

 

[traingamer]

That should work.

If all you are looking for is a standard 'Analyte_Name', you will have to account for those that do not have a standard name by defaulting to the name in current rec. (i.e Tin and Tin (Sn)).

If you are not referencing any of the other data in the records, then this is not 'normalized'. That is, for record 1, if all you need from record 2 is the analyte_name - 'Mercury' and none of the recording units, lab id, etc. then you're probably better off with a 'standard name table' as opposed to a possibly confusing self-reference back to the analyte table.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[wvandenberg]

Greg,

Thanks for the reply. That's a good point about having to default to the name in the current record if there is no standard name.

...if all you need from record 2 is the analyte_name - 'Mercury' and none of the recording units, lab id, etc. then you're probably better off with a 'standard name table'...

Thanks for mentioning this because I completely forgot that the Analyte_Name and fkReporting_Units together make up the standard list. Any results that are in non-standard units will have to be converted before the analyte result is viewed or exported. So, taking this error into consideration, the data would look like this
[tt]
pkAnalyte_ID Analyte_Name Std_Analyte_ID Reporting_Units
1 Mercury (Hg) 2 ug/L (must be converted to mg/L)
2 Mercury 2 mg/L
3 LL Mercury 2 mg/L
4 Trace Mercury 2 ng/L (must be converted to mg/L)
5 Hg DISSOLVED 2 mg/L
6 EthylBenzene 6 mg/L
7 Ethyl benzene 6 mg/L
8 ethylbenzene 6 ug/L (must be converted to mg/L)
9 Tin (Sn) 9 mg/L
10 Tin 10 mg/L
[/tt]
Is this normalized? Maybe a 'standard name table' (that includes the standard units), as you suggested, would be better? Any idea how to deal with converting the results to the proper units. tblResults is on the 'many' side of a one-to-many join with tblAnalytes

tblResults (Numerical results of sample analysis)
pkfkAnalyte_ID
pkfkSample_ID
Sample_Name
Result_Value
Symbol
Qualifier_Code
Outlier
QCFail
Comments
IDLoc

I can easily construct a table of conversions but am not sure how or to what table I would join it or would I just use it in a query?

Wendy

 

[wvandenberg]

Greg,

I hope I didn't offend you. I am still really interested in any suggestions you may have regarding my last post.

Wendy