Need Help: Tables Structure/Relationships

[MasterBIB]

First time poster here and a newbie at databases. Took a class in college (over 10 years ago), winging it from here. Appreciate the help in advance!

I am trying to create a database that stores information about scientific experiments I run in the lab.

During each experiment we can test many different variables such as color of product, amount of product used, whether its a solid or liquid, etc...

Each variable can have many different values tested, for example, for color i can test blue, red, yellow and green.

If you are not familiar with experimentation, each experiment has a control, the original value you are testing against.

For example, say as part of my test, the control color is blue. I test green, yellow and red against blue and find out if any of the test colors are better.

Ultimately I want to build a report that spits out the following:
- Give me all the experiments I ran that tested color.

The output should give me the following:
Experiment name, experiment date, experiment variable (color, amount), the values tested (red, green and yellow) and the results of each test.

The part I am having difficulty with is I want to associate the control of the experiment (the color blue in the example above) with the values I tested against the control (green, yellow and red).

I want people that view the report to know that the original color was blue, we tested 3 other colors and the result were x for each value tested.


My current report spits out each individual value tested on it's own line/record. In this case its hard for people to understand that the green and red value tested are associated with the blue color (control)

For example:
blue - control (break - new line/record)
Green - loser (break - new line/record)
Red - loser (break - new line/record)

Id like my results for each variable to spit out everything on the same line/record and associate the test values with the control value.

Blue - Control | Green - Loser | Red - Loser

My Current Table structure:

Experiments:
ExperimentID (Primary Key)
Test Name
DateofTest
Overall Outcome

Variables:
VariableID (Primary Key)
ExperimentID (Foreign Key)
Variable (color, amount)
Value (blue, green, red)
Outcome (winner, loser)

The relationship is one to many. As I mentioned, each experiment you can test many different variables.

Example variable table data:
VariableID | ExperimentID | Variable | Value | OutCome
1 | 001 | Color | Blue | Control
2 | 002 | Color | Green | Loser
3 | 003 | Color | Red | Loser
4 | 004 | Amount | 2ml | Control
5 | 005 | Amount | 4ml | Winner

Thanks in advance!

 

[jzwp22]

Experiment name, experiment date, experiment variable (color, amount), the values tested (red, green and yellow) and the results of each test.

I need some help understanding your experiments. I would think that for an experiment, you are testing many samples for a number of variables. Of the samples tested, one or more may be a control. So I am guessing here, but don't you need a sample table? For example, let's say you want to evaluate 3 paint samples against a control paint (4 samples one of which is a control) (i.e the experiment). All of the samples are submitted on standard panels. You are going to subject the 4 panels to UV exposure (i.e. the test). You will evaluate each sample for the following (your variables): chalking, fading, gloss.

Since a sample can have many variables and a variable can apply to many samples, you have a many-to-many relationship which is best handled with a junction table.

Based on the above discussion, this is how I would structure the tables:

tblExperiments
-pkExperimentID (Primary Key, autonumber)
-TestName
-DateofTest


tblVariables (this table just holds all possible variables that may be involved in an experiment)
-pkVariableID (Primary Key, autonumber)
-txtVariableDesc


Now a table to hold the samples involved in the experiment

tblExperimentSamples
-pkExpSamplesID primary key, autonumber
-fkExperimentID foreign key to tblExperiments
-txtSampleDesc

tblExperimentSampleVariables (the junction table relating samples & variables)
-pkExpSampVarID primary key, autonumber
-fkExpSamplesID foreign key to tblExperimentSamples
-fkVariableID foreign key to tblVariables
-result

The above table structure will change if you can conduct multiple experiments on 1 sample.