Calculation thoughts...

[bridges05]

I am am a filemaker beginner who is also learning databases in general for the first time as I go. I have completed a couple sucessful multi-relational databases (simple ones) so far and am trying my hand at another. I work for an archaeology company and am trying to design a database for our faunal bone collection. Right now I have three tables:

Sites-->Specimins-->Cuts. Both of these relationships are one to many. What I would like to do now is create a calculation that automatically fills in taxonomy when you enter the common name for a specimin. So when we enter "Cow" under common name (specimin table) it automatically fills in the kingdom, phylum, order...genus and species. We are usually only working with a certain amount of common mammals found in southern ca...still I realize this is a lot data to manipulate. Does anyone know how I should start? Is it best to have a seperate taxonomy table and use lookup values when the common name matches? or should I use value lists? Has anyone tackled a similar problem?

thanks in advance for any advice.

[Raybaudi]

Hi

so you have something like this in the taxonomy table:

Natura - nature 

   Mundus Plinius - material world 

     Tellusque - earthly 

       Naturalia 

         Biota 

           Domain Eukaryota - eukaryotes 

             Kingdom Animalia Linnaeus, 1758 - animals 

               Subkingdom Bilateria (Hatschek, 1888) Cavalier-Smith, 1983 - bilaterians 

                 Branch Deuterostomia Grobben, 1908 - deuterostomes 

                   Infrakingdom Chordonia (Haeckel, 1874) Cavalier-Smith, 1998 

                     Phylum Chordata Bateson, 1885 - chordates 

                       Subphylum Vertebrata Cuvier, 1812 - vertebrates 

                         Infraphylum Gnathostomata auct. - jawed vertebrates 

                           Superclass Tetrapoda Goodrich, 1930 - tetrapods 

                             Series Amniota 

                               Mammaliaformes Rowe, 1988 

                                 Class Mammalia C. Linnaeus, 1758 - mammals 

                                   Subclass Theriiformes (Rowe, 1988) M.C. McKenna & S.K. Bell, 1997 

                                     Infraclass Holotheria (Wible et al., 1995) M.C. McKenna & S.K. Bell, 1997 

                                       Superlegion Trechnotheria McKenna, 1975 

                                         Legion Cladotheria McKenna, 1975 

                                           Sublegion Zatheria McKenna, 1975 

                                             Infralegion Tribosphenida (McKenna, 1975) M.C. McKenna & S.K. Bell, 1997 

                                               Supercohort Theria (Parker & Haswell, 1897) M.C. McKenna & S.K. Bell, 1997 - therians 

                                                 Cohort Placentalia (Owen, 1837) M.C. McKenna & S.K. Bell, 1997 - placentals 

                                                   Magnorder Epitheria (McKenna, 1975) M.C. McKenna & S.K. Bell, 1997 

                                                     Superorder Preptotheria (McKenna, 1975) McKenna, in Stucky & McKenna, in Benton, ed., 1993 

                                                       Grandorder Ungulata (C. Linnaeus, 1766) McKenna, 1975 

                                                         Mirorder Eparctocyona McKenna, 1975 

                                                           Order Artiodactyla Owen, 1848 - even-toed ungulates 

                                                             Suborder Ruminantia Scopoli, 1777 

                                                               Superfamily Bovoidea (Gray, 1821) Simpson, 1931 

                                                                 Family Bovidae™  Gray, 1821 - antelope, cattle, bison, buffalo, goats, and sheep 

                                                                   Subfamily Bovinae™  (Gray, 1821) Gill, 1872 

                                                                     Tribe Bovini™  (Gray, 1821) Simpson, 1945 

                                                                       Genus Bos™  Linnaeus, 1758 - oxen

from: http://taxonomicon.taxonomy.nl

add an ID, species, sub-species, variety ( primigenius taurus ? ) and an english unique name ( cow ) and go on with relationship

Get even more informations from: http://www.ncbi.nlm.nih.gov/Taxonomy/

Other names:

synonym: Bos primigenius taurus

synonym: Bos bovis

common name: cow

common name: bovine

common name: domestic cattle

common name: domestic cow

[Aaron Briggs]

For what its worth I would go with a separate table,,,,

[Raybaudi]

Ofcourse, Taxonomy table is a separate one.

[bridges05]

Hey sorry I am a little slow with this. Actually, I am very very slow. Just to clarify: you think I should create a seperate table that has all of the taxa fields (genus, species, etc etc). I should then create a seperate table (with ID and common name) and relate the two? Or is the Taxa table (which also had a species ID and a common name field) related to my original specimin table? Either way, isn't this a one-to-one relationship that I am supposed to avoid? Thanks so much for spending time explaining this me:)