MS Access Database Design Problem for test results

dazzler77
dazzler77 used Ask the Experts™
on
I am trying to build a database to store test results, and requirements tracking.
Each test has a step number and an instruction step.

My problem is there are different settings for each test which I would normally like to have different columns for.

For example the first 100 tests are like this:
test       5 degrees    10 degrees     20 degrees
1              pass              fail                   pass
2              pass              pass                 pass

For example the next 50 tests, each test  has different setting, but does not have different temperature ranges.

If I were doing object programming, I would have a base class, with each test having a class with different properties.

In access, I could use a different table for each test result set, but I would like to print out the report as one document.

I also want to link specifications to each test step.

Also, if I have different tables, how do I make relationships to different tables from one table to another?

eg.  I have a table of specifications, and some test steps are in different tables.

Specification    Test step      Result
1.2.3                 1.4               pass
1.2.4                 2.5               pass


Another way I thought was to use attached properties:
test      property   setting
1           temp          5
1           dial             3
1           voltage        7.4

test      property   setting
2           temp          10
2           dial             3
2           voltage        7.4

Using this method, it is very time consuming attaching each of these properties to each test, it would be easier to have these as columns in a table.  Just that the columns are different for groups of tests.


Maybe I am going about this the wrong way.  What is the best method to achieve this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What you want to do is break this down one step at a time.  Each table should represent one "thing" and all the fields in that table should relate to it.

<<Using this method, it is very time consuming attaching each of these properties to each test, it would be easier to have these as columns in a table.  Just that the columns are different for groups of tests.>>

 The rub with that is that every time one of your test changes or a new test comes along, you database design would need to change.  But if you do it right, the only thing that should need to be done is records added.

You've mentioned a couple of things in the above, and I'm not quite clear on how they all relate:

Specifications
Tests
Results

 Not sure where the degree changes fit in (5, 10, 20); would that not be conditions for a specific test?  Or in other terms, it's really just another test?

 Probably what would be simplest is to walk us through a actual example of something you would test.

Jim.

Author

Commented:
thanks for your comments Jim,

You are correct that there are lots of different tests.

A specification is like:
1.1  For each temperature below 20 degrees, the output variation shall be within 5%.
1.2 For temperatures above 20 degrees, the output variation shall be within 10%
1.3  The output for each combination of voltage inputs will not change.

You an ignore the specifications as this is easy to relate once I have I have a table of all my tests.

A 'test' is just a combination of settings.  one test might relate to specification 1.1 and 1.3 and another test might relate to specifications 1.2 and 1.3  These can be related in another table.

For each test or combination of settings, I need a result.  The result is pass or fail, or it is a voltage or number output.  Some tests I need to repeat many times.  Others I may repeat a few times.  Each time I would like to record the date and time.

So I would like to do something like this

Test 1 is related to specification 1.1 and 1.3.  The settings for this test are: temperature = 25, dial = 3, voltage = 7.4.  Measure the output.  The output is to be within the range 5.5 and 6.2.

Test 1 attempt 1 = pass (record date and time of test)
Test 1 attempt 2 = fail (record date and time of test)

In the future I may add different temperatures, or voltages, or dial settings.

Not sure where the degree changes fit in (5, 10, 20); would that not be conditions for a specific test?  Or in other terms, it's really just another test?

Yes it is just another test.  But for each temperature I have to test each combination of other voltage and setting.  I really would like a 3 dimensional matrix.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
OK.  Two ways to do this.  One is a basic method which I've outlined below.  The other would be more complex, and it's what you were referring to as adding properties.  It's called a entity name/value paired design.  

It's very flexible in that you can extend the design of the data through adding records.  However it's considered a poor relational design for a number of reasons and does present some problems.

There are some instances though where you need to use it and as long as your aware of the problems associated with it, you can do so.  

But let's start with a regular normalized design and see if your happy with that.

Let's see how this fits:

tblSpecifications - One record per specification
SpecID - Autonumber - PK
Description - Text
DateCreated - D/T

tblSpecificationDetail - One record per specification step
SpecDetailID - Autonumber - PK
SpecDisplayNumber - Single
SpecDescription - Text

tblTestDetail - One record per test
TestDetailID - Autonumber - PK
DateCreated - D/T
Conditions - Text
InterationsRequired - Integer

and a linking table between the two:

tblTestDetailSpecs - One record per test detail per spec it's linked to
TestDetailSpecID - Autonumber - PK
TestDetailID - FK to tblTestDetail
SpecDetailID - FK to tblSpecificationDetail


and for the results:

tblTestResultDetail - One record per test per unit.
TestResultDetailID - Autonumber PK
TestDetailID - FK to tblTestDetail - CK1A
InterationNumber - Integer - CK1B
SerialNumber - FK to tblEquipment
TestDate - D/T
TestValue - Decimal
TestPass - True/False

  Now this last is not quite normalized as SerialNumber is repeated in each record because we may have multiple records due to the number of interactions, but let's leave that slide for the moment.

 With the above design, your leaving it up to the tester to read the condition for the test and determine whether it is a pass fail.  That may be sufficient for you.

 If not, then we'd fall into the EAV design.  This would be where for each test, you'd list the various properties to be check (temp, voltage, etc), the data type you need to record as a result, and the value or range that you accept as a pass.

 The measuring of result then would be linked and determined by this test record and pass / fail could be determined by the recording of the value.  i.e. test is:

Voltage - Numeric  -  range  5.5  to 6.2

A measurement of 5.4 would be a fail.

Conditions would be the same.  You'd name each condition for a test and a value:

temperature = 25
dial = 3
voltage = 7.4


This is exactly what you were thinking about in your initial question.  And while it does take time to setup, it's extremely flexible as you can add any sort of test you require, with any parameters needed, and record the result.


Here BTW is an excellent past thread on EAV designs, and the in's and out's.  Read my first comment there with the links and check those out:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23714122.html

 Further down in that thread, you will see a design for an asset system, which might help clarify the type of thing were talking about.

Bounce back and let me know which direction you'd like to go and we'll flesh it out.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial