17 Views and Materialized Views

17.1 About Views (Briefly)

Neotoma manages its core data in highly normalized data tables within the ndb schema. The highly normalized structure of thi data helps to reduce conflicts across the database and helps reduce data duplication. A challenge with normalization is that complex queries require a set of JOIN arguments that may be time intensive.

To help reduce the time required to perform these queries we can store query plans for joining these tables, called VIEWS, or run the queries ahead of time and store these results in MATERIALIZED VIEW. Using these structures means that we can reliably query these structures as if they were tables, reducing the amount of time required to return a result set, and improving the users’ experience.

17.2 Neotoma Views

17.2.1 Schema ap

datasetpisauthors taxaindatasetview datasetkeywords

17.2.2 Schema da

contacts datasetbytaxonname vcubestchron vsampagesstd vbestdatasetages datasets datasets_last geopoliticalunits publications vbestsampleages

17.2.3 Schema ‘db’


17.2.4 Schema ndb

ndb dsageranges ndb dsdatasample ndb dslinks ndb dssampdata ndb geopoldepth ndb pubtsv

17.2.5 Schema ti

ti dslinks ti geopol1 ti geopol2 ti geopol3 ti geopol4

17.2.6 Schema ts

ts stewardauthorization

17.3 Neotoma Materialized Views

17.3.1 Schema ap geopolnames

This materialized view is used to compress the geopolitical units for a site into a single array to support enhanced search functionality. Currently the geopolitical search must perform a hierarchical search through ndb.geopoliticalunits. The goal of this materialized view is to add the geopolitical “chain” for any site to the table so we can always find the best match to any country, state/province or lower level geopolitical search.