|
|
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?
|
|
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" <inACCESSable[ at ]discussions.microsoft.com> wrote in message
news:E81012A0-FF84-4518-9E52-7E3F79B1D2B7[ at ]microsoft.com...
[Quoted Text] > 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?
|
|
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.
|
|
you're welcome
"inACCESSable" <inACCESSable[ at ]discussions.microsoft.com> wrote in message
news:1721DB20-5A67-47B5-8C47-B66DEC334FA7[ at ]microsoft.com...
[Quoted Text] > 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.
>
>
|
|
|