design: cross-ref issues

[Davide]

Dear all,


I have the following design issues: an analysis is made of several tests. A test is performed measuring one parameter (given a unit of measure) by means of some methods. The test will produce an outcome, a measure of the parameter in the given unit of measure.
For example, Aluminium may be measured in mg using methods 1 and 2, or 2 and 3 (and these are two different tests).
The same parameter may be measured using different units, and they would be different tests. Same if the parameter changes, of course.
In some sense, tests are master data, since the same couple parameter-unit of measure can be measured in a given set of methods.
The outcome instead is generated after a test is selected and performed for an analysis.
See the following table as an example of possible tests.

TestID	Parameter	UM	Methods
1	Al	mg	method1, method2
2	Al	mg	method2, method3
3	Al	ml	method4
4	Cd	mg	method3, method4

At run-time, the lab technician will select testID 1 and perform the test, producing an outcome. The same test can be perfomed several times, but in different analyses, producing different outcomes.

At the same time, a parameter may have some limits, given the unit of measure, according to different destinations.
I thought about two possible design solutions, which I attach here. If you look at the E/R diagrams in both files you can see the two options.
Basically, in the second DB, Lab2, I got rid of the table ParamUM, which simply puts together the parameter and a unit of measure.
Which do you prefer and why? Or are they equivalent? Thanks a lot.

[ranman256]

use 2 tables: ElemID, elem, UM 1,AL, mg 2, AL, mg 3, AL, ml

SubTbl:
testID, ElemID, method
1,1,method1
2, 1, method2
3, 3, method4
4, 2, method2
5, 2, method5

[NoellaG]

Sorry for not having time to examine your example, but this is a simple lab schema we use (table users is link to users module)

[Davide]

That is what I am doing, but there are two possibilities:

1 I create the table ParamUM



2 I attach Parameter and UM directly to both Test (on one side) and Limit (other side)



I think table ParamUM comes handy, but at the same time it seems redundant, since it is made just of Ids (as Test btw)
Ideas on this? Thanks

[NoellaG]

If you have a many to many relationship: 1 parameter can have many units and 1 unit can be attributed to many parameters, there is in almost every case a linking table that holds the possible combinations of ID's. So tables with only ID's are common and even essential in relational models. However, in our application we see the unit as a property of the analysis (test in your example). Some tests measure a parameter in ppm, others in mg, for instance if you test Fe in water you get an other measuring unit as if you would measure Fe in Iron ore, but that's normal because it are 2 whole different kind of tests.

[ssanfu]

@ Davide
You might want to read this article about Primary Key Tips and Techniques