Easy table design question

[Chris]

Hi,

I'm sure this will be easy for you folks to sort out and I would be
grateful for some help:

I work in a lab and I need to use Access to store some data. So, for
a particualr lab experiment event I want to store the following data
items:

StaffName
Date
Time
EquipmentID

and then for each lab experiment event I have results data that I need
to store:

Result1
Result2
Result3 etc.

From what I understand about table design I should probably have two
tables (one for each 'subject') i.e. a table for the 'experiment
event' and a table for the results. I'm not quite sure if this is
correct and if so what should I use as the primary and foreign keys in
each table.

Thanks,

Chris.

 

[Jeff Boyce]

Chris

I'm not entirely clear what the relationship is among your "entities" (and
this is critical to designing a well-normalized data structure).

Are you saying that one Experiment can have many results? If so, does that
mean you have a set of conditions that, together, uniquely define one
experiment, which is then run multiple times, each time producing
independent results?

If so, it sounds like you have a table that describes each unique
experimental condition set, and a related (child) table that holds one row
for each result related to a unique row in the Experiment table. A rough
design would look something like:

tblExperiment
ExperimentID
Height
Weight
Age
(... you didn't specify what settings are used <g>)

trelResults
ResultsID
ExperimentID (foreign key, from tblExperiment row)
TimeToCompletion (again, you didn't specify what was being measured)

Now, if the relationship is more complex, you'll need a more complex table
structure. If, for example, different settings are used for each
Experiment, you'll need a one-to-many relationship between a given
Experiment and it's (multiple) settings (another table).

And if you are measuring results for multiple criteria (rather than one
criterion, measured many times), you'll need a one-to-many relationship
between Experiment and Result, and a CriterionType added to each result.

This could get complex!

 

[John Vinson]

Hi,

I'm sure this will be easy for you folks to sort out and I would be
grateful for some help:

I work in a lab and I need to use Access to store some data. So, for
a particualr lab experiment event I want to store the following data
items:

StaffName
Date
Time
EquipmentID

and then for each lab experiment event I have results data that I need
to store:

Result1
Result2
Result3 etc.

From what I understand about table design I should probably have two
tables (one for each 'subject') i.e. a table for the 'experiment
event' and a table for the results. I'm not quite sure if this is
correct and if so what should I use as the primary and foreign keys in
each table.

You'll need more tables than that.

At the very least I'd see the following tables:

Staff
StaffID
LastName
FirstName
<other bio information as needed>

Equipment
EquipmentID
Description
<other fields describing this piece of equipment>

Events
EventID <autonumber probably>
EventDate ' just use one date/time field, not separate date and time
' fields, and DON'T use the reserved names DATE or TIME
StaffID
EquipmentID

Results
ResultID <autonumber>
EventID <long integer, link to Events>
ResultValue
<maybe other fields, e.g. result type, comments, confidence limit>

 

[Chris]

Thank you gentlemen for your very detailed responses which have made
me think and define my problem with more clarity.

Generically my situation is this:

An experiment with a piece of equipment produces a trace. Different
settings on a piece of equipment produce different traces. Different
dimensions on the trace are then measured (literally in millimetres).

From your disscussion on table design I think I need the following
tables:

tblEVENT (if I keep this table very generic then I think I can use it
for other laboratory "events" that I want to deal with later)
EventDateTime
EventType (e.g. make a trace)
EquipmentUsed (let's say we use equipmentA)

tblEQUIPMENT_A
EquipmentA_setting1
EquipmentA_setting2

tblTRACE
DateProduced
DateAnalysed
OtherTraceSpecificData1
OtherTraceSpecificData2

tblTRACE_MEASUREMENTS
DimensionX
DimensionY
DimensionZ
Need something else in here that identifies the machine
settings

So all I need to know now is how to assign the primary and foreign
keys. A Date/Time combination might be one possibilty or a unique ID
number.

Would be very grateful for your comments

Chris.