If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
table redesign
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? |
#2
|
|||
|
|||
table redesign
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" wrote in message ... 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? |
#3
|
|||
|
|||
table redesign
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. |
#4
|
|||
|
|||
table redesign
you're welcome
"inACCESSable" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|