table redesign

[inACCESSable]

i thought i posted this yesterday, but i can't find the post, sorry if this
is repetitive.

i have a table for tree demography info. each record has a tag id, census
date, stage, and size. there are 1700 trees and 5100 records. instead of
having multiple records with a unique key for each, i want the tree tag id to
be the key. i know this runs counter to the idea of a key, but it's easier
for me to see tree data.

to further complicate things, depending on stage (juvenile, adult), the tag
id changes. would i be able to records to a different key/tag id when the tag
id changes?

 

[tina]

well, assuming that 1) a specific tree is a specific tree, regardless of
what stage it's in (just as a person is a person, whether child or adult),
and that 2) you want to know what census data belongs to what stage of
growth, for each tree - then i might use the following table setup, as

tblTrees
TreeID (primary key, probably Autonumber)
TreeDescription
<rather than a "description" field, you may need several fields to identify
a tree, such as the kind of tree (ash, pine, maple, whatever), location
(where is the tree? and a separate table of locations may be appropriate, to
list all the places you track trees, with a foreign key field in tblTrees to
link each tree to a location), etc. just make sure that you store the data
atomically in tblTrees, not strung together in a "narrative" field.>

tblTreeTags
TagID (primary key)
TreeID (foreign key from tblTrees)
Stage
<suggest you set a multi-field unique index on fields TreeID + Stage. you
could dispense with the TagID field altogether, and use the other two fields
as a multi-field primary key; personally, i don't use multi-field primary
keys when the key will be used as a foreign key in another table, as in this
scenario, so i recommend going with all three fields listed above.>

tblCensus
CensusID (primary key, autonumber)
TagID (foreign key from tblTreeTags)
CensusDate
Size

you don't need a Stage field in tblCensus, because each TagID has a specific
stage associated with it in tblTreeTags; when the stage of a tree changes,
so does the tag id, so each tree record in tblTrees may have multiple
associated records in tblTreeTags - one record for each tag id that is
assigned to a tree in it's "lifetime". btw, this setup will also support
possible expansion of stages - someday, you may find yourself needing to use
more than stages "juvenile" and "adult". also, with this setup, you can
trace a single tree's growth in all stages of life, as well as compile
statistics on trees in a specific stage, and/or a specific time frame (using
the CensusDate field).

hth

 

[inACCESSable]

thanks for your response, tina. i will work with what you suggested.

also, i found the other post (something weird, can't see a lot of
posts/replies) and a response there seemed promising. i will mess around with
these ideas.

 

[tina]

you're welcome