This is an example of a physical table structure for recording map details to enable, for example, searches for relevant maps and the production of maplists, catalogues, and map lookup tables for use by cave and area tables. It also addresses the issue of handling both cave maps and published government and commercial maps. A simpler alternative for flat-file cave-map-only use has also been shown. See also the further explanatory notes below. Comments and suggested improvements are invited.
Tips for using this page:
+---
¦
PL0000 ¦
Map master table ¦
+---+-------------------------+----+ 1 » ¦
¦195¦Map ID ¦A10*¦¦»-------+
¦366¦Map series ID ¦A8 ¦»L ¦
¦202¦Map or sheet name ¦A30 ¦1 ¦
¦272¦Map subsheet name ¦A30 ¦ ¦
¦271¦Map number ¦A25 ¦ ¦
¦205¦Map scale ¦N ¦2 ¦
¦367¦Map type ¦A2 ¦L 2 ¦
¦551¦Map geodetic datum ¦A20 ¦L 2 ¦
¦552¦Map height datum ¦A20 ¦L 2 ¦
¦553¦Map grid ¦A20 ¦L 2 ¦
¦554¦Map geog coords shown Y/N¦L ¦2 ¦
¦555¦Map vector version avail?¦L ¦2 ¦
¦556¦Map raster version avail?¦L ¦2 ¦
¦370¦Map source - country code¦A2 ¦L 2 ¦
¦200¦Map source - org code ¦A3 ¦L 2 ¦
¦396¦Map source - type ¦A2 ¦L 2 ¦
¦573¦Map scope - world region ¦A2 ¦L 2 ¦
¦196¦Map scope - country code ¦A2 ¦L 2 ¦
¦197¦Map scope - state code ¦A2 ¦L 2 ¦
¦198¦Map scope - area code ¦A5 ¦L 2 ¦
¦274¦Map scope - N latitude ¦N ¦ ¦
¦275¦Map scope - S latitude ¦N ¦ ¦
¦276¦Map scope - E longitude ¦N ¦ ¦
¦277¦Map scope - W longitude ¦N ¦ ¦
¦273¦Map update year ¦A4 ¦ ¦
¦557¦Map edition ¦A10 ¦ ¦
+---+-------------------------+----+ ¦
This master table is used for all maps ¦
whether caver-produced or external. ¦
(1) See also Table PL0558 below. ¦
(2) If a map belongs to a Series, these ¦
fields could be left empty and the ¦
equivalent Series fields relied on ¦
instead (see further below in this ¦
column). ¦
¦
¦
PL0368 ¦
Further political coverage ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦368¦Map scope - other country¦A2 ¦L ¦
¦369¦Map scope - other state ¦A2 ¦L ¦
¦409¦Map scope - other area ¦A5 ¦L ¦
+---+-------------------------+----+ ¦
For where map overlaps political or other ¦
borders. ¦
¦
¦
PL0397 ¦
Further geographic coverage ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦397¦Map scope - other N lat ¦N ¦ ¦
¦398¦Map scope - other S lat ¦N ¦ ¦
¦399¦Map scope - other E long ¦N ¦ ¦
¦400¦Map scope - other W long ¦N ¦ ¦
+---+-------------------------+----+ ¦
For where there are extra coverage segments ¦
beyond the nominal rectangle of the map ¦
as recorded above in the master table. ¦
This ensures searches for maps covering a ¦
lat/long on an extra segment will still ¦
find the map. ¦
¦
¦
PL0558 ¦
Special published sheetname ¦
+---+-------------------------+----+ 0:1 « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦558¦Map special sheet name ¦A50 ¦ ¦
+---+-------------------------+----+ ¦
This table occasionally used typically when ¦
a map sheet which is part of a map series ¦
is published under a special name usually ¦
in conjunction with an adjacent sheet. ¦
¦
¦
PL0589 ¦
Images of the map ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦
¦591¦Map image master software¦A30 ¦L
¦592¦Map image s/w version ¦A10 ¦
¦593¦Map image technique ¦A2 ¦L 3
¦594¦Map image type ¦A2 ¦L 3
¦595¦Map image file format ¦A2 ¦L 3
¦597¦Map image comment ¦A70 ¦4
¦590¦Map image filename ¦A50 ¦1 2
¦589¦Map image ¦B ¦1
+---+-------------------------+----+
(1) These are alternatives. Some recommend
not storing large images directly in
the db but only its filename. An action
can then open the designated file in a
graphics viewer.
(2) If field used, the path to the filenames
directory can be stored separately in
field 596[Map images directory path]
found on the Cave Tables page in the
site table SYS00SPA.
(3) Strictly, these fields are not
independent of each other (3NF), but
based on 595. Fields 593 and 594 could
be stored in a separate table which is
also used for the lookup table for 595.
(4) For any brief comment specific to this
image. The main comments for this map
can be stored in the comments table
PL0218.
**********************************************
The tables below in this column relate to a
map series rather than to an individual map,
e.g. a government topographic map series.
PS0000
Map series
+---+-------------------------+----+ 1 »
¦371¦Map series ID ¦A8* ¦¦»-------+
¦278¦Map series code ¦A12 ¦ ¦
¦279¦Map series name ¦A62 ¦ ¦
¦372¦Map series abbreviation ¦A12 ¦ ¦
¦280¦Map series length units ¦A1 ¦L ¦
¦373¦Map series scale ¦N ¦ ¦
¦374¦Map series publisher ID ¦A9 ¦»L ¦
¦559¦Map series map type ¦A2 ¦L ¦
¦560¦Map series geodetic datum¦A20 ¦L ¦
¦561¦Map series height datum ¦A20 ¦L ¦
¦562¦Map series map grid ¦A20 ¦L ¦
¦563¦Map series geo coords Y/N¦L ¦ ¦
¦564¦Map series vector avail? ¦L ¦ ¦
¦565¦Map series raster avail? ¦L ¦ ¦
¦566¦Map series source country¦A2 ¦L ¦
¦567¦Map series org code ¦A3 ¦L ¦
¦568¦Map series source type ¦A2 ¦L ¦
¦572¦Map series world region ¦A2 ¦L ¦
¦569¦Map series scope country ¦A2 ¦L ¦
¦570¦Map series scope state ¦A2 ¦L ¦
¦571¦Map series scope area ¦A5 ¦L ¦
+---+-------------------------+----+ ¦
¦
¦
PS0574 ¦
Further political coverage ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map series ID ¦A8* ¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦574¦Map series other country ¦A2 ¦L ¦
¦575¦Map series other state ¦A2 ¦L ¦
¦576¦Map series other area ¦A5 ¦L ¦
+---+-------------------------+----+ ¦
For where the map series overlaps political ¦
or other borders. ¦
¦
¦
PS0577 ¦
Map series comments ¦
+---+-------------------------+----+ 0:n « ¦
¦371¦Map series ID ¦A8* ¦¦«-------+
¦ 73¦Value sequence ¦S* ¦
¦577¦Map series comment line ¦A70 ¦
+---+-------------------------+----+
If a memo-type field is preferred, then
field 578[Map series comment memo] can
replace fields 73 and 577 above in a 0:1
table PS0578.
|
»--------------------------------------------+
¦
PL0203 ¦
Cave map details ¦
+---+-------------------------+----+ 0:1 « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦588¦Map numberer country code¦A2 ¦L ¦
¦517¦Map numberer org code ¦A3 ¦L ¦
¦201¦Map sequence number ¦A5 ¦R ¦
¦199¦Map cave serial number ¦A4 ¦R ¦
¦203¦Map & survey grade org ¦A5 ¦L ¦
¦204¦Map & survey grade value ¦A8 ¦L ¦
¦206¦Map views shown ¦A3 ¦L ¦
¦207¦Map survey year ¦A4 ¦ ¦
¦401¦Map issue year ¦A4 ¦ ¦
¦208¦Map principal surveyor ID¦A10 ¦»L 1 ¦
¦402¦Map principal drafter ID ¦A10 ¦»L 1 ¦
¦211¦Map host area map ID ¦A10 ¦»L ¦
¦212¦Map restricted Y/N ¦L ¦ ¦
¦213¦Map microfilmed Y/N ¦L ¦ ¦
¦403¦Map digital master Y/N ¦L ¦ ¦
¦214¦Map sheet size ¦A25 ¦ ¦
¦404¦Map sheet quantity ¦S ¦ ¦
¦580¦Map biblio updater ID ¦A10 ¦»L ¦
¦581¦Map biblio updater org ID¦A9 ¦»L ¦
¦217¦Map biblio updater year ¦A4 ¦ ¦
+---+-------------------------+----+ ¦
This table is used to show extra details ¦
which may be needed for locally-produced ¦
maps, e.g. caver-produced maps. ¦
(1) If it is wanted to record multiple ¦
surveyors and/or drafters, then ¦
tables PL0586/7 below can be used ¦
instead. ¦
¦
PL0406 ¦
Current map controller ¦
+---+-------------------------+----+ 0:1 « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦406¦Map controller org ID ¦A9 ¦»L ¦
¦407¦Map controller person ID ¦A10 ¦»L ¦
¦210¦Map controller if no ID ¦A70 ¦1 ¦
¦408¦Map controller comment ¦A70 ¦ ¦
¦209¦Map source if no ID ¦A70 ¦2 ¦
+---+-------------------------+----+ ¦
These fields can be used (1) if the master ¦
copy of the map is now controlled by other ¦
than the original source shown in field ¦
200[Map source - org code] in table PL0000, ¦
or (2) if you want to record the person who ¦
is currently holding the master copy. ¦
These fields have been put in a ¦
separate table to save space when they ¦
are rarely being used, otherwise they can ¦
be incorporated back in table PL0203. ¦
(1) This is an alternative field if Person ¦
and Org tables are not available so ¦
that fields 406 and 407 cannot be used. ¦
(2) This field can be used instead of ¦
200[Map source - org code] from PL0000 ¦
if the source has no Org Code or no ¦
Org table is available. ¦
¦
¦
PL0598 ¦
Map publication ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦598¦Map citation Article ID ¦A11*¦»L 1 ¦
¦599¦Map citation page number ¦A15 ¦1 ¦
¦600¦Map citation comment ¦A70 ¦ ¦
+---+-------------------------+----+ ¦
This table links maps and references. ¦
(1) If there is no References table to link ¦
to, these two fields can be replaced by ¦
219[Map citation if no ID]. ¦
¦
¦
PL0218 ¦
Map comments ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦218¦Map comment line ¦A70 ¦ ¦
+---+-------------------------+----+ ¦
If a memo-type field is preferred, then ¦
field 579[Map comment memo] can replace ¦
fields 73 and 218 above in a 0:1 ¦
table PL0579. ¦
¦
¦
PL0586 ¦
Surveyors ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦586¦Map surveyor ID ¦A10 ¦»L 1 ¦
+---+-------------------------+----+ ¦
This table can be used if it is wanted to ¦
record multiple surveyors. ¦
(1) Links to a Record ID in a People table. ¦
If no People table is available, then ¦
the person's actual name can be used ¦
by substituting multi-valued field ¦
584[Map surveyor]. ¦
¦
¦
PL0587 ¦
Drafters ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦ 73¦Value sequence ¦S* ¦ ¦
¦587¦Map drafter ID ¦A10 ¦»L 1 ¦
+---+-------------------------+----+ ¦
This table can be used if it is wanted to ¦
record multiple drafters. ¦
(1) Links to a Record ID in a People table. ¦
If no People table is available, then ¦
the person's actual name can be used ¦
by substituting multi-valued field ¦
585[Map drafter]. ¦
¦
PL0601 ¦
Caves on map ¦
+---+-------------------------+----+ 0:n « ¦
¦195¦Map ID ¦A10*¦¦«-------+
¦601¦Map scope - Cave ID ¦A10*¦»L
+---+-------------------------+----+
This table links maps and caves.
PL0000 (alt)
Alternative single table for cave maps
+---+-------------------------+----+
¦195¦Map ID ¦A10*¦
¦196¦Map scope - country code ¦A2 ¦L
¦197¦Map scope - state code ¦A2 ¦L
¦198¦Map scope - area code ¦A5 ¦L
¦199¦Map cave serial number ¦A4 ¦R
¦588¦Map numberer country code¦A2 ¦L
¦517¦Map numberer org code ¦A3 ¦L
¦201¦Map sequence number ¦A5 ¦R
¦370¦Map source - country code¦A2 ¦L
¦200¦Map source - org code ¦A3 ¦L
¦209¦Map source if no ID ¦A70 ¦
¦202¦Map or sheet name ¦A30 ¦
¦272¦Map subsheet name ¦A30 ¦
¦203¦Map & survey grade org ¦A5 ¦L
¦204¦Map & survey grade value ¦A8 ¦L
¦205¦Map scale ¦N ¦
¦206¦Map views shown ¦A3 ¦
¦207¦Map survey year ¦A4 ¦
¦401¦Map issue year ¦A4 ¦
¦273¦Map update year ¦A4 ¦
¦557¦Map edition ¦A10 ¦
¦582¦Map principal surveyor ¦A25 ¦
¦583¦Map principal drafter ¦A25 ¦
¦210¦Map controller if no ID ¦A70 ¦3
¦408¦Map controller comment ¦A70 ¦3
¦211¦Map host area map ID ¦A10 ¦»L
¦212¦Map restricted Y/N ¦L ¦
¦213¦Map microfilmed Y/N ¦L ¦
¦403¦Map digital master Y/N ¦L ¦
¦214¦Map sheet size ¦A25 ¦L
¦404¦Map sheet quantity ¦S ¦
¦219¦Map citation if no ID ¦A70 ¦1
¦600¦Map citation comment ¦A70 ¦1
¦218¦Map comment line ¦A70 ¦2
¦215¦Map biblio updater ¦A20 ¦
¦216¦Map biblio updater org ¦A9 ¦
¦217¦Map biblio updater year ¦A4 ¦
+---+-------------------------+----+
This is an alternative master table
which could be used if only cave maps
were being recorded and only a single
table was wanted. It combines the
fields from the other tables PL0000,
PL0203, PL0218, PL0219, PL0406, PL0586,
PL0598 and PL0587 above.
(1) These fields could be split off to
their own table PL0219 to save space.
(2) This field could be split off to
its own table PL0218 to save space.
(3) These fields could be split off to
their own table PL0406 to save space.
They are used only if the current
controller differs from the source.
|
Each of the main 3-column rectangles above represents a data table in a database, and lists the fields, i.e. columns, which are in that data table. Note that the rectangles are not a picture of the tables, they are just the lists of fields forming the columns of each table. The rows of each data table would be a separate instance of what that table was about, e.g. each row in the map master table (PL0000) would represent another map.
To see the definition for a particular field, note its Field ID and search for that number on the Field Definitions page.
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 would be linked in order to show particular "views" of the data. For example, the links between the three Map Series tables at the bottom of the left column would enable you to see any further countries or states covered by a particular series, and also any comments about that series.
The reason that the diagram has been built up from standard text characters 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 characters have been chosen from 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.
These tables of cave map and government map details allow such things as:
Map numbers: It can be useful to identify each cave map with a unique public number (as opposed to its internal record ID in a particular database). For example, this allows consistent and unambiguous reference to any particular map, especially in a multi-club environment where several maps of a cave may exist.
For example, in producing its national cave maplist, the Australian
Speleological Federation
used a national cave map number (Ref: ASF Map Numbering Guide, 1985)
built up from: CaveNumber+Club+MapSequenceNumber, for example,
"3B6.VSA29", where:
3B6 = cave number
VSA = club
29 = map sequence number in VSA's records.
This structure of number has the advantages of (1) when sorted,
even in a simple wordprocessor or spreadsheet table, of bringing
all maps for the same cave together in the list regardless of which
organisation produced them, and (2) allowing each club to allocate their own
nationally unique map numbers without reference to any central co-ordinator.
We can add some country codes to "internationalise" this map number (make it unique internationally). It can be automatically constructed for display, output, or storage by concatenating the following fields:
| Field | Example |
| 196[Map scope - country code] | AU |
| 197[Map scope - state code] | 3 |
| 198[Map scope - area code] | B |
| 199[Map cave serial number] | 6 |
| . | . |
| 588[Map numberer country code] | AU |
| 517[Map numberer org code] | VSA |
| 201[Map sequence number] | 29 |
The dot in the middle is always present and acts as a separator for when the cave serial number is not present (the cave serial number is omitted if it's an area map, and the area code is omitted if several areas are covered by the map).
Note that if a map has multiple "master" copies such that new versions of it can be independently produced, then each such version would normally need its own map number and its own record in the database in order to be able to record different values for various fields. Of course an updated edition of the one master would normally retain the same number, but with the edition and update-date fields suitably updated.
Foreign keys: Where a field value relates to another entity, e.g. the person recorded in the "surveyor" field relates to the Person entity (which has its own full set of tables), then preference has been given to showing the internal Record ID version of the field rather than the version which accepts the person's actual name. This is so that the field can then act as a "foreign key" and link to the other entity's master table, thus giving the option of displaying all Person field values wanted, not just their name. However, for where no such other entity tables are present, an alternative field which accepts the person's actual name has also been referred to.
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.
- Any numbers to the right of a field refer to the notes below that table.
Table linkages (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.
L = Logical. True or False. Where L data type is not available, this could
be implemented via an A1 field with coded values of 1 or 0, meaning
"Yes" or "No" (or True or False) respectively, when expressed in English.
M = Memo (variable length free text).
B = BLOB (Binary Large Object, e.g. a photo image).
[ Top ]
[ Tables ]
20-Sep-2002. Initial release.