Cave/Karst Map Subsystem
Table Design

20 Sep 2002

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:

[ Notes ] [ Legend ] [ Updates ] [ Other Tables ] [ Proposal ] [ Fields ] [ Field definitions ] [ UISIC ] [ UIS ] [ ASF ]
                                             +---
                                             
PL0000                                       
Map master table                             
+---+-------------------------+----+ 1      
195Map ID                   A10*-------+
366Map series ID            A8  L       
202Map or sheet name        A30 1        
272Map subsheet name        A30          
271Map number               A25          
205Map scale                N   2        
367Map type                 A2  L 2      
551Map geodetic datum       A20 L 2      
552Map height datum         A20 L 2      
553Map grid                 A20 L 2      
554Map geog coords shown Y/NL   2        
555Map vector version avail?L   2        
556Map raster version avail?L   2        
370Map source - country codeA2  L 2      
200Map source - org code    A3  L 2      
396Map source - type        A2  L 2      
573Map scope - world region A2  L 2      
196Map scope - country code A2  L 2      
197Map scope - state code   A2  L 2      
198Map scope - area code    A5  L 2      
274Map scope - N latitude   N            
275Map scope - S latitude   N            
276Map scope - E longitude  N            
277Map scope - W longitude  N            
273Map update year          A4           
557Map 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    
195Map ID                   A10*-------+
 73Value sequence           S*           
368Map scope - other countryA2  L        
369Map scope - other state  A2  L        
409Map scope - other area   A5  L        
+---+-------------------------+----+         
For where map overlaps political or other    
borders.                                     
                                             
                                             
PL0397                                       
Further geographic coverage                  
+---+-------------------------+----+ 0:n    
195Map ID                   A10*-------+
 73Value sequence           S*           
397Map scope - other N lat  N            
398Map scope - other S lat  N            
399Map scope - other E long N            
400Map 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    
195Map ID                   A10*-------+
558Map 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    
195Map ID                   A10*-------+
 73Value sequence           S*           
591Map image master softwareA30 L        
592Map image s/w version    A10          
593Map image technique      A2  L 3      
594Map image type           A2  L 3      
595Map image file format    A2  L 3      
597Map image comment        A70 4        
590Map image filename       A50 1 2      
589Map 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  
371Map series ID            A8* -------+
278Map series code          A12          
279Map series name          A62          
372Map series abbreviation  A12          
280Map series length units  A1  L        
373Map series scale         N            
374Map series publisher ID  A9  L       
559Map series map type      A2  L        
560Map series geodetic datumA20 L        
561Map series height datum  A20 L        
562Map series map grid      A20 L        
563Map series geo coords Y/NL            
564Map series vector avail? L            
565Map series raster avail? L            
566Map series source countryA2  L        
567Map series org code      A3  L        
568Map series source type   A2  L        
572Map series world region  A2  L        
569Map series scope country A2  L        
570Map series scope state   A2  L        
571Map series scope area    A5  L        
+---+-------------------------+----+         
                                             
                                             
PS0574                                       
Further political coverage                   
+---+-------------------------+----+ 0:n    
195Map series ID            A8* -------+
 73Value sequence           S*           
574Map series other country A2  L        
575Map series other state   A2  L        
576Map series other area    A5  L        
+---+-------------------------+----+         
For where the map series overlaps political  
or other borders.                            
                                             
                                             
PS0577                                       
Map series comments                          
+---+-------------------------+----+ 0:n    
371Map series ID            A8* -------+
 73Value sequence           S*  
577Map 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    
195Map ID                   A10*-------+
588Map numberer country codeA2  L        
517Map numberer org code    A3  L        
201Map sequence number      A5  R        
199Map cave serial number   A4  R        
203Map & survey grade org   A5  L        
204Map & survey grade value A8  L        
206Map views shown          A3  L        
207Map survey year          A4           
401Map issue year           A4           
208Map principal surveyor IDA10 L 1     
402Map principal drafter ID A10 L 1     
211Map host area map ID     A10 L       
212Map restricted Y/N       L            
213Map microfilmed Y/N      L            
403Map digital master Y/N   L            
214Map sheet size           A25          
404Map sheet quantity       S            
580Map biblio updater ID    A10 L       
581Map biblio updater org IDA9  L       
217Map 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    
195Map ID                   A10*-------+
406Map controller org ID    A9  L       
407Map controller person ID A10 L       
210Map controller if no ID  A70 1        
408Map controller comment   A70          
209Map 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    
195Map ID                   A10*-------+
598Map citation Article ID  A11*L 1     
599Map citation page number A15 1        
600Map 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    
195Map ID                   A10*-------+
 73Value sequence           S*           
218Map 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    
195Map ID                   A10*-------+
 73Value sequence           S*           
586Map 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    
195Map ID                   A10*-------+
 73Value sequence           S*           
587Map 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    
195Map ID                   A10*-------+
601Map scope - Cave ID      A10*L
+---+-------------------------+----+
This table links maps and caves.


PL0000 (alt)
Alternative single table for cave maps
+---+-------------------------+----+
195Map ID                   A10*
196Map scope - country code A2  L
197Map scope - state code   A2  L
198Map scope - area code    A5  L
199Map cave serial number   A4  R
588Map numberer country codeA2  L
517Map numberer org code    A3  L
201Map sequence number      A5  R
370Map source - country codeA2  L
200Map source - org code    A3  L
209Map source if no ID      A70 
202Map or sheet name        A30 
272Map subsheet name        A30 
203Map & survey grade org   A5  L
204Map & survey grade value A8  L
205Map scale                N   
206Map views shown          A3  
207Map survey year          A4  
401Map issue year           A4  
273Map update year          A4  
557Map edition              A10 
582Map principal surveyor   A25 
583Map principal drafter    A25 
210Map controller if no ID  A70 3
408Map controller comment   A70 3
211Map host area map ID     A10 L
212Map restricted Y/N       L   
213Map microfilmed Y/N      L   
403Map digital master Y/N   L   
214Map sheet size           A25 L
404Map sheet quantity       S   
219Map citation if no ID    A70 1
600Map citation comment     A70 1
218Map comment line         A70 2
215Map biblio updater       A20 
216Map biblio updater org   A9  
217Map 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.

Notes:

General:
Maps

Legend:

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 ]

Updates:

20-Sep-2002. Initial release.


Copyright © 2002 Peter Matthews. May be reproduced for non-commercial use provided this copyright paragraph is retained. This ongoing development of field definitions etc is being conducted by the Informatics Commission (UISIC) of the International Union of Speleology (UIS). The Australian Speleological Federation's (ASF) national Karst Index Database has been used as a pilot.
Page address: http://www.uisic.uis-speleo.org/exchange/pltables.html
P. Matthews. Email: matthews@melbpc.org.au