SFS database design
Home     Manual

Design details and issues

This portion of the Internet site contains many technical details and unresolved issues.  It is intended primarily for those involved in the design and development of Species File Software.  Other interested persons are welcome to review these pages.  Unresolved design issues are shown in contrasting color.  Version specific features are noted by highlighted text.  Comments, criticisms and suggestions are welcome from casual readers as well as from members of the design group.  The input will help us to produce a better service.  Send comments to David Eades (send mail).

The narrative below is divided into Access levels, Software selected, Table relationships, Table structure, and URL entry points.  A separate page describes the treatment for taxon names and concepts.

Access levels

Species File Software provides four different levels of access using the Internet.  The first is open to the public; the other three require logging in with a password.

1. Information contained in species files is available to the public except for a small number of items that carry restricted access codes.  (The restricted access codes are used primarily for software testing purposes, but can be used for unpublished information.)
2. Edit access is available to taxonomists and others who add and modify the data.
3. Administrative access allows additional functions and requires greater familiarity with the internal database structure.
4. Secure access provides the ability to manage the access levels of other users and information useful for software development.

Software selected

The database behind the website was originally on FileMaker Pro, moved to MS Access, moved again to MS SQL Server.  The greater complexity of SQL Server has made the task more difficult, but it has allowed the addition of more complex programming that does a better job of detecting and preventing user errors in updating the data.

Programming for the Orthoptera Species File was initially done with MS Visual Interdev.  The web pages are published using HTML 4.  The Active Server Pages (ASP) use Visual Basic Script.  Client side programming is done using JavaScript.  Web pages can be viewed using Microsoft Internet Explorer, version 4 and higher, or Netscape Navigator, version 4 and higher.  Current work is done using Visual Studio.NET.  The conversion from ASP to ASP.NET and from Visual Basic Script to Visual Basic.NET was completed in November 2003.

Table relationships

• Citation related tables
• Ecological relationships related tables
• Image and sound related tables
• Key related tables
• Link related tables
• People related tables
• Scrutiny related tables
• Source related tables
• Specimen I related tables
• Specimen II related tables
• Taxon I related tables
• Taxon II related tables

Table structure

• Data about taxa
  • tblTaxa, taxon names at all ranks, including synonyms.
  • tblRelatedTaxa, linking table to record nomenclatural relationships between taxonomic names.
  • tblTaxonHistory, history of concepts and nomenclature for protonyms added in version 4.0
  • tblRelatedHistory, definition of values used in tblTaxonHistory.RelatedHistoryID, shared across files added in version 4.0
  • tblRanks, ranks in the taxonomic hierarchy, shared across files.
  • tblSupplTaxonInfo, supplementary information about taxa.
  • tblExperts, information about experts familiar with taxa.
  • tblSources, references to sources of images, sound recordings and other data.
  • tblCommonNames, common names for taxa.
  • tblLanguages, the language used for common names.
  • tblTypeGenera, citation to genus that is type for a family-group name and to oldest use of a name based on the same genus.
  • tblTypeSpecies, citation to species that is type for a genus-group name.
  • tblTypeSpeciesReasons, reasons a type species has been validly designated, shared across files.
  • tblTypeInfo, effects of citations on types, shared across files.
  • tblDistribution, recorded occurences in geographic areas, added in version 3.5
• Data about specimens
  • tblSpecimens, information about specimens.
  • tblSpecimenCategories, categories of specimens (male, female, egg, etc.) added in version 4.0
  • tblSpecimenCounts, counts for various specimen categories added in version 4.0
  • tblDepos, depositories that hold specimens.
  • tblSpecimenStatus, lost, lost?, missing, missing?, etc., shared across files.
  • tblSexes, sex and maturity classification for specimens, shared across files.
  • tblIdentifications, record of identifications on labels or in publications.
  • tblTypeKinds, holotype, lectotype, neotype, etc., shared across files.
  • tblLocalities, information about localities where specimens were found including geologic time period.
  • tblGeoLevel1, continent or other major part of the world, shared across files.
  • tblGeoLevel2, region or large country, shared across species files, shared across files.
  • tblGeoLevel3, country or portion of large country, shared across files.
  • tblGeoLevel4, county or other smaller area, shared across files.
  • tblGazetteer, listing of names and the corresponding values in the preceding four tables, used with human data entry, shared across files.
  • tblLandCoords, polygon lists for TDWG areas
  • tblSeaCoords, polygon lists for oceanic TDWG areas
  • tblGeoConvert1, 2, 3, 3A and 4, data used to help convert localities to standardized codes in automated process. should be shared across species files.
  • tblTDWGtoITIS, for conversion of the TDWG geo classification to the ITIS geo classification. should be shared across species files.
  • tblTimePeriods, geologic time period, shared across files.
  • tblCollectEvents, information about the date and collector.
• Data about images, sounds and ecology
  • tblImages, images of specimens and other items.
  • tblMIME (Multipurpose Internet Mail Extension code), supported image types, shared across files.
  • tblImageTypes, type of image.
  • tblSounds, sound recordings.
  • tblEcoRoles, ecological roles
  • tblEcoRelationships, ecological relationships
  • tblOtherEcoTaxa, taxa in ecological relationships but outside the primary scope
