This is an example of a physical table structure in the subsystem for one entity in a cave/karst database system which uses the draft UIS fields (See UISIC's Cave Data Exchange Proposal). Such a cave/karst database could include data for entities such as caves, karst features, areas, maps, references, specimens, people, organisations, photos, reports, surveys, and so on. See also the further explanatory notes below. We have not started formal discussion yet, but in the meantime your comments and suggested improvements are very welcome.
An example of where much of this structure and its fields have been implemented is the Australian Speleological Federation's Australian Karst Index national cave/karst database. Their software is open-source using Perl+MySQL+Apache+Linux, and is available free of charge.
Tips for using this page:
+----
¦
¦
CA0000 ¦
Cave master table ¦
+---+-------------------------+----+ 1 » ¦
¦227¦Cave ID ¦A10*¦¦»------------+
¦ 1¦Karst feature type ¦A2 ¦L ¦
¦ 9¦Quantity of entrances ¦S ¦ ¦
¦ 10¦Entrance type ¦A1 ¦L ¦
¦ 2¦Submersion ¦A1 ¦L ¦
¦ 20¦Penetrability ¦A1 ¦L ¦
¦ 3¦Flow presence ¦A1 ¦L ¦
¦ 4¦Flow direction ¦A1 ¦L ¦
¦ 5¦Internal flow ¦A1 ¦L ¦
¦ 6¦Internal water ¦A1 ¦L ¦
¦ 56¦Length ¦N ¦ ¦
¦ 57¦Length method ¦A2 ¦L ¦
¦ 58¦Length accuracy ¦N ¦ ¦
¦511¦Vertical extent ¦N ¦ ¦
¦ 59¦Extent below entrance ¦N ¦ ¦
¦ 60¦Extent above entrance ¦N ¦ ¦
¦ 61¦Vertical method ¦A2 ¦L ¦
¦ 62¦Vertical accuracy ¦N ¦ ¦
¦ 63¦Length of largest chamber¦N ¦ ¦
¦ 64¦Width of largest chamber ¦N ¦ ¦
¦ 65¦Height of largest chamber¦N ¦ ¦
¦ 67¦Extent length ¦N ¦ ¦
¦ 68¦Extent width ¦N ¦ ¦
¦531¦Orientation ¦N ¦ ¦
¦297¦Length category ¦A1 ¦L(3) ¦
¦527¦Depth category ¦A1 ¦L(3) ¦
¦ 21¦Latitude - coarse ¦N ¦ ¦
¦ 22¦Longitude - coarse ¦N ¦ ¦
¦ 23¦Map sheet name - coarse ¦A30 ¦L ¦
¦ 26¦Grid reference - coarse ¦A4 ¦ ¦
¦ 25¦Gr.ref qualifier - coarse¦A4 ¦L ¦
¦ 27¦Nearest locality ¦A30 ¦ ¦
¦ 28¦Altitude - coarse ¦N ¦ ¦
¦ 29¦Degree explored ¦A2 ¦L ¦
¦ 42¦Owner type ¦A2 ¦L ¦
¦ 44¦Management classifier ¦A8 ¦L(2) ¦
¦ 45¦Management category ¦A2 ¦L ¦
¦ 47¦Controller ¦A8 ¦L(2) ¦
¦ 54¦Percent mapped ¦S ¦ ¦
¦ 78¦Entrance marking ¦A2 ¦L ¦
¦ 13¦Minimum temperature ¦N ¦ ¦
¦ 14¦Maximum temperature ¦N ¦ ¦
¦ 15¦Minimum humidity ¦N ¦ ¦
¦ 16¦Maximum humidity ¦N ¦ ¦
¦ 17¦Moisture level ¦A2 ¦L ¦
¦228¦Owner - person ID ¦A10 ¦L» People ¦
¦417¦Owner - organisation ID ¦A9 ¦L» Orgs ¦
¦418¦Owner - name ¦A60 ¦(1) ¦
¦229¦Local government area ¦A30 ¦L ¦
¦230¦Update status ¦A2 ¦L ¦
¦220¦Country in cave number ¦A2 ¦(4) ¦
¦ 18¦State in cave number ¦A2 ¦(4) ¦
¦ 19¦Area in cave number ¦A3 ¦(4) ¦
¦ 77¦Serial in cave number ¦A4 ¦R(4) ¦
¦303¦Area lookup ¦A7 ¦(4) ¦
+---+-------------------------+----+ ¦
(1) If not available by direct link to 228. ¦
(2) Use initials for the time being. ¦
(3) Calculated field 528[Size category] is ¦
formed by concatenating these two. ¦
(4) These fields could be removed to a ¦
separate table CA0018 Cave Number (below) ¦
which would probably vary per country. ¦
¦
CA0018 ¦
Cave number ¦
+---+-------------------------+----+ 1:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦220¦Country in cave number ¦A2 ¦ ¦
¦ 18¦State in cave number ¦A2 ¦ ¦
¦ 19¦Area in cave number ¦A3 ¦ ¦
¦ 77¦Serial in cave number ¦A4 ¦R ¦
¦303¦Area lookup ¦A7 ¦L ¦
+---+-------------------------+----+ ¦
(1) This table could replace the equivalent ¦
fields in the master table. ¦
(2) Format of table would vary per country. ¦
¦
CA0231 ¦
Previous cave numbers ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦231¦Previous country ¦A2 ¦ ¦
¦232¦Previous state ¦A2 ¦ ¦
¦233¦Previous area ¦A3 ¦ ¦
¦234¦Previous serial ¦A4 ¦R ¦
¦304¦Previous area lookup ¦A7 ¦L ¦
+---+-------------------------+----+ ¦
¦
CA0074 ¦
Cross-references ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦235¦Cross-reference country ¦A2 ¦ ¦
¦236¦Cross-reference state ¦A2 ¦ ¦
¦237¦Cross-reference area ¦A3 ¦ ¦
¦ 74¦Cross-reference serial ¦A4 ¦R ¦
¦305¦Cross-reference lookup ¦A7 ¦L ¦
+---+-------------------------+----+ ¦
This table can link the cave to any other ¦
numbered entrances which it may have. ¦
¦
CA0070 ¦
Principal name ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 70¦Principal name ¦A52 ¦ ¦
+---+-------------------------+----+ ¦
Field 69[Other names] in Table CA0069 below ¦
allows for further names. ¦
¦
CA0439 ¦
Publishable land unit location ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦439¦Land unit - coarse ¦A30 ¦L ¦
+---+-------------------------+----+ ¦
¦
CA0259 (read-only) ¦
Other db site Cave IDs for this cave ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦259¦Other cave IDs ¦A10 ¦L» Caves ¦
+---+-------------------------+----+ ¦
¦
CA0075 ¦
Fields to be excluded from cave lists ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦ 75¦Cave list exclusion FID ¦S ¦L» Fields ¦
+---+-------------------------+----+ ¦
¦
CA0007 ¦
Rock type ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦ 7¦Rock type ¦A2 ¦L ¦
+---+-------------------------+----+ ¦
¦
The following fields each have a similar ¦
multi-valued table to Rock Type above: ¦
«------------+
8 Cave type A2 L 0:n « ¦
11 Development A3 L ¦
12 Decoration A2 L ¦
35 Extension discovery A52 ¦
41 Cave use A2 L ¦
43 Damage A2 L ¦
46 Protection methods A2 L(1) ¦
48 Importances A2 L ¦
49 Surface use A2 L ¦
50 Difficulties A2 L ¦
51 Prospects A2 L ¦
52 Hazards A2 L ¦
53 Comments A62 ¦
66 Pitches N ¦
69 Other names A52 ¦
72 Contents A3 L ¦
258 Access status (ex 46) A2 L(1) ¦
¦
(1) 46 will split to 46 and 258 later. ¦
¦
¦
CA0037 ¦
Species found ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦ 37¦Genus name ¦A30 ¦L(1) ¦
¦ 38¦Species name ¦A30 ¦L(1) ¦
¦509¦Genus confidence level ¦A2 ¦L ¦
¦510¦Species confidence level ¦A2 ¦L ¦
¦ 39¦Species ref - surname ¦A45 ¦(1,2) ¦
¦260¦Species ref - year ¦A4 ¦(1,2) ¦
¦261¦Species ref - year suffix¦A1 ¦(1,2) ¦
¦262¦Species ref - comment ¦A25 ¦(1) ¦
¦263¦Species ref - article ID ¦A11 ¦L» Refs (1) ¦
¦ 40¦Species significance ¦A1 ¦L(1) ¦
¦364¦Specimen ID ¦A10 ¦L» Specimens ¦
+---+-------------------------+----+ ¦
(1) If not avail. by 364 direct link. ¦
(2) If not avail. by 263 direct link. ¦
¦
CA0525 (1) ¦
Description of cave (lines) ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦525¦Cave description line ¦A62 ¦ ¦
+---+-------------------------+----+ ¦
(1) Tables CA0525 and CA0530 are alternatives. ¦
¦
CA0530 (1) ¦
Description of cave (memo) ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦530¦Cave description memo ¦M ¦
+---+-------------------------+----+
(1) Tables CA0525 and CA0530 are alternatives.
===============================================
SITE PARAMETERS:
The following values in this column
apply to the site database as a whole:
SYS00SPA
Database site parameters
+---+-------------------------+----+ 1 »
¦156¦Site country code ¦A2* ¦L¦»-------+
¦157¦Site organisation code ¦A3* ¦L¦ ¦
¦182¦Database org code ¦A3 ¦L ¦
¦158¦Site default language ¦A2 ¦L ¦
¦288¦Geodetic Datum used ¦A20 ¦L(1) ¦
¦289¦Map Grid used ¦A20 ¦L(1) ¦
¦516¦Height Datum used ¦A20 ¦L(1) ¦
¦412¦Coarse - map series ¦A30 ¦ ¦
¦296¦Coarse - grid ref units ¦A1 ¦L ¦
¦413¦Coarse - gr.ref qualifier¦A30 ¦ ¦
¦294¦Coarse - geogr. precision¦N ¦ ¦
¦295¦Coarse - gr.ref precision¦N ¦ ¦
¦526¦Coarse altitude precision¦N ¦ ¦
¦440¦Coarse - land unit name ¦A15 ¦ ¦
¦446¦Coarse - altitude units ¦A1 ¦L ¦
¦290¦Table scroll arrows Y/N ¦L ¦L ¦
¦291¦Code translations Y/N ¦L ¦L ¦
¦336¦Local abstracts name ¦A132¦ ¦
¦337¦Local abstracts initials ¦A10 ¦ ¦
¦529¦Software upgrade level ¦A4 ¦ ¦
¦539¦Program version number ¦A15 ¦ ¦
¦596¦Map images directory path¦A50 ¦ ¦
+---+-------------------------+----+ ¦
This table will later be split into site ¦
parameters and user preferences. ¦
Most parameters are settable only by the ¦
Central Database Administrator. ¦
(1) This field could be included in this table¦
if the one value was applicable throughout¦
the site. Otherwise, if the value could ¦
vary per location value, then it can be ¦
omitted here and an equivalent field used ¦
in CA0241 Position on Maps and CA0245 ¦
Exact Position tables, as appropriate. ¦
¦
SYS00SPB ¦
Names for level of government at site ¦
+---+-------------------------+----+ 0:n « ¦
¦156¦Site country code ¦A2* ¦¦«--------+
¦157¦Site organisation code ¦A3* ¦¦ ¦
¦ 73¦Value sequence ¦S* ¦(1) ¦
¦292¦Government level name ¦A15 ¦ ¦
+---+-------------------------+----+ ¦
¦
SYS00SPC ¦
Land parcel nomenclature at site ¦
+---+-------------------------+----+ 0:n « ¦
¦156¦Site country code ¦A2* ¦¦«--------+
¦157¦Site organisation code ¦A3* ¦¦
¦ 73¦Value sequence ¦S* ¦(1)
¦293¦Land unit name ¦A15 ¦
+---+-------------------------+----+
(1) Value Sequence to be set so that the names
are stored in decreasing size of area.
|
»-------------------------------------------------+
¦
¦
CA0241 ¦
Position on maps ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦411¦Map posn - map ID ¦A10 ¦L» Maps ¦
¦241¦Map posn - map easting ¦A10 ¦ ¦
¦242¦Map posn - map northing ¦A10 ¦ ¦
¦671¦Map posn - full easting ¦A10 ¦ ¦
¦672¦Map posn - full northing ¦A10 ¦ ¦
¦243¦Map posn - units ¦A1 ¦L298 ¦
¦678¦Map posn - UTM grid zone ¦A3 ¦ ¦
¦248¦Map posn - sheet number ¦A8 ¦L ¦
¦679¦Map posn - 100km letters ¦A2 ¦ ¦
¦244¦Map posn - precision used¦A7 ¦ ¦
¦302¦Map posn - accuracy used ¦A7 ¦ ¦
¦629¦Map posn - geodetic datum¦A20 ¦L(1) ¦
¦630¦Map posn - map grid ¦A20 ¦L(1) ¦
¦238¦Map posn - map scale ¦N ¦(1) ¦
¦239¦Map posn - map number ¦A25 ¦(1) ¦
¦414¦Map posn - map name ¦A30 ¦(1) ¦
¦240¦Map posn - map edition ¦A8 ¦ ¦
¦632¦Map posn - date estimated¦D ¦ ¦
¦633¦Map posn - citation ID ¦A11 ¦L» Refs(2) ¦
¦634¦Map posn - citation title¦A132¦(3) ¦
¦639¦Map posn - publicn name ¦A132¦(3) ¦
¦635¦Map posn - citation year ¦A4 ¦(3) ¦
¦636¦Map posn - citation vol ¦A7 ¦(3) ¦
¦637¦Map posn - citation issue¦A4 ¦(3) ¦
¦638¦Map posn - citation page ¦A13 ¦(2) ¦
¦640¦Map posn - comment ¦A255¦ ¦
+---+-------------------------+----+ ¦
This table stores the position of the cave as ¦
it would appear on any number of specific maps ¦
using each map's own grid reference system. ¦
(1) Only present if not avail by 411 direct link. ¦
Could also be auto-filled upon 411 lookup. ¦
(2) Only present if Data Quality tables not being ¦
used, e.g. no field 468[Data - reference ID]. ¦
(3) Only present if not available by 633 direct ¦
link, and Data Quality tables not being used, ¦
e.g. no field 468[Data - reference ID]. ¦
¦
CA0245 ¦
Exact position ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦245¦Position - latitude ¦N ¦ ¦
¦246¦Position - longitude ¦N ¦ ¦
¦514¦Position - geog precision¦N ¦ ¦
¦515¦Position - geog accuracy ¦N ¦ ¦
¦625¦Posn - geog geodet datum ¦A20 ¦L ¦
¦628¦Posn - geographic method ¦A2 ¦L641 ¦
¦631¦Posn - geog date estimatd¦D ¦ ¦
¦642¦Posn - geog reference ID ¦A11 ¦L» Refs(2) ¦
¦643¦Posn - geog ref title ¦A132¦(3) ¦
¦644¦Posn - geog publicn name ¦A132¦(3) ¦
¦645¦Posn - geog ref year ¦A4 ¦(3) ¦
¦646¦Posn - geog ref volume ¦A7 ¦(3) ¦
¦647¦Posn - geog ref issue ¦A4 ¦(3) ¦
¦648¦Posn - geog ref page ¦A13 ¦(2) ¦
¦649¦Posn - geographic comment¦A255¦ ¦
¦249¦Position - easting ¦N ¦ ¦
¦250¦Position - northing ¦N ¦ ¦
¦251¦Position - grid ref units¦A1 ¦L298 ¦
¦247¦Posn - UTM zone designatn¦A3 ¦(5) ¦
¦673¦Posn - UTM zone number ¦A2 ¦(5) ¦
¦674¦Posn - UTM zone letter ¦A1 ¦(5) ¦
¦675¦Posn - 100km sq letters ¦A2 ¦ ¦
¦676¦Posn - 100K sheet number ¦A8 ¦ ¦
¦677¦Posn - 25K sheet number ¦A16 ¦ ¦
¦252¦Posn - gref precision ¦N ¦ ¦
¦300¦Posn - gref accuracy ¦N ¦ ¦
¦626¦Posn - gref map grid ¦A20 ¦L ¦
¦650¦Posn - gref geodet datum ¦A20 ¦L ¦
¦651¦Posn - gref method ¦A2 ¦L641 ¦
¦652¦Posn - gref date estimatd¦D ¦ ¦
¦653¦Posn - gref reference ID ¦A11 ¦L» Refs(2) ¦
¦654¦Posn - gref ref title ¦A132¦(3) ¦
¦655¦Posn - gref publicn name ¦A132¦(3) ¦
¦656¦Posn - gref ref year ¦A4 ¦(3) ¦
¦657¦Posn - gref ref volume ¦A7 ¦(3) ¦
¦658¦Posn - gref ref issue ¦A4 ¦(3) ¦
¦659¦Posn - gref ref page ¦A13 ¦(2) ¦
¦660¦Posn - gref comment ¦A255¦ ¦
¦442¦Altitude ¦N ¦ ¦
¦443¦Altitude - units ¦A1 ¦L298 ¦
¦444¦Altitude - precision +/- ¦N ¦ ¦
¦445¦Altitude - accuracy +/- ¦N ¦ ¦
¦627¦Altitude - height datum ¦A20 ¦L ¦
¦661¦Altitude - method ¦A2 ¦L641 ¦
¦662¦Altitude - date estimated¦D ¦ ¦
¦663¦Altitude - reference ID ¦A11 ¦L» Refs(3) ¦
¦664¦Altitude - ref title ¦A132¦(4) ¦
¦665¦Altitude - publicn name ¦A132¦(4) ¦
¦666¦Altitude - ref year ¦A4 ¦(4) ¦
¦667¦Altitude - ref volume ¦A7 ¦(4) ¦
¦668¦Altitude - ref issue ¦A4 ¦(4) ¦
¦669¦Altitude - reference page¦A13 ¦(3) ¦
¦670¦Altitude - comment ¦A255¦ ¦
¦441¦Fifth-smallest land unit ¦A30 ¦(1)(2) ¦
¦253¦Fourth-smallest land unit¦A30 ¦(1)(2) ¦
¦254¦Third-smallest land unit ¦A30 ¦(1)(2) ¦
¦255¦Second-smallest land unit¦A30 ¦(1)(2) ¦
¦256¦Smallest land unit ¦A10 ¦(1)(2) ¦
¦365¦Special land parcel ¦A30 ¦(1) ¦
+---+-------------------------+----+ ¦
This table stores the current best estimate of ¦
the cave's position using any or all of lat/long, ¦
grid co-ords, altitude and cadastral. Note that ¦
many of the fields shown are optional alternate ¦
fields depending on how the site's database is ¦
structured, as per the notes below. ¦
(1) All these fields could be replaced by ¦
foreign key 542[Entrance land parcel ID]». ¦
(2) The names for these land unit types should be ¦
stored in multi-valued Site Params field ¦
293[Land unit name]. ¦
(3) Only present if Data Quality tables not being ¦
used, e.g. no field 468[Data - reference ID]. ¦
(4) Only present if not available by direct link ¦
to above ID, and Data Quality tables not ¦
being used, e.g. no ¦
field 468[Data - reference ID]. ¦
(5) These fields are mutual alternatives. Use ¦
either 247 alone, or both 673 and 674. ¦
¦
CA0680 (1) ¦
How to find the cave (memo) ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦680¦How to find cave - memo ¦M ¦ ¦
+---+-------------------------+----+ ¦
(1) Tables CA0680 and CA0257 are alternatives. ¦
¦
¦
CA0257 (1) ¦
How to find the cave (lines) ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦257¦How to find cave - line ¦A62 ¦ ¦
+---+-------------------------+----+ ¦
(1) Tables CA0680 and CA0257 are alternatives. ¦
¦
CA0055 ¦
Widest map ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦265¦Widest map ID ¦A10 ¦L» Maps ¦
¦518¦Map numberer org code ¦A3 ¦(1,2,3) ¦
¦264¦Map sequence number ¦A5 ¦(1,3)R ¦
¦359¦Map or sheet name ¦A30 ¦(1) ¦
¦ 55¦Map source - org code ¦A3 ¦(1) ¦
¦360¦Map & survey grade org ¦A4 ¦(1) ¦
¦361¦Map & survey grade value ¦A8 ¦(1) ¦
¦362¦Map scale ¦N ¦(1) ¦
¦363¦Dates of surv/issue/amend¦A15 ¦(1) ¦
+---+-------------------------+----+ ¦
Widest map(s) are those which give the widest or ¦
most comprehensive coverage of the cave or karst ¦
feature, not necessarily the most accurate map. ¦
(1) If not available by 265 direct link. ¦
(2) Was 55[Map source - org code]. ¦
(3) With the Cave ID, these two fields would ¦
normally be enough to identify the map. ¦
¦
CA0071 ¦
References ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦268¦Ref - subjects ¦A25 ¦ ¦
¦269¦Ref - relevant page range¦A20 ¦ ¦
¦270¦Article ID ¦A11 ¦L» Refs ¦
¦ 71¦Ref - surnames ¦A45 ¦(1) ¦
¦266¦Ref - year of publication¦A4 ¦(1) ¦
¦267¦Ref - suffix to year ¦A1 ¦(1) ¦
¦355¦Ref - name of article ¦A132¦(1) ¦
¦356¦Ref - name of publication¦A132¦(1) ¦
¦357¦Ref - volume number ¦A7 ¦(1) ¦
¦358¦Ref - issue number ¦A4 ¦(1) ¦
+---+-------------------------+----+ ¦
(1) If not avail. by 270 direct link. ¦
¦
CA0030 ¦
Discovery ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦ 30¦Discoverer type ¦A1 ¦L ¦
¦419¦Discoverer - person ID ¦A10 ¦L» People ¦
¦522¦Discoverer surname ¦A30 ¦(1) ¦
¦523¦Discoverer firstname ¦A20 ¦(1) ¦
¦420¦Discoverer - org ID ¦A9 ¦L» Orgs ¦
¦524¦Discoverer - org initials¦A8 ¦(2) ¦
¦ 31¦Discoverer name ¦A45 ¦(3) ¦
¦ 34¦Discoverer year ¦A4 ¦(4) ¦
¦ 33¦Discoverer month ¦A2 ¦ ¦
¦ 32¦Discoverer day ¦A2 ¦ ¦
¦477¦Discoverer date qualifier¦A2 ¦L ¦
+---+-------------------------+----+ ¦
Use multiple records if multiple persons ¦
and/or organisations were involved. ¦
(1) Manual entry or autofilled by 419. ¦
(2) Manual entry or autofilled by 420. ¦
(3) Combined people+org field for when ¦
419, 420, 522-524 are not available. ¦
(4) A date-type field (FID 36) is also ¦
available. ¦
¦
CA0532 ¦
Entrance feature type ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦532¦Entrance feature type ¦A2 ¦L ¦
+---+-------------------------+----+ ¦
¦
CA0533 ¦
Entrance development ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦533¦Entrance development ¦A3 ¦L ¦
+---+-------------------------+----+ ¦
¦
CA0534 (1) ¦
Entrance description (memo) ¦
+---+-------------------------+----+ 0:1 « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦534¦Entrance description memo¦M ¦ ¦
+---+-------------------------+----+ ¦
(1) Tables CA0534 and CA0535 are alternatives. ¦
¦
CA0535 (1) ¦
Entrance description (lines) ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦535¦Entrance description line¦A62 ¦ ¦
+---+-------------------------+----+ ¦
(1) Tables CA0534 and CA0535 are alternatives. ¦
¦
CA0536 ¦
Entrance close-up photos ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦536¦Entrance photoID - near ¦A10 ¦L» Photos ¦
+---+-------------------------+----+ ¦
¦
CA0537 ¦
Entrance long-shot photos ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦537¦Entrance photoID - locale¦A10 ¦L» Photos ¦
+---+-------------------------+----+ ¦
¦
CA0538 ¦
Entrance number-tag photos ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦538¦Entrance photoID - tag ¦A10 ¦L» Photos ¦
+---+-------------------------+----+ ¦
¦
CA0540 ¦
Cave photos ¦
+---+-------------------------+----+ 0:n « ¦
¦227¦Cave ID ¦A10*¦¦«------------+
¦ 73¦Value sequence ¦S* ¦
¦540¦Cave photo ID ¦A10 ¦L» Photos
+---+-------------------------+----+
|
Each of the main 3-column rectangles above represents a data table in a database. In the rectangle is a list of the fields which form that database table. Note that the rectangles are not a picture of the tables - they are just a list of the fields appearing in each record of that database table. Column 1 in the above rectangles shows the field's UIS Field ID number, Column 2 shows the field's UIS name in English, and Column 3 shows the field's data type.
The full definition for each field can be found on the Field Definitions page. Text-search on the definition page using the field's ID number.
By identifying each UIS field with a numeric code number, it is easier to correlate fields in other cave/karst databases with the corresponding UIS field, even though the other fields may have different names or be written in a different language. This can help when transferring data between different databases.
The purpose of Field 73[Value sequence], used as part of the key on multi-valued tables, is to make the key of each record unique, and to enable the values to be presented in the sequence desired. In some DBMS systems it may not be required.
The dotted lines between the tables show some of the ways in which the tables could be linked in order to show particular "views" of the data.
The tables are arranged as a relational database and generally conform to 3rd normal form, unless the tables for a desired foreign key are not yet available.
The recommended makeup of record IDs for an entity's key, e.g. Cave ID, is by concatenating the following: [2-letter country code] + [3-char organisation code within that country of the organisation which first created the database record] + [a serial number left-padded with zeros to a specific width per entity]. The entity itself does not need to be identified in the ID. Example: 'AUASF00034' for the 34th record for a particular entity created by ASF from Australia. This structure means that the record ID will be unique in the world, and so the entity records can be transferred to other databases without clashing, and still retain their original foreign-key linkages between entities. See also UISIC's Data Exchange Proposal: RecordID proposal and RecordID discussion.
The reason that the diagram has been built up from standard text characters instead of solid lines is so that the page's source code can be copy/pasted to allow easy plain-text emailing of table designs during discussion. Although the tables appear in two columns above, they are in a single column in the source code. The line characters have been chosen from Western ISO-8859-1, and will render in the same way also in Central European: Windows-1250; Cyrillic: KO18R and Windows-1251; Baltic: Windows-1257; Greek: ISO-8859-7 and Windows-1253; and Turkish: ISO-8859-9; and Unicode.
At this stage, these tables show only fields for data which could normally be gathered during routine cave exploration and mapping, and which in the main, apart from exact location data, could normally be published in cave lists without causing too many problems.
Of course, as well as further caving fields, there are many many other fields which could be included to cater also for data gathered during cave/karst scientific work. We plan to invite cave/karst scientists to collaborate with UIS to define fields tailored to their specific discipline, which can then be added to our Data Dictionary for other scientists to use rather than reinvent them.
While exact locations would seldom if ever be published, sometimes it is useful to include approximate or coarse locations in published lists. For example, close enough to allow distribution studies but not close enough to find the caves. The master table CA0000 contains Fields 21-28 which allow for coarse values for latitude, longitude, grid reference, altitude, locality, and mapsheet. And Field 439 in table CA0439 allows for coarse land unit. The Database site parameters table SYS00SPA contains several fields which allow local policy makers to specify how coarse these values should be for this database site. The use of latitude and longitude values as well as normal grid references can assist foreign cavers and researchers to find the approximate location of the cave even though they do not have access to local topo maps and their associated grid references.
Field 75[Cave list exclusion FID] in Table CA0075 allows the specifying of fields which are not to be included in cave listings.
With ongoing exploration, constantly changing cave lengths and depths can require high maintenance effort for a state or national database, so the master table CA0000 contains fields 297[Length category] and 527[Depth category] which allow placing a cave into one of 10 broad dimensional categories instead of tracking its actual dimensions. The local group can keep track of the actual values, and advise the state or national database only if the cave enters a new category. This method and the category ranges have been borrowed courtesy of the Austrian central cave registry.
The cave master table contains single-valued fields which are either inherently short or are normally populated with a value. Single-valued fields which are long and only occasionally populated are separated off into their own table in order to save wasting large empty spaces in master table records.
Further notes can be found below some of the tables.
Columns shown:
1. Standard UIS Field ID (FID).
2. Field name.
3. Data type.
Codes:
* = A primary key.
L = A lookup table is used to set this field's values.
R = Right-justified.
» = This field provides a link to another entity, i.e. is a
"foreign key".
- The names above the table descriptions are example filenames for
the tables using max 8 chars.
- Bracketted numbers to the right of a field refer to the notes
below that table.
Table linkage types (respective instances in detail table per
instance in master table):
0:1 = from 0 to 1 instances.
0:n = from 0 to many instances.
1:1 = always 1 instance.
1:n = at least 1 instance.
The vertical bar(s) at each end of a link show the actual fields linked.
Data types:
S = Short integer, up to 32,768.
N = Numeric, decimals allowed.
Annn = Alphanumeric up to 255 chars long (A1-A255).
D = Date, i.e. accepts only full valid dates. If some parts of a
date could be unknown, then use separate 'A' fields for
year(A4), month(A2) and day(A2).
L = Logical. True or False.
If the L data type is not available, this could be implemented
instead via an S field with values of 1 or 0, meaning True or
False respectively.
M = Memo (variable length free text).
B = BLOB (Binary Large Object, e.g. a photo image).
[ Top ]
[ Notes ]
[ Legend ]
[ Diagram ]
[ Updates ]
[ Other Tables ]
[ Proposal ]
[ Fields list ]
[ Field definitions ]
[ UISIC ]
[ UIS ]
[ ASF ]
Previous versions: [ 2000-11-04 ]
[ Top ] [ Notes ] [ Legend ] [ Diagram ] [ Updates ] [ Other Tables ] [ Proposal ] [ Fields list ] [ Field definitions ] [ UISIC ] [ UIS ] [ ASF ]