NBMG GEOTHERMAL GEOCHEMICAL DATABASE
SCHEMA AND DEFINITIONS
As of 5/30/06
TABLE SITES
SITE_IDIntegerPrimary Key
LatDD83DoubleLatitude, decimal degree, NAD27?
LonDD83DoubleLongitude, decimal degree, NAD27?
TypeIntegerSpring, well, fumarole, etc.
Temp_DescrTextCold (<20C), warm (20-37C), hot (>=37C)
NameTextMost-used name of spring
AreaTextGroup (can be 1 ("Kyle") or multiple as in "Steamboat Springs")
CountyTextCounty
StateTextTwo letter state abbreviation
TownshipSingleNumber
TownNSTextN or S only
RangeSingleNumber
RangeEWTextE or W only
SectionIntegerSection (1-36)
1/4SecTextQuarter section or more (we need to adopt single convention)
MeridianTextCadastral coordinate meridian
7 ½ QuadrangleText7.5 minute quad the site occurs within
ElevationIntegerMeters above sea level
HoledepthDoubleDepth of hole, if it is a well
AquiferTextName of Aquifer the spring or well taps into
TABLE SITEMANY('Many' in one to many relationship with SITES)
SITE_IDIntegerPrimary Key to Sites Table
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
SITEMANY_IDIntegerPrimary Key
CHEM_IDIntegerTo cue temperature/flow rate data to a sample: OPTIONAL
DateDateDate of reading
TempCDoubleTemperature reading in degrees Centigrade
DateDoubleDate temperature (or comment, flow rate…) taken
FlowRateDoubleFlow rate in liters per minute (if Site is a spring)
HoleDepthDoubleTotal Depth of hole in meters (if Site is a well)
SampleDepthDoubleSample depth of hole in meters (if Site is a well)
TABLE SITEEXTRAS('Many' in one to many relationship with SITES)
SITEIDIntegerPrimary Key to Sites Table
SITEEXTRAIDIntegerPrimary Key
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
TextTextContains data for whatever item is in ‘Item’ Field
TypeTextType of information stored in ‘Text” field (Name, etc.)
The above table is going to contain information on the following fields:
Hot Spring Name
Mapname - Mapscale
TABLE SITECOM
SITE_IDIntegerPrimary Key to Sites Table
SITECOMIDIntegerPrimary Key to SITECOM Table
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
Comment TypeTextType of comment (“Location”, temperature, flow rate, etc.)[i]
CommentTextComment
TABLE CHEM('Many' in one to many relationship with SITES)
SITE_IDIntegerPrimary Key to Sites Table
CHEM_IDIntegerPrimary Key
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
DATEDateDate sample collected (or, whatever date in database…)
TIMEDate/TimeTime sample collected
MediumTextMedium sampled (water, steam, gas)
CollectedByTextPerson or group taking the sample
AnalystTextPerson or group analyzing the sample (drop down?)
PointofCollectTextSpot at site where the sample was collected
Lab NumberTextLab sample number
OrigLatDoubleOriginal latitude of sample, DDNAD83[ii]
OrigLonDoubleOriginal longitude of sample, DDNAD83ii
OrigFileTextName of original file data came from (or “hand-entered”, etc)
FiltersizeTextFilter size, in microns, or description such as "unfiltered"
ReferenceTextShort reference, which is also the key to Reference lookup table
TempCDoubleTemperature in Centigrade
Alkalinity-labDoublemg/l as CacO3 in lab[iii]
Alkalinity-fieldDoublemg/l as CacO3 in fieldiii
Alkalinity TypeTextDescription of alkalinity
Alkalinity TotalDoubleTotal alkalinity, incremental titration, mg/l as CaCO3
Conduct FieldDoubleSpecific Conductivity field, microseimens/cm at 25o C, unfilterediii
Conduct LabDoubleSpecific Conductivity lab, microseimens/cm at 25o C, unfilterediii
Conduct UnkDoubleSpecific Conductivity, unknown method, “ “ unfilterediii
Turbidity-labDoubleLab meas. In Nephalometric Units, unfilterediii
Turbidity-fieldDoubleField meas. In Nephalometric Units, unfilterediii
Turbidity-unkDoubleUnknown method in Nephalometric Units, unfilterediii
HardnessDoublemg/l as CaCO3, filterediii
HardnessDoublemg/l as CaCO3, unfilterediii
Non-Carb HardDoublemg/l as CaCO3
pHFieldDoublepH of sample taken in field at time of samplingiii
pHLabDoublepH of sample taken at labiii
pH PaperDoublepH of sample using pH paper taken in field at time of samplingiii
eH-fieldDoubleMillivoltsiii
eH-labDoubleMillivoltsiii
TDSMDoubleTotal Dissolved Solids (measured), mg/liii
TDSCDoubleTotal Dissolved Solids (calculated), mg/liii
TDSc EquationDoubleTotal dissolved solids using some equation (?), mg/liii
Specific GravityDoubleSpecific gravity, g/cm3
NaDoublemg/l filtered
KDoublemg/l filtered
CaDoublemg/l filtered
MgDoublemg/l filtered
SiO2Doublemg/l filtered
AsDoublemg/l filtered
BDoublemg/l filtered
BaDoublemg/l filtered
FDoublemg/l filtered
FeDoublemg/l filtered
H2SDoublemg/l filtered
LiDoublemg/l filtered
MnDoublemg/l filtered
SiDoublemg/l filtered
Si (total)Doublemg/l filtered
Si02 (total)DoubleTotal silica, mg/l filtered
SrDoublemg/l filtered
BrDoublemg/l filtered
ClDoublemg/l filtered
OHDoublemg/l filtered
PO4Doublemg/l filtered as P
SO3Doublemg/l filtered
SO4Doublemg/l filtered
CO3Doublemg/l filtered
CO3 FieldDoublemg/l filtered field carbonate
CO3 CorrDoublemg/l, filtered, with steam correction
HCO3Doublemg/l filtered bicarbonate
HCO3 FieldDoublemg/l filtered field bicarbonate
HCO3 LabDoublemg/l filtered lab bicarbonate
Cation GeoDoubleCalculated cation geothermometer temperature, degrees C
Silcia GeoDoubleCalculated silica geothermometer temperature, degrees C
Avg GeoDoubleAverage of cation and silica geothermometer[iv]
NOTE: All the data in CHEMMORE is currently duplicated in the CHEM Tableiii
TABLE CHEMMORE(Many in 1 to many relationship with CHEM)
CHEM_IDIntegerPrimary Key to Chem Table
CHEMMOREIDIntegerPrimary Key
VALUEDoubleValue for whatever Type field is (“3.7”)
ItemTextField name for which Value is assigned to (“Alkalinity”)
TypeTextDescriptor for Item (“Field”, “Lab”, etc.)
DateDate/TimeDate listed in the original reference
The above fields are going to contain information on the following fields:*
HardnessDoubleHardness, mg/l
HardTypeText“Lab”, “Non-carbonate”, etc.
pHDoublepH reading, whatever type, pH units
pHTypeText‘Lab’, ‘field’, or ‘unknown’
AlkalinityDoubleAlkalinity value, whatever type
AlkalinityTypeText‘Lab’, ‘Field’, or whatever text is in there (e.g. “CaCO3”)
EhDoubleeH reading, millivolts
EHTypeText‘Lab’, ‘field’, or ‘unknown’
TurbidityDoubleTurbidity reading, Nephalometric Units, unfiltered
TurbidityTypeText‘Lab’, ‘field’, or ‘unknown’
SpecCondDoubleSpecific Conductivity, microseimens/cm at 25o C, unfiltered
SpecCondTypeText‘Lab’, ‘field’, or ‘unknown’
TDSDoubleTotal Dissolved Solids, mg/l
TDSTypeTextCalculated or measured
SourceDoubleSource of Sample (drop down: Stack, Counter, NBMG, etc.)
SourceNo.IntegerNumber associated with source
TABLE CHEMCOM
CHEM_IDIntegerPrimary Key to Chem Table
CHEMCOMIDIntegerPrimary Key
Comment TypeTextDrop down box with discrete type: “Point of Collection”, etc.
CommentTextComment
TABLE TRACE(Many in 1 to many relationship with SITE)
SITE_IDIntegerPrimary Key to Sites Table
TRACE_IDIntegerPrimary Key
CHEM_IDIntegerPrimary key to tie this sample to data in the chem. table
DATEDate/TimeDate listed in original reference, presumably sample date
Al
Al_FiltersizeDoubleSize, in microns, of filter
Al3 (02)DoubleAl3 value, mg/l, after filtering through a 0.2 micron filter
Al3 (045)DoubleAl3 value, mg/l, after filtering through a 0.45 micron filter
AgDoubleAll the trace elements below are in mg/l
AuDoublemg/l, filtered
BeDoublemg/l, filtered
BiDoublemg/l, filtered
CdDoublemg/l, filtered
CrDoublemg/l, filtered
CsDoublemg/l, filtered
CuDoublemg/l, filtered
DyDoublemg/l, filtered
EuDoublemg/l, filtered
Fe1Doublemg/l, filtered
Fe2Doublemg/l, filtered Fe 2+
FettlDoublemg/l, filtered Fe 2+ + Fe3+
GaDoublemg/l, filtered
GeDoublemg/l, filtered
HgDoublemg/l, filtered
IDoublemg/l, filtered
MoDoublemg/l, filtered
NdDoublemg/l, filtered
NiDoublemg/l, filtered
PbDoublemg/l, filtered
RbDoublemg/l, filtered
SDoublemg/l, filtered
SbDoublemg/l, filtered
ScDoublemg/l, filtered
SeDoublemg/l, filtered
SnDoublemg/l, filtered
ThDoublemg/l, filtered
TiDoublemg/l, filtered
TlDoublemg/l, filtered
UDoublemg/l, filtered
VDoublemg/l, filtered
WDoublemg/l, filtered
ZnDoublemg/l, filtered
ZrDoublemg/l, filtered
(Gas portion of table)
ArDoubleArgon
Ar_dissDoubleArgon. dissolved
CH4Double
CH4dissDouble
C2H6Double
CO2Double
CO2_dissDouble
H2Double
H2_dissDouble
H2S_aqDouble
HeDouble
O2Double
N2Double
N2_dissDouble
NO2Double
NO3 (N)Double
NO3 FieldDouble
NH3Double
NH3 FreeDouble
NH4 (N)Double
NH4 FieldDouble
S2O3Double
TICDouble
TABLE ISOTOPE
SITEIDIntegerPrimary Key to Site Table
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
ISOTOPEIDIntegerPrimary Key
CHEMIDIntegerPrimary Key to Chem Table
DateDate/Time
C13Doouble
C13 in CO2Double
C14DoublePercent modern
C14 AppDoubleApparent age in years
DeuteriumDouble
TritiumDouble
O18/O16 H20Double
O18/O16 CO2Double
O18/O16 S04Double
NH3 FreeDouble
S 34/32DoubleIsotope ratio per mil
S 34/32 SO4DoubleIsotope ratio per mil in sulfate
AlphaRadioDoubleAlpha radioactivity in mg/l
BRadioCSDoublepicograms per liter Cesium
BRadioSrDoublepicograms per liter Strontium
N15/N14DoubleIsotope ratio, per mil
RadonDoublepictogram (or picocuries?) per liter
TABLE ISOPTOPEALPHA
ISOALPHAIDIntegerPrimary Key to Isotope Table
SITEIDIntegerPrimary Key
OLDSITEIDIntegerTemporary key until we’re sure we removed sites correctly
NWISIDIntegerTemporary key for determining units
DATEText
TIMEText
AlphaTextAlphanumeric assoc with record (“<”, “>”, “below detection”, etc)
ValueDoubleValue
IsotopeTextName of isotope
TABLE ALPHAOne to many relationship with CHEM and TRACE tables
CHEM_IDIntegerPrimary Key to Chem Table
ALPHAIDIntegerPrimary Key
Plus fields as described below
This table contains records where there was a problem with analysis of compounds found in the CHEM and TRACE tables. Usually the values are below detection limits of the equipment. In the table are two fields for each compound: the value (if any) and a text ‘alpha’ field showing the flag associated with each record (“<”, “>”, “below detection”, etc). The table has these pairs for Ag, Al, As, Au, B, Ba, Be, Bi, Br, Ca, Cd, Cl, Co, Fe total, Flowrate, Cr, Cs, Cu, C2H6, CO3, C13, CH4, F, Fe, Ga, Ge, H2, H2S, Hg, I, K, Li, Mg, Mn, Mo, NH4, Ni, NO3, Pb, PO4, Rb, Re, Sb, Se, AlkTtl, Conductivity, He, HCO3, Hardness, Na, N2, O2, O2UF, pH Field, pH Lab, SpecGrav, Turbidity, Tritium, S, SO2, S2O3, SO3, SO4, SiO2, Sr, Sulfide, SulfideH2S, SulfideUF, Th, Ti, Tl, U, V, W, Zr
TABLE GETREFSLookup table between CHEM and REF table
IDIntegerKey to table
CHEMIDIntegerCHEM table key
REFIntegerKey to REF table
TABLE REFSContains long (bibliographic) references keyed to ‘ref’ in CHEM
REFIntegerPseudo-primary key for each reference
FAuthorTextFirst author with [second author or "others"] and date
AuthorsTextList of all the authors, in last name + initial(s) format
TitleTextTitle of Text
YearTextYear of Publication
PubTextPublication name, volume, edition, number, etc.
Thus, bibliographic references can be concatenated for any record in the CHEM table.
[i] Based on assumption that some springs or wells will have temperature sampled without any chem. data that would be contained in the chem table. Makes for duplicate location of temperature data
[ii] Original latitude and longitude of a record is maintained because we moved some locations around in the SITE table which we think are better, but we leave the original location here in case we were mistaken.
[iii] Many fields in the CHEM table contain multiple ways a measurement was sampled. For example, pH was potentially sampled both in the field and/or in the lab. The table thus can include multiple fields ‘pH Field’, ‘pH Lab’, or ‘pH Unknown’ if the method was not specified. This data can also be found in the CHEMMORE table keyed to the CHEM table in a one-to-many relationship, with two fields per measurement: “Type” (such as ‘field’, ‘lab’, ‘unknown’, etc.) and “Item” (such as pH, eH, etc.). Yes, this is in violation of database normalization rules, but the data were put in two ways to determine which worked best, with the idea that one way would eventually be removed.
[iv] Yes, this could have been a calculated field