Hello all,

A Widget can go through several kinds of Tests (for now, AlphaTest and BetaTest) that have different attributes.

table widget
( widget_id int primary key
, ...
);

table test
( test_id int primary key
, widget_id references widget
, ...
);

table alpha_test
( test_id references test
, ...
);

table beta_test
( test_id references test
, ...
);

Inserting test data is easy enough. I'm trying to address some retrieval issues.

Given a test_id of 42, I want to cleanly display all the information held about that particular test to the user.

The number of Test subtypes will increase over time, so I'd rather not have to join on every subtype of test for performance and maintenance reasons -- I'd like to minimize the changes when new tests are added.

I'd also like to have a way to be able to store and associate user-friendly attribute names to these tables and fields. What I mean is so that on the GUI I would be able to do something like

Widget 42's Tests:
Alpha & Co -- id: 384 /*instead of alpha_test id: 384*/
Beta -- id: 439 /*instead of beta_test id: 429*/

or on the AlphaTest results page:

Test Operator: John Doe /*instead of test_operator: John Doe*/
Configuration Operator: Jane Public /*instead of config_operator: Jane Public*/

Any suggestions?
-----------------------
Here is my attempt so far.

I add a "test_type_id references test_type" to the test table, and the following tables.

table test_type
( test_type_id int primary key /*one entry for each subtype of test*/
, test_type_name varchar(25) /* "Alpha & Co" would go here */
, test_table_name
)

I still need a way to link back to the actual subtype (ie alpha_test) table. For this I actually stored the table name above. So you'd query this then query again into the table name you just discovered. Is this a horrendous idea?

One last table:

table field
( field_id int primary key,
, test_type_id references test_type
, field_name varchar(25) /* ie "config_operator" */
, field_description varchar(25) /* ie "Configuration Operator" */
);
Your impressions of this and alternative suggestions will be much appreciated. Thanks for reading through the long post.

Hi,
Can you explain your testing more.

Is a widget a physcial object or manufactured item?

What are the tests? Are these measurements? Hardness tests?
?????

In theory..... isn't a test a measurement to the specifications?

Vmusic

Thanks for the quick reply.

Um, a widget is a physical object that is manufactured? Not sure what you are getting at. . .

I guess a test is a measurement-taking procedure done by a particular instrument. So AlphaTest could actually perform several measurements. The fields in AlphaTest include the settings/configurations of the instrument as well as any comments about the test. Tests actually have a 1-N relationship with Measurements. I didn't include that in the prev post because I didn't feel the details of this would contribute to the more general issues I'm asking about and might possibly confuse the question more. I think for the purposes of this question, we can assume that AlphaTest has fields for each type of measurement done on it.

In this case, a test isn't necessarily a measurement to a specification.

Hope that answers your questions.

Hi,
I apologize. I thought the tests were 'quality' tests used in manufacturing, since you used the word 'widget'.

My experience in manufacturing is that a series of quality control tests are performed on manufactured parts, to see that they meet the specification.

Obviously that's not the case here.

I'm still curious as to what you're testing? Is this like a survey or statistical testing?

Now would this be testing for a clinical trial for a medication?

I can see two approaches, one approach assumes the tests are rather clearly defined. This is the subtype path you suggested.
The other uses measurements as repeatable components for tests.

I've attached some images.

Hope this helps!
Vmusic

Sorry about the late reply.

There's actually both of these things going on. The way I currently have it modelled is that Test holds some basic parameters that all tests will have, say Operator and Date. Each subtype has parameters that are specific to it. There is also a Measurement table that references the Test table:

table measurement
( measurement_id int primary key
, measurement_type_id references measurement_type
, test_id references test
, measured_value double
);

table measurement_type
( measurement_type_id int primary key
, measurement_name /*say Thickness or Weight*/
);

So this basically looks like your two pictures combined.

In my first post I said I was storing a table name inside one of my tables. I would use this value to determine which subtype of Test I should search:

I half-expected to get an uproar along the lines of 'you should never do anything like this', 'that's a horrible idea', and 'what's wrong with you'. Not having heard this, am I to assume that this is indeed an acceptable approach?

ThatFella

test table name and test type name are usually the same so you would only use one attribute.

Well the idea there was that test_table_name may have underscores or be in PascalCase or camelCase and perhaps have Hungarian style prefixes and things that the user doesn't need to see. test_type_name would be what the users are accustomed to seeing; it's strictly for labeling the GUI.

Is there a better way to accomplish this?

ThatFella