• Data about literature citations, etc.
  • tblAddrCountries, a list of countries used in the address book.
  • tblAddrStates, a list of states used in the address book.
  • tblCites, citations to a reference, associated with a specific taxon.
  • tblCiteContentFlags, flags that identify information contained in citations, shared across files, to be deleted in version 4.0 and replaced by the next three tables
  • tblConceptChanges, kind of changes in taxon concepts, shared across files, added in version 4.0
  • tblNewNameStatus, types of status for names, shared across files, added in version 4.0
  • tblCiteInfoFlags, kind of information contained in citations, shared across files, added in version 4.0
  • tblRefs, references found in publications.
  • tblRefAuthors, linking table to match references with authors.
  • tblPeople, names of people (mainly authors) including misspellings, etc.
  • tblPeopleReg, registry of data about authors shared across species files, shared across files, for future development
  • tblPendingPeopleFiles, a linking table used to track the reconciliation of data in tblPeopleReg and the separate tblPeople in different species files, shared across files, for future development
  • tblPubs, names of publications including misspellings, etc.
  • tblPubReg, registry of data about publications, shared across files, for future development
  • tblPubRegDetail, items that may have multiple values for a single registry pub, shared across files, for future development
  • tblPendingPubFiles, linking table used to track the reconciliation of data in tblPubReg and the separate tblPubs in different species files, shared across files, for future development
  • tblPubSuccession, relationship between preceding and succeeding publications, for future development
  • tblNomenclator, name combinations at rank of genus or lower.
  • tblGenusNames, generic name for lookup in tblNomenclator.
  • tblSpeciesNames, species name for lookup in tblNomenclator.
  • tblScrutinies, years and comments from persons working on this database.
  • tblTaxonScrutinies, linking table to match taxa with scrutinies.
  • tblScrutinyAuthors, linking table to match scrutinies with persons.
• Data for keys
  • tblKeyGroups, groups of one or more taxa that can be separated by using a key block.
  • tblGroupTaxa, subordinate to tblKeyGroups, included or excluded members of a group based on a taxon listed in tblKeyGroups.
  • tblKeyBlocks, decision blocks for working through keys.
  • tblKeyChars, pool of characters available for use in key blocks.
  • tblCharTypes, types of characters, for future development
  • tblKeyCharStates, subordinate to tblKeyChars, description of each character state for each character.
  • tblCharStateDetails, subordinate to tblKeyCharStates, display details for character states.
  • tblKeyBlockChars, linking table to match blocks and characters.
  • tblKeyBlockBranches, subordinate to tblKeyBlocks, list giving group selected for each branch.
  • tblBlockCharStates, linking table to match block character (specific to block) with key character (pool of all characters).
  • tblBranchCharStates, linking table for block to match branch with character state.
• Data about log entries
  • tblEditLog, log of entries that alter the content of tables in species file databases.
  • tblSFSeditLog, log of entries that alter the content of tables in SFSdb (the shared database), shared across files.
  • tblTestLog, log of test program runs and results.
• Data for relationships outside of species files
  • tblLinks, hyperlinks to other websites with information about a taxon.
  • tblWebsites, information about other websites.
  • tblTxnNmLSIDs, information about LSIDs for scientific taxon names
  • tblTConceptLSIDs, taxon concept LSIDs
  • tblDeletedTNameIDs, deleted taxon name IDs
  • tblForeignLSIDs, LSIDs issued by other entities such as Species 2000 and ZooBank
• Miscellaneous
  • tblConstants, constant values to be referenced by programs.
  • tblGlossary, words that are recognized in a glossary search.
  • tblFiles, data about the status of various species files, shared across files.
  • tblFileTypes, types of database files based on how they are used.
  • tblSources, sources of data in tables in SFSdb.
  • Temporary tables used for export to the Catalogue of Life

URL entry points

The recommended entry point is "http://[SpeciesFileName].SpeciesFile.org".  Replace "SpeciesFileName" with the name of the taxonomic group such as "Orthoptera."  In addition there are special entry points provided primarily for use in links from other websites, but available to anyone.  When a specific taxon is desired, and the rank is subgenus or higher, use "http://[SpeciesFileName].SpeciesFile.org?TaxonName=" plus the name of the taxon.  When the desired taxon is below the rank of subgenus, use "http://[SpeciesFileName].SpeciesFile.org?Genus=[genus name]&Species=[species name]&Subspecies=[subspecies name]".  Any of these parameters may be omitted.  Replace "[xxx name]" with the actual name.  If a space is included within a name, quotation marks must be placed around the name.  For those who coordinate data with a TaxonNameID, an additional entry point is "http://[SpeciesFileName].SpeciesFile.org?TaxonNameID=" plus the value of the TaxonNameID.

Some additional entry points are supported, either because they were used in older versions or because some users store bookmarks in their browsers.  Although these other entry points work now, there is no assurance that they will continue to work in the future.