This is a proposed table design for organising specimens.
The layout is based on that developed by the UISIC for caves. As that layout has been useful for automated database creation, it is proposed to keep the style to minimise changes to other software.
| Legend | Tables 1 | Proposal for Exchange (new) | Fields | Field definitions |
SM0000
Specimen master table
+---+-------------------------+----+ 1 >
¦535¦Specimen ID ¦S * ¦¦>------------+
¦555¦Cave ID ¦S ¦L (1) ¦
¦557¦Sample label No. ¦S ¦ ¦
¦558¦Subsample ID ¦A2 ¦ ¦
¦559¦Sample xref ¦A40 ¦ ¦
¦536¦Sample type ¦S ¦L ¦
¦540¦Sample physical ¦S ¦L ¦
¦560¦Map ID ¦S ¦L (1) ¦
¦561¦Site reference ¦A60 ¦ ¦
¦478¦Collected by (person) ¦S ¦L (1) ¦
¦562¦Collection date ¦D ¦ ¦
¦563¦Collected with ¦A60 ¦ ¦
¦539¦Presently stored ¦S ¦L ¦
+---+-------------------------+----+ ¦
(1) sb linked to KID but JR uses local table ¦
¦
¦
SM0006
Stored at location table
+---+-------------------------+----+ 1 >
¦539¦Store ID ¦S * ¦¦>------------+
¦574¦Store Location ¦A60 ¦ ¦
+---+-------------------------+----+ ¦
SM0005
Person Title table
+---+-------------------------+----+ 1 >
¦572¦Title ID ¦S * ¦¦>------------+
¦573¦Person title ¦A7 ¦ ¦
+---+-------------------------+----+ ¦
SM0004
Person table
+---+-------------------------+----+ 1 >
¦478¦Person ID ¦S * ¦¦>------------+
¦570¦First name ¦A24 ¦ ¦
¦571¦Surname ¦A24 ¦ ¦
¦572¦Title ID ¦S ¦L ¦
+---+-------------------------+----+ ¦
SM0003
Map table
+---+-------------------------+----+ 1 >
¦560¦Map ID ¦S * ¦¦>------------+
¦569¦XrefKIDmapID ¦A5 ¦ ¦
+---+-------------------------+----+ ¦
SM0002
Sample or Specimen physical environment
+---+-------------------------+----+ 1 >
¦540¦Physical ID ¦S * ¦¦>------------+
¦568¦Physical environment ¦A45 ¦ ¦
+---+-------------------------+----+ ¦
SM0001
Sample or Specimen type
+---+-------------------------+----+ 1 >
¦536¦Type ID ¦S * ¦¦>------------+
¦564¦Type description ¦A60 ¦ ¦
+---+-------------------------+----+ ¦
SM000x
Cave table
+---+-------------------------+----+ 1 >
¦555¦ID ¦S * ¦¦>------------+
¦556¦Area code ¦S ¦L (1) ¦
¦565¦Cave Serial number ¦S ¦ (1) ¦
¦566¦Cave name ¦A42 ¦ (1) ¦
¦567¦XrefKIDCC ¦A5 ¦ (1) ¦
+---+-------------------------+----+ ¦
|
SM0018
Specimen number (will replace master fields)
> 1:1 +---+-------------------------+----+
---------->¦¦227¦xxxxxxx ¦A10*¦
¦220¦xxxxxxxxxxxxxxxxxxxxxx ¦A2 ¦
¦ 18¦xxxxxxxxxxxxxxxxxxxxxx ¦A2 ¦
¦ 19¦xxxxxxxxxxxxxxxxxxxxxx ¦A3 ¦
¦ 77¦xxxxxxxxxxxxxxxxxxxxxx ¦A4 ¦R
¦303¦xxxxxxxxxxxxxxxxxxxxxx ¦A7 ¦L
+---+-------------------------+----+
Format of table varies per country.
|
Legend:
Columns shown:
Standard UIS field ID, field name, data type.
Codes:
* = A primary key. L = A lookup table is used. R = Right-justified.
Table linkages:
0:1 = from 0 to 1 instances per master instance.
1:1 = always 1 instance per master instance.
0:n = from 0 to many instances per master instance.
1:n = at least 1 instance per master instance.
Data types:
S = Short integer, up to 32,768.
N = Numeric, decimals allowed.
A20 = (e.g.) Alphanumeric up to 20 chars wide (max A255).
D = Date.
[ Top ]
[ Tables 1 ]
This file, smtables.html, is copyright © 2001, 2002 Jill Rowling and the information may be used according to the LGPL license (see GNU Library General Public License).
This table, smtables.html is proposed by Jill Rowling for
discussion.
In general, the ongoing development of field definitions etc is
conducted by the Informatics Commission (UISIC) of the
International Union of Speleology (UIS).
Discussions of cave minerals and speleothems are generally conducted by the
members of the Mineralogy Commission of the IUS.
Discussions of cave biological and other specimens are
preliminary at this stage, possibly Mr Arthur Clarke may suggest how
this may be developed?