Hi LeAnne,

I think I understand the design. However, the measurement value field in
tblMeasurements will contain numeric data for some measurements, text data
for some measurements, and logical data for still others. I am not aware of
access's ability to mix these data types in the same field.

Thanks, and all the best,

LAF



"LeAnne" wrote:

Hi LAF,

Sorry, this is incorrect. Condition categories like age, sex, molt,
disease, etc. are *data*, not attributes (fields). The values recorded
for each category are also *data.* In a properly relational design,
tables expand down, not across. With a table design using conditions as
attributes, each time you want to add an additional measurement
category, you have to add a new field. All queries you have based on the
table will then have to be changed manually. How data are viewed or
exported should not affect table design. Tables are for *storing* data.
The design I suggested is normalized for a relational model. Use queries
to *display* the data in a wide-flat arrangement...look up "Crosstab
queries" in the Help file. You can export query recordsets as .xls or
..dbf files (File Save As/Export To an External File or Database),
which I believe SAS can handle easily.

L.

LAF wrote:

Hi LeAnne,

Thanks so much. I will get back to the group on the suggestions. However,
one point that could be made now. The condition data like age, sex, molt,
disease, etc, are really attributes. The sampling data are also really
attributes. The morphometric data are really the values. I am trying to
think of how the measurement table as you identify could be used for
statistical analysis. I frequently bring tables into the statistical program
S-plus, where I have the fields (columns) with BirdId, Species, Yr, and many
of the condition and morphometric fields. Even if the 1:1 table
relationships that I proposed have lots of fields with missing values, it is
easy either in access or in S-plus to just take the subset of records with
non-missing values.

Might it would be better to have the condition fields and the sampling
fields in the event table? I have to think of how I could get tables into
S-plus in which the rows are observations and columns are values.

Thanks,

LAF

"LeAnne" wrote:


Hi LAF,

You're very close. However, I suggest:

tblSites 1:m to tblEvents (since each site (subsite?) can be visited on
one OR MORE dates) on SiteID

tblEvents would have EventID as PK. EventID would be an autonumber
providing each unique combination of site+date+time (+capture type if
your are using more than one per event)

tblEvents 1:m to a BirdsCaptured table (since each event can capture one
OR MORE birds) on EventID

The BirdsCaptured table would be the junction table between tblEvents
and tblBirds (since a bird could conceivably be captured one OR MORE
times), and contain BOTH EventID and BirdID as key fields. NetNum could
also be included as a field if multiple nets are being deployed per event.

tblBirdsCaptured m:1 to tblBirds on BirdID (again, each individual bird
may be captured one OR MORE times)

tblBirds m:1 to tblSpecies (assuming that one OR MORE individuals in a
species have been banded) on SpeciesID

tblSpecies is the lookup table with taxon-specific information.

Now, if I read your explanation right, there are multiple parameters
measured for each bird captured (morphological characteristics, age,
genetics, &c.). Not all birds may be measured for all parameters. And
each time a bird is recaptured, some (but not all) parameters are
measured again. Correct? If so, then I would recommend that measurements
be combined into a single Measurements table, joined m:1 to
tblBirdsCaptured on BirdID + EventID. tblMeasurements would include the
fields EventID, BirdID, MeasurementID, and MeasurementValue.
tblMeasurements would be joined m:1 to a MeasurementLookup table on
MeasurementID with MeasurementName, MeasurementUnit, MeasurementCategory
(morpho, genetic, etc.), Description, MethodUsed, etc. included as
fields. Measurements are data, not attributes; by expanding measurements
down instead of across, you eliminate empty fields when a particular
parameter is not recorded for a particular bird captured in a particular
event.

Hope this helps,

LeAnne

LAF wrote:


Thanks to Tim, Leanne, and Allen for advice.

The original question about junction tables has become transmorgrified into
general database design. Here I will attempt to integrate and indicate the
aspects of design that I think are relevant. I will list the tables and some
fields with primary key as (PK) and foreign key as (FK):

event table: event.id (PK), bird.id (FK), subsite.id (FK), date, time,
capture.type, observer

bird table: bird.id (PK), species.id (FK), leftleg, rightleg

site table: subsite.id (PK), site, elevation, forest.structure

species table: species (PK), residency, listing

status table: event.id (PK), age, sex, disease, molt, fat, weight

morpho table: event.id (PK), bill, wing, tail, tarsus

sampling table: event.id (PK), bloodlog (FK), bloodquan, feather, pictures

genetics table: bloodlog (PK), event.id (FK), prepnum, malaria, cytb, chd

The major aspect of design here is that the 3 fields in the many to many
relationships (bird.id, subsite.id, date, time [actually 4 fields]) are
indicated by event.id as an autonumbered field. In this case, event.id
serves to indicate the unique combinations of the 4 fields. This is true
because the 4 fields could serve as a composite primary key. What I think is
important here is that the autonumbered field is meaningless except as a
primary key. I read somewhere that composite primary keys can cause some
problems in some queries.

The second aspect of design is that there are several one to one
relationships linked by event.id. In principle, all of these tables can be
merged into the event table, but not all individual birds have all data
taken. The event.id field in all of these additional tables will come from
the event table.

Many of my queries will involve looping over sorted recordsets, such as a
query that could add fields to the bird table such as date first captured,
date last captured. Or identifying individuals that were mis-sexed at
different events.

Happy New Year,

LAF