Design for ongoing assessments

[Angela]

I am trying to build a database to store various
measurements of plants, done over a period of time.

I have a table for the plants, which specifies the
location of each plant and its identity.

PlantID, Rep, Row, Column, PlantName

The plants will be measured at various intervals, and what
will be measured will not always be the same. For
example, the first assessment may just be a survival
(yes/no). The second assessment may be a height and a
condition code. The third may be height, condition, and
damage. I don't know ahead of time what the variables
will be.

I think that I should have two additional tables; one for
the measurements, and another for the actual values.

Measurements table
measurementID
measurement description

Assessments table
PlantID
MeasurementID
Assessment Date
Value

This would be normalized, but it seems awkward. The value
column will have integers, numbers with decimals, text,
etc.;quite a mix. I guess I'd have to make it a text
column, but then how can I do any statistical analysis on
say the height measurements?

Is this the way to design it?

Angela

 

[Adrian Jansen]

There is a rather nice survey database at

http://www.rogersaccesslibrary.com/duanehookom/duanehookom.htm.

which uses similar ideas.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Angela]

Thank you for the link, however it didn't really help me.
Angela

 

[Steve Schapel]

Angela,

Your suggested design seems fine to me. When it comes to doing
calculations on the numerical values in the text field, you could use
functions such as Val(), CInt().

- Steve Schapel, Microsoft Access MVP

 

[John Nurick]

Hi Angela,

I suspect you need at least one more table. Possibly something like
this:

Plants (as now)

Measurements (each possible measurement you can make, e.g. "Survival",
"Height")
MeasurementId
MeasurementName
Description
Units (or data type: e.g. "Yes/No", "mm", "Condition",
"Integer"; having this field simplifies data validation
and display)

Assessments (e.g. "First", "Second"))
AssessmentId
AssessmentName
Description
Possibly there should be fields here to indicate
the schedule; or possibly this should be implemented
with yet another table, or isn't needed at all.

AssessmentsPlants (implementing the many-to-many relationship
between Assessments and Plants)
PlantID
AssessmentID
DateStamp
PersonID (do you need to track who measures what?)

AssessmentsMeasurements (implementing the many-to-many relationship
between Measurements and Assessments)
PlantID
AssessmentID
MeasurementID
Value