Physical Data Diagram

SEAD Master structure

Generated: 2014-06-20 14:31:06

Table of Contents


1. Diagram Information
1.1. Basic Information
ProjectSEAD
DiagramSEAD Master structure
CompanyEnvironmental Archaeology Lab. Umeå
AuthorPhilip I Buckland
Version12.24b
Created2011-05-25 16:08:38
Last Modified2013-10-01 11:37:44
1.2. Diagram Description
12.24b - minor modification to tbl_species_associations to allow associations without bibliographic references
12.24 - added tbl_bugs_datesmethods to enable easier sync with BugsCEP, ocrrected names of several references to follow naming convention
12.23 - changes to correctly implement synchronisation of dating data from BugsCEP; reduced length of names of several references to max 63 chrs
12.20 - various minor corrections and notes
12.17 - corrections to table names and id's around samples and sample groups
12.16 - added missing date_updated field to tbl_relative_age_types. A couple of spelling and case corrections on other tables.
12.15 - recreated tbl_dendro_measurement_lookup to correct error in serial creation
12.14 - alterations to tbl_ceramics_measurements and tbl_dendro_measurements making link to tbl_methods rather than built-in
12.13 - alterations to tbl_synonyms
12.12 - added tbl_taxa_images; added tbl_taxa_reference_specimens; altered tbl_species_associations
12.11 - added tbl_association_types
12.10 - major minor revision
12.09 - added generic tables for dendro and ceramics results; preliminary dendro dating tables added
12-12.08 - updated to cover aspects of dendro sample management; rationalised coordinate handling
12 - Major overhaul to include documentation and cater for thin section data, dating, and a number of improvements
11.02 - After discussion with Erik & Toby 2011-11-29
11.01 - safety backup
11 - minor revisions with implications for software
1.3. Diagram Annotation
Table colours:
white - standard
LightGrey - lookup/type definition, manager modified
Aliceblue - lookup/type data, user can add to predefined list
LemonChiffon - bugs legacy PK data
Aqua - nodal data tables
("at error" included in documentation to indicate incomplete documentation?)

1.4. ER Diagram
2. Database
Description
 
Annotation
 
NameDatabase_1
Character Set 
Template 
Tablespace 
Comment 
Create SQL
CREATE DATABASE "Database_1";

3 Domains
4. Tables
4.1 tbl_abundance_elements
Description
Look-up data defining the type of element/part/counted unit represented by the number in tbl_abundances. For insects usually MNI
(Minumum Number of Individuals) but may also be individual sclerites (elytra, thorax etc). For plant macrofossils more commonly parts
used including seed, leaf, bud scale etc.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
abundance_element_id int4      
date_updated timestamp with time zone - - -   now()  
element_description text - - -     Explanation of short name, e.g. Minimum Number of Individuals, base of seed grain, covering of leaf or flower bud
element_name varchar(100) - -     Short name for element, e.g. MNI, seed, leaf
record_type_id int4 - - -     Used to restrict list of available elements according to record type. Enables specific use of single term for multiple proxies whilst avoiding confusion, e.g. MNI insects, MNI seeds
Indexes Unique Columns Method Comment
pk_abundance_elements_idx_pk abundance_element_id  
Constraints Kind Expression Columns Comment
pk_abundance_elements PRIMARY KEY   abundance_element_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_abundance_elements" (
	"abundance_element_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"element_description" text,
	"element_name" varchar(100) NOT NULL,
	"record_type_id" int4,
	CONSTRAINT "pk_abundance_elements" PRIMARY KEY("abundance_element_id"),
	CONSTRAINT "fk_abundance_elements_record_type_id" FOREIGN KEY ("record_type_id")
		REFERENCES "tbl_record_types"("record_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_abundance_elements" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_elements" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_elements" TO "seadworker";
4.2 tbl_abundance_ident_levels
Description
Allows for the storage of multiple instances of a taxon, with different levels of uncertainty in the identification.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
abundance_ident_level_id int4      
abundance_id int4 - -      
date_updated timestamp with time zone - - -   now()  
identification_level_id int4 - -      
Indexes Unique Columns Method Comment
pk_abundance_ident_levels_idx_pk abundance_ident_level_id  
Constraints Kind Expression Columns Comment
pk_abundance_ident_levels PRIMARY KEY   abundance_ident_level_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_abundance_ident_levels" (
	"abundance_ident_level_id" SERIAL NOT NULL,
	"abundance_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"identification_level_id" int4 NOT NULL,
	CONSTRAINT "pk_abundance_ident_levels" PRIMARY KEY("abundance_ident_level_id"),
	CONSTRAINT "fk_abundance_ident_levels_abundance_id" FOREIGN KEY ("abundance_id")
		REFERENCES "tbl_abundances"("abundance_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_abundance_ident_levels_identification_level_id" FOREIGN KEY ("identification_level_id")
		REFERENCES "tbl_identification_levels"("identification_level_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_abundance_ident_levels" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_ident_levels" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_ident_levels" TO "seadworker";
4.3 tbl_abundance_modifications
Description
Modifications (carbonised, corroded, calcified etc) for individual counts. Allows for multiple instances of same taxon but with different
modifications. E.g. Hordeum sp. carbonised AND Hordeum sp. unmodified
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
abundance_modification_id int4      
abundance_id int4 - -      
date_updated timestamp with time zone - - -   now()  
modification_type_id int4 - -      
Indexes Unique Columns Method Comment
pk_abundance_modifications_idx_pk abundance_modification_id  
Constraints Kind Expression Columns Comment
pk_abundance_modifications PRIMARY KEY   abundance_modification_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_abundance_modifications" (
	"abundance_modification_id" SERIAL NOT NULL,
	"abundance_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"modification_type_id" int4 NOT NULL,
	CONSTRAINT "pk_abundance_modifications" PRIMARY KEY("abundance_modification_id"),
	CONSTRAINT "fk_abundance_modifications_abundance_id" FOREIGN KEY ("abundance_id")
		REFERENCES "tbl_abundances"("abundance_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_abundance_modifications_modification_type_id" FOREIGN KEY ("modification_type_id")
		REFERENCES "tbl_modification_types"("modification_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_abundance_modifications" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_modifications" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundance_modifications" TO "seadworker";
4.4 tbl_abundances
Description
Stores individual count, presences or scaled values for biological proxies, linking an analysis_entity to instances of an individual taxon. 
tbl_abundances essentially the species lists, with counts, for a single physical sample, with the intermidiate analysis_entity 
allowing for multiple proxies per sample. Usually stores count value (abundance) but can be presence (1) or catagorical or 
relative scale, as defined by tbl_data_types through tbl_datasets
E.g. 5 MNI of Carabus granulatus in sample S1, where "MNI" is the abundance element recorded.
Annotation
 
Comment
20120503PIB Deleted column "abundance_modification_id" as appeared superfluous with "abundance_id" in tbl_adbundance_modifications
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
abundance_id int4      
abundance int4 - -   0 Usually count value (abundance) but can be presence (1) or catagorical or relative scale, as defined by tbl_data_types through tbl_datasets
abundance_element_id int4 - - -     Allows recording of different parts for single taxon, e.g. leaf, seed, MNI etc.
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_abundances_idx_pk abundance_id  
Constraints Kind Expression Columns Comment
pk_abundances PRIMARY KEY   abundance_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_abundances" (
	"abundance_id" SERIAL NOT NULL,
	"abundance" int4 NOT NULL DEFAULT 0,
	"abundance_element_id" int4,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_abundances" PRIMARY KEY("abundance_id"),
	CONSTRAINT "fk_abundances_abundance_elements_id" FOREIGN KEY ("abundance_element_id")
		REFERENCES "tbl_abundance_elements"("abundance_element_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_abundances_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_abundances_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_abundances" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundances" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_abundances" TO "seadworker";
4.5 tbl_activity_types
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
activity_type_id int4      
activity_type varchar(50) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
description text - - -      
Indexes Unique Columns Method Comment
pk_activity_types_idx_pk activity_type_id  
Constraints Kind Expression Columns Comment
pk_activity_types PRIMARY KEY   activity_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_activity_types" (
	"activity_type_id" SERIAL NOT NULL,
	"activity_type" varchar(50) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_activity_types" PRIMARY KEY("activity_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_activity_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_activity_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_activity_types" TO "seadworker";
4.6 tbl_aggregate_datasets
Description
Collation unit for a set of aggregated samples. Purpose of aggregation (e.g. type of metaanalysis) should eb included in the 
description.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
aggregate_dataset_id int4      
aggregate_dataset_name varchar(255) - - -     Name of aggregated dataset
aggregate_order_type_id int4 - -      
biblio_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
description text - - -     Notes explaining the purpose of the aggregated set of analysis entities
Indexes Unique Columns Method Comment
pk_aggregate_datasets_idx_pk aggregate_dataset_id  
Constraints Kind Expression Columns Comment
pk_aggregate_datasets PRIMARY KEY   aggregate_dataset_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_aggregate_datasets" (
	"aggregate_dataset_id" SERIAL NOT NULL,
	"aggregate_dataset_name" varchar(255),
	"aggregate_order_type_id" int4 NOT NULL,
	"biblio_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_aggregate_datasets" PRIMARY KEY("aggregate_dataset_id"),
	CONSTRAINT "fk_aggregate_datasets_aggregate_order_type_id" FOREIGN KEY ("aggregate_order_type_id")
		REFERENCES "tbl_aggregate_order_types"("aggregate_order_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_aggregate_datasets_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_aggregate_datasets" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_datasets" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_datasets" TO "seadworker";
4.7 tbl_aggregate_order_types
Description
Define order in which the aggregate samples are to be listed. aggregate_order_type used to refer to a field in another table that
can be used to sort the list, e.g. Site name = tbl_sites.site_name.
Annotation
Requires a programatical implementation. Ignore for now.
Comment
20120504PIB: drop this? or replace with alternative?
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
aggregate_order_type_id int4      
aggregate_order_type varchar(60) - -     Aggregate order name, e.g. Site name, Age, Sample Depth, Altitude
date_updated timestamp with time zone - - -   now()  
description text - - -     Explanation of ordering system
Indexes Unique Columns Method Comment
pk_aggregate_order_types_idx_pk aggregate_order_type_id  
Constraints Kind Expression Columns Comment
pk_aggregate_order_types PRIMARY KEY   aggregate_order_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_aggregate_order_types" (
	"aggregate_order_type_id" SERIAL NOT NULL,
	"aggregate_order_type" varchar(60) NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_aggregate_order_types" PRIMARY KEY("aggregate_order_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_aggregate_order_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_order_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_order_types" TO "seadworker";
4.8 tbl_aggregate_sample_ages
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
aggregate_sample_age_id int4      
aggregate_dataset_id int4 - -      
analysis_entity_age_id int4 - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_aggregate_sample_ages_idx_pk aggregate_sample_age_id BTREE  
Constraints Kind Expression Columns Comment
pk_aggregate_sample_ages PRIMARY KEY   aggregate_sample_age_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_aggregate_sample_ages" (
	"aggregate_sample_age_id" SERIAL NOT NULL,
	"aggregate_dataset_id" int4 NOT NULL,
	"analysis_entity_age_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_aggregate_sample_ages" PRIMARY KEY("aggregate_sample_age_id"),
	CONSTRAINT "fk_aggregate_sample_ages_aggregate_dataset_id" FOREIGN KEY ("aggregate_dataset_id")
		REFERENCES "tbl_aggregate_datasets"("aggregate_dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_aggregate_sample_ages_analysis_entity_age_id" FOREIGN KEY ("analysis_entity_age_id")
		REFERENCES "tbl_analysis_entity_ages"("analysis_entity_age_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_aggregate_sample_ages" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_sample_ages" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_sample_ages" TO "seadworker";
4.9 tbl_aggregate_samples
Description
Allows the aggregation of samples for analyses. As links through analysis entities and not physical samples, allows the selection of 
specific proxies for inclusion in the aggregation.
Annotation
Needs to be many-one with tbl_analysis_entities as multiple aggregations may include the same analysis_entities.
Note that the ability to aggregate across proxies differs from Neotoma and should be watched for when transferring.
Comment
20120504PIB: Can we drop aggregate sample name? Seems excessive and unnecessary sample names can be traced.
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
aggregate_sample_id int4      
aggregate_dataset_id int4 - -      
aggregate_sample_name varchar(50) - - -     Optional name for aggregated entity.
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_aggregate_samples_idx_pk aggregate_sample_id  
Constraints Kind Expression Columns Comment
pk_aggregate_samples PRIMARY KEY   aggregate_sample_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_aggregate_samples" (
	"aggregate_sample_id" SERIAL NOT NULL,
	"aggregate_dataset_id" int4 NOT NULL,
	"aggregate_sample_name" varchar(50),
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_aggregate_samples" PRIMARY KEY("aggregate_sample_id"),
	CONSTRAINT "fk_aggragate_samples_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_aggregate_samples_aggregate_dataset_id" FOREIGN KEY ("aggregate_dataset_id")
		REFERENCES "tbl_aggregate_datasets"("aggregate_dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_aggregate_samples" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_samples" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_aggregate_samples" TO "seadworker";
4.10 tbl_alt_ref_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
alt_ref_type_id int4      
alt_ref_type varchar(50) - -      
date_updated timestamp with time zone - - -   now()  
description text - - -      
Indexes Unique Columns Method Comment
pk_alt_ref_types_idx_pk alt_ref_type_id  
Constraints Kind Expression Columns Comment
pk_alt_ref_types PRIMARY KEY   alt_ref_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_alt_ref_types" (
	"alt_ref_type_id" SERIAL NOT NULL,
	"alt_ref_type" varchar(50) NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_alt_ref_types" PRIMARY KEY("alt_ref_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_alt_ref_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_alt_ref_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_alt_ref_types" TO "seadworker";
4.11 tbl_analysis_entities
Description
Allows the representation of multiple proxies for a single physical sample, essentially a virtual entity or statistical sample connecting
physical samples to measurements or abundances (counts) for specific methods. Analysis entities are grouped by dataset, the construction of
datasets are proxy dependent (see tbl_datasets).
Annotation
 
Comment
20120503PIB Deleted column preparation_method_id, but may need to cater for this in datasets...
20120506PIB: deleted method_id and added table for multiple methods per entity
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
analysis_entity_id int4      
dataset_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
physical_sample_id int4 - - -      
Indexes Unique Columns Method Comment
pk_analysis_entities_idx_pk analysis_entity_id  
Constraints Kind Expression Columns Comment
pk_analysis_entities PRIMARY KEY   analysis_entity_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_analysis_entities" (
	"analysis_entity_id" SERIAL NOT NULL,
	"dataset_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"physical_sample_id" int4,
	CONSTRAINT "pk_analysis_entities" PRIMARY KEY("analysis_entity_id"),
	CONSTRAINT "fk_analysis_entities_dataset_id" FOREIGN KEY ("dataset_id")
		REFERENCES "tbl_datasets"("dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_analysis_entities_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_analysis_entities" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_analysis_entities" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_analysis_entities" TO "seadworker";
4.12 tbl_analysis_entity_ages
Description
A virtual object defining a single proxy within a single physical sample, essentially a statistical sample. A single physical sample may 
have multiple analysis_entities each of which provide the linkages for the storage of different proxies. It also allows easier recording 
of specific species when used in (radiocarbon) dating.
Annotation
The logic of this may have to become more flexible to incorporate further proxies or measurement systems which do not quite fit
the SEAD model.
Comment
20120504PIB: Should this be connected to physical sample instead of analysis entities? Allowing multiple ages (from multiple dates) for a sample. At the moment it requires a lot of backtracing to find a sample's age... but then again, it allows... what, exactly?
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
analysis_entity_age_id int4      
age numeric(20,10) - -      
age_older numeric(15,5) - - -      
age_younger numeric(15,5) - - -      
analysis_entity_id int4 - - -      
chronology_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_sample_ages_idx_pk analysis_entity_age_id  
Constraints Kind Expression Columns Comment
pk_sample_ages PRIMARY KEY   analysis_entity_age_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_analysis_entity_ages" (
	"analysis_entity_age_id" SERIAL NOT NULL,
	"age" numeric(20,10) NOT NULL,
	"age_older" numeric(15,5),
	"age_younger" numeric(15,5),
	"analysis_entity_id" int4,
	"chronology_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_sample_ages" PRIMARY KEY("analysis_entity_age_id"),
	CONSTRAINT "fk_analysis_entity_ages_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_analysis_entity_ages_chronology_id" FOREIGN KEY ("chronology_id")
		REFERENCES "tbl_chronologies"("chronology_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_analysis_entity_ages" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_analysis_entity_ages" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_analysis_entity_ages" TO "seadworker";
4.13 tbl_biblio
Description
The following columns are temporary for the import of the Bugs bibliography, and will be deleted when the fields are parsed:
bugs_reference, bugs_author, bugs_title
The link to to tbl_bugs_biblio will retain legacy data for later Bugs updates.
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
biblio_id int4      
author varchar - - -      
biblio_keyword_id int4 - - -      
bugs_author varchar(255) - - -   NULL::character varying  
bugs_biblio_id int4 - - -      
bugs_reference varchar(60) - - -   NULL::character varying  
bugs_title varchar - - -      
collection_or_journal_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
doi varchar(255) - - -   NULL::character varying  
edition varchar(128) - - -   NULL::character varying  
isbn varchar(128) - - -   NULL::character varying  
keywords varchar - - -      
notes text - - -      
number varchar(128) - - -   NULL::character varying  
pages varchar(50) - - -   NULL::character varying  
pdf_link varchar - - -      
publication_type_id int4 - - -      
publisher_id int4 - - -      
title varchar - - -      
volume varchar(128) - - -   NULL::character varying  
year varchar(255) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
pk_biblio_idx_pk biblio_id  
Constraints Kind Expression Columns Comment
pk_biblio PRIMARY KEY   biblio_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_biblio" (
	"biblio_id" SERIAL NOT NULL,
	"author" varchar,
	"biblio_keyword_id" int4,
	"bugs_author" varchar(255) DEFAULT NULL::character varying,
	"bugs_biblio_id" int4,
	"bugs_reference" varchar(60) DEFAULT NULL::character varying,
	"bugs_title" varchar,
	"collection_or_journal_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"doi" varchar(255) DEFAULT NULL::character varying,
	"edition" varchar(128) DEFAULT NULL::character varying,
	"isbn" varchar(128) DEFAULT NULL::character varying,
	"keywords" varchar,
	"notes" text,
	"number" varchar(128) DEFAULT NULL::character varying,
	"pages" varchar(50) DEFAULT NULL::character varying,
	"pdf_link" varchar,
	"publication_type_id" int4,
	"publisher_id" int4,
	"title" varchar,
	"volume" varchar(128) DEFAULT NULL::character varying,
	"year" varchar(255) DEFAULT NULL::character varying,
	CONSTRAINT "pk_biblio" PRIMARY KEY("biblio_id"),
	CONSTRAINT "fk_biblio_collections_or_journals_id" FOREIGN KEY ("collection_or_journal_id")
		REFERENCES "tbl_collections_or_journals"("collection_or_journal_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_biblio_publication_type_id" FOREIGN KEY ("publication_type_id")
		REFERENCES "tbl_publication_types"("publication_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_biblio_publisher_id" FOREIGN KEY ("publisher_id")
		REFERENCES "tbl_publishers"("publisher_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_biblio" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_biblio" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_biblio" TO "seadworker";
4.14 tbl_bugs_abundance_codes
Description
Stores legacy data for synchronising with BugsCEP, will be removed once Bugs features replicated in SEAD.
Stores mapping of bugsdata's fossil records and samples.
Annotation
 
Comment
Note modification of spelling in field since earlier models
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_abundance_code_id int4      
abundance_id int4 - - -      
bugs_fossilbugscode varchar(10) - - -     TFossil.FossilBugsCODE from bugsdata
bugs_samplecode varchar(10) - - -     TSample.SampleCODE from bugsdata
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_bugs_abundance_codes_idx_pk bugs_abundance_code_id  
Constraints Kind Expression Columns Comment
pk_bugs_abundance_codes PRIMARY KEY   bugs_abundance_code_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_abundance_codes" (
	"bugs_abundance_code_id" SERIAL NOT NULL,
	"abundance_id" int4,
	"bugs_fossilbugscode" varchar(10),
	"bugs_samplecode" varchar(10),
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_bugs_abundance_codes" PRIMARY KEY("bugs_abundance_code_id"),
	CONSTRAINT "fk_bugs_abundance_codes_abundance_id" FOREIGN KEY ("abundance_id")
		REFERENCES "tbl_abundances"("abundance_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_bugs_abundance_codes" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_abundance_codes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_abundance_codes" TO "seadworker";
4.15 tbl_bugs_physical_samples
Description
Legacy data for synchronisation with BugsCEP. Bugsdata's TSamples.SampleCODE is equivalent to SEAD's unique identifier for a 
physical sample.
Annotation
May be deleted once BugsCEP's functionality has been duplicated in SEAD.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_physical_sample_id int4      
bugs_samplecode varchar(10) - - -     From Bugsdata.mdb: TSample.SampleCODE
date_updated timestamp with time zone - - -   now()  
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_bugs_physical_samples_idx_pk bugs_physical_sample_id  
Constraints Kind Expression Columns Comment
pk_bugs_physical_samples PRIMARY KEY   bugs_physical_sample_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_physical_samples" (
	"bugs_physical_sample_id" SERIAL NOT NULL,
	"bugs_samplecode" varchar(10),
	"date_updated" timestamp with time zone DEFAULT now(),
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_bugs_physical_samples" PRIMARY KEY("bugs_physical_sample_id"),
	CONSTRAINT "fk_bugs_physical_samples_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_bugs_physical_samples" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_physical_samples" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_physical_samples" TO "seadworker";
4.16 tbl_bugs_sample_groups
Description
Legacy data for synchronisation with BugsCEP. Bugsdata's TCountsheet.CountsheetCODE is essentially equivalent to a sample group
unique id.
Annotation
This may be removed once full duplication of BugsCEP's functionality has been trasfered to SEAD
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_sample_group_id int4      
bugs_countsheetcode varchar(10) - - -     From Bugsdata.mdb: TCountsheets.CountsheetCODE, primary key for countsheets which are equivalent to sample groups in BugsCEP
date_updated timestamp with time zone - - -   now()  
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_bugs_sample_groups_idx_pk bugs_sample_group_id  
Constraints Kind Expression Columns Comment
pk_bugs_sample_groups PRIMARY KEY   bugs_sample_group_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_sample_groups" (
	"bugs_sample_group_id" SERIAL NOT NULL,
	"bugs_countsheetcode" varchar(10),
	"date_updated" timestamp with time zone DEFAULT now(),
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_bugs_sample_groups" PRIMARY KEY("bugs_sample_group_id"),
	CONSTRAINT "fk_bugs_sample_groups_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_bugs_sample_groups" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_sample_groups" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_sample_groups" TO "seadworker";
4.17 tbl_bugs_sites
Description
Used for holding synchronisation data for parallel running with BugsCEP.
Annotation
This will be phased out once all BugsCEP features are available through SEAD.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_sites_id int4      
bugs_sitecode varchar(10) - - -     Reference to primary key in bugsdata.mdb TSite.SiteCODE.
date_updated timestamp with time zone - - -   now()  
site_id int4 - -      
Indexes Unique Columns Method Comment
pk_bugs_sites_idx_pk bugs_sites_id  
Constraints Kind Expression Columns Comment
pk_bugs_sites PRIMARY KEY   bugs_sites_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_sites" (
	"bugs_sites_id" SERIAL NOT NULL,
	"bugs_sitecode" varchar(10),
	"date_updated" timestamp with time zone DEFAULT now(),
	"site_id" int4 NOT NULL,
	CONSTRAINT "pk_bugs_sites" PRIMARY KEY("bugs_sites_id"),
	CONSTRAINT "fk_bugs_sites_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_bugs_sites" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_sites" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_bugs_sites" TO "seadworker";
4.18 tbl_chron_control_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
chron_control_type_id int4      
chron_control_type varchar(50) - - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_chron_control_types_idx_pk chron_control_type_id  
Constraints Kind Expression Columns Comment
pk_chron_control_types PRIMARY KEY   chron_control_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_chron_control_types" (
	"chron_control_type_id" SERIAL NOT NULL,
	"chron_control_type" varchar(50),
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_chron_control_types" PRIMARY KEY("chron_control_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_chron_control_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chron_control_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chron_control_types" TO "seadworker";
4.19 tbl_chron_controls
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
chron_control_id int4      
age numeric(20,5) - - -      
age_limit_older numeric(20,5) - - -      
age_limit_younger numeric(20,5) - - -      
chron_control_type_id int4 - - -      
chronology_id int4 - -      
date_updated timestamp with time zone - - -   now()  
depth_bottom numeric(20,5) - - -      
depth_top numeric(20,5) - - -      
notes text - - -      
Indexes Unique Columns Method Comment
pk_chron_controls_idx_pk chron_control_id BTREE  
Constraints Kind Expression Columns Comment
pk_chron_controls PRIMARY KEY   chron_control_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_chron_controls" (
	"chron_control_id" SERIAL NOT NULL,
	"age" numeric(20,5),
	"age_limit_older" numeric(20,5),
	"age_limit_younger" numeric(20,5),
	"chron_control_type_id" int4,
	"chronology_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"depth_bottom" numeric(20,5),
	"depth_top" numeric(20,5),
	"notes" text,
	CONSTRAINT "pk_chron_controls" PRIMARY KEY("chron_control_id"),
	CONSTRAINT "fk_chron_controls_chron_control_type_id" FOREIGN KEY ("chron_control_type_id")
		REFERENCES "tbl_chron_control_types"("chron_control_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_chron_controls_chronology_id" FOREIGN KEY ("chronology_id")
		REFERENCES "tbl_chronologies"("chronology_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_chron_controls" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chron_controls" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chron_controls" TO "seadworker";
4.20 tbl_chronologies
Description
 
Annotation
 
Comment
20120504PIB: Note that the dropped age type recorded the type of dates (C14 etc) used in constructing the chronology... but is only one per chonology enough? Can a chronology not be made up of mulitple types of age?
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
chronology_id int4      
age_bound_older int4 - - -      
age_bound_younger int4 - - -      
age_model varchar(80) - - -      
age_type_id int4 - -      
chronology_name varchar(80) - - -      
contact_id int4 - - -      
date_prepared timestamp(0) - - -      
date_updated timestamp with time zone - - -   now()  
is_default bool - -   false  
notes text - - -      
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_chronologies_idx_pk chronology_id  
Constraints Kind Expression Columns Comment
pk_chronologies PRIMARY KEY   chronology_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_chronologies" (
	"chronology_id" SERIAL NOT NULL,
	"age_bound_older" int4,
	"age_bound_younger" int4,
	"age_model" varchar(80),
	"age_type_id" int4 NOT NULL,
	"chronology_name" varchar(80),
	"contact_id" int4,
	"date_prepared" timestamp(0),
	"date_updated" timestamp with time zone DEFAULT now(),
	"is_default" bool NOT NULL DEFAULT false,
	"notes" text,
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_chronologies" PRIMARY KEY("chronology_id"),
	CONSTRAINT "fk_chronologies_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_chronologies_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_chronologies" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chronologies" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_chronologies" TO "seadworker";
4.21 tbl_collections_or_journals
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
collection_or_journal_id int4      
collection_or_journal_abbrev varchar(128) - - -      
collection_title_or_journal_name varchar - - -      
date_updated timestamp with time zone - - -   now()  
issn varchar(128) - - -      
number_of_volumes varchar(50) - - -      
publisher_id int4 - - -      
series_editor varchar - - -      
series_title varchar - - -      
volume_editor varchar - - -      
Indexes Unique Columns Method Comment
pk_collections_or_journals_idx_pk collection_or_journal_id BTREE  
Constraints Kind Expression Columns Comment
pk_collections_or_journals PRIMARY KEY   collection_or_journal_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_collections_or_journals" (
	"collection_or_journal_id" SERIAL NOT NULL,
	"collection_or_journal_abbrev" varchar(128),
	"collection_title_or_journal_name" varchar,
	"date_updated" timestamp with time zone DEFAULT now(),
	"issn" varchar(128),
	"number_of_volumes" varchar(50),
	"publisher_id" int4,
	"series_editor" varchar,
	"series_title" varchar,
	"volume_editor" varchar,
	CONSTRAINT "pk_collections_or_journals" PRIMARY KEY("collection_or_journal_id"),
	CONSTRAINT "fk_collections_or_journals_publisher_id" FOREIGN KEY ("publisher_id")
		REFERENCES "tbl_publishers"("publisher_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_collections_or_journals" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_collections_or_journals" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_collections_or_journals" TO "seadworker";
4.22 tbl_colours
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
colour_id int4      
colour_name varchar(30) - -      
date_updated timestamp with time zone - - -   now()  
method_id int4 - -      
rgb int4 - - -      
Indexes Unique Columns Method Comment
pk_colours_idx_pk colour_id  
Constraints Kind Expression Columns Comment
pk_colours PRIMARY KEY   colour_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_colours" (
	"colour_id" SERIAL NOT NULL,
	"colour_name" varchar(30) NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4 NOT NULL,
	"rgb" int4,
	CONSTRAINT "pk_colours" PRIMARY KEY("colour_id"),
	CONSTRAINT "fk_colours_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_colours" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_colours" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_colours" TO "seadworker";
4.23 tbl_contact_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
contact_type_id int4      
contact_type_name varchar(150) - -      
date_updated timestamp with time zone - - -   now()  
description text - - -      
Indexes Unique Columns Method Comment
pk_contact_types_idx_pk contact_type_id  
Constraints Kind Expression Columns Comment
pk_contact_types PRIMARY KEY   contact_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_contact_types" (
	"contact_type_id" SERIAL NOT NULL,
	"contact_type_name" varchar(150) NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_contact_types" PRIMARY KEY("contact_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_contact_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_contact_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_contact_types" TO "seadworker";
4.24 tbl_contacts
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
contact_id int4      
address_1 varchar(255) - - -      
address_2 varchar(255) - - -      
date_updated timestamp with time zone - - -   now()  
email varchar - - -      
first_name varchar(50) - - -      
last_name varchar(100) - - -      
location_id int4 - - -      
phone_number varchar(50) - - -      
url text - - -      
Indexes Unique Columns Method Comment
pk_contacts_idx_pk contact_id  
Constraints Kind Expression Columns Comment
pk_contacts PRIMARY KEY   contact_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_contacts" (
	"contact_id" SERIAL NOT NULL,
	"address_1" varchar(255),
	"address_2" varchar(255),
	"date_updated" timestamp with time zone DEFAULT now(),
	"email" varchar,
	"first_name" varchar(50),
	"last_name" varchar(100),
	"location_id" int4,
	"phone_number" varchar(50),
	"url" text,
	CONSTRAINT "pk_contacts" PRIMARY KEY("contact_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_contacts" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_contacts" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_contacts" TO "seadworker";
4.25 tbl_data_type_groups
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
data_type_group_id int4      
data_type_group_name varchar(25) - - -      
date_updated timestamp with time zone - - -   now()  
description text - - -      
Indexes Unique Columns Method Comment
pk_data_type_groups_idx_pk data_type_group_id  
Constraints Kind Expression Columns Comment
pk_data_type_groups PRIMARY KEY   data_type_group_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_data_type_groups" (
	"data_type_group_id" SERIAL NOT NULL,
	"data_type_group_name" varchar(25),
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	CONSTRAINT "pk_data_type_groups" PRIMARY KEY("data_type_group_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_data_type_groups" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_data_type_groups" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_data_type_groups" TO "seadworker";
4.26 tbl_data_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
data_type_id int4      
data_type_group_id int4 - -      
data_type_name varchar(25) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
definition text - - -      
Indexes Unique Columns Method Comment
pk_samplegroup_data_types_idx_pk data_type_id  
Constraints Kind Expression Columns Comment
pk_samplegroup_data_types PRIMARY KEY   data_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_data_types" (
	"data_type_id" SERIAL NOT NULL,
	"data_type_group_id" int4 NOT NULL,
	"data_type_name" varchar(25) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	"definition" text,
	CONSTRAINT "pk_samplegroup_data_types" PRIMARY KEY("data_type_id"),
	CONSTRAINT "fk_data_types_data_type_group_id" FOREIGN KEY ("data_type_group_id")
		REFERENCES "tbl_data_type_groups"("data_type_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_data_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_data_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_data_types" TO "seadworker";
4.27 tbl_dataset_contacts
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dataset_contact_id int4      
contact_id int4 - -      
contact_type_id int4 - -      
dataset_id int4 - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_dataset_contacts_idx_pk dataset_contact_id BTREE  
Constraints Kind Expression Columns Comment
pk_dataset_contacts PRIMARY KEY   dataset_contact_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dataset_contacts" (
	"dataset_contact_id" SERIAL NOT NULL,
	"contact_id" int4 NOT NULL,
	"contact_type_id" int4 NOT NULL,
	"dataset_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_dataset_contacts" PRIMARY KEY("dataset_contact_id"),
	CONSTRAINT "fk_dataset_contacts_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dataset_contacts_contact_type_id" FOREIGN KEY ("contact_type_id")
		REFERENCES "tbl_contact_types"("contact_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dataset_contacts_dataset_id" FOREIGN KEY ("dataset_id")
		REFERENCES "tbl_datasets"("dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dataset_contacts" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_contacts" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_contacts" TO "seadworker";
4.28 tbl_dataset_masters
Description
Major grouping unit for datasets, usually designating contributing database, project, user or lab.
E.g. BugsCEP, MAL, Lund Dendro lab
Annotation
Probably need to define protocols for what constitutes a master dataset
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
master_set_id int4      
biblio_id int4 - - -     Primary reference for master dataset if available, e.g. Buckland & Buckland 2006 for BugsCEP
contact_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
master_name varchar(100) - - -     Identification of master dataset, e.g. MAL, BugsCEP, Dendrolab
master_notes text - - -     Description of master dataset, its form (e.g. database, lab) and any other relevant information for tracing it.
url text - - -     Website or other url for master dataset, be it a project, lab or... other
Indexes Unique Columns Method Comment
pk_dataset_masters_idx_pk master_set_id  
Constraints Kind Expression Columns Comment
pk_dataset_masters PRIMARY KEY   master_set_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dataset_masters" (
	"master_set_id" SERIAL NOT NULL,
	"biblio_id" int4,
	"contact_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"master_name" varchar(100),
	"master_notes" text,
	"url" text,
	CONSTRAINT "pk_dataset_masters" PRIMARY KEY("master_set_id"),
	CONSTRAINT "fk_dataset_masters_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dataset_masters_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dataset_masters" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_masters" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_masters" TO "seadworker";
4.29 tbl_dataset_submission_types
Description
Lookup data listing types of dataset submission, e.g. original submission, ingestion from other database
Annotation
Any dataset can have multiple submissions to aid quality assurance and trasparency when tracing origins.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
submission_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -     Explanation of submission type, explaining clearly data ingestion mechanism
submission_type varchar(60) - -     Descriptive name for type of submission, e.g. original submission, ingestion from another database
Indexes Unique Columns Method Comment
pk_dataset_submission_types_idx_pk submission_type_id  
Constraints Kind Expression Columns Comment
pk_dataset_submission_types PRIMARY KEY   submission_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dataset_submission_types" (
	"submission_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"submission_type" varchar(60) NOT NULL,
	CONSTRAINT "pk_dataset_submission_types" PRIMARY KEY("submission_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dataset_submission_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_submission_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_submission_types" TO "seadworker";
4.30 tbl_dataset_submissions
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dataset_submission_id int4      
contact_id int4 - -      
dataset_id int4 - -      
date_submitted date - -      
date_updated timestamp with time zone - - -   now()  
notes text - - -     Any details of submission not covered by submission_type information, such as name of source from which submission originates if not covered elsewhere in database, e.g. from BugsCEP
submission_type_id int4 - -      
Indexes Unique Columns Method Comment
pk_dataset_submissions_idx_pk dataset_submission_id  
Constraints Kind Expression Columns Comment
pk_dataset_submissions PRIMARY KEY   dataset_submission_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dataset_submissions" (
	"dataset_submission_id" SERIAL NOT NULL,
	"contact_id" int4 NOT NULL,
	"dataset_id" int4 NOT NULL,
	"date_submitted" date NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"notes" text,
	"submission_type_id" int4 NOT NULL,
	CONSTRAINT "pk_dataset_submissions" PRIMARY KEY("dataset_submission_id"),
	CONSTRAINT "fk_dataset_submission_submission_type_id" FOREIGN KEY ("submission_type_id")
		REFERENCES "tbl_dataset_submission_types"("submission_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dataset_submissions_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dataset_submissions_dataset_id" FOREIGN KEY ("dataset_id")
		REFERENCES "tbl_datasets"("dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dataset_submissions" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_submissions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dataset_submissions" TO "seadworker";
4.31 tbl_datasets
Description
Datasets group collections of analysis_entities in a manner that is meaningful for the proxy concerned. For biological proxies, the 
dataset is usually equivalent of a spreadsheet containing samples and taxa for a single proxy.
method of analysis (eg phosphates through citric acid extraction... etc)
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dataset_id int4      
biblio_id int4 - - -      
data_type_id int4 - -      
dataset_name varchar(50) - -     Something uniquely identifying the dataset for this site. May be same as sample group name, or created adhoc if necessary, but preferably with some meaning.
date_updated timestamp with time zone - - -   now()  
master_set_id int4 - - -      
method_id int4 - - -      
project_id int4 - - -      
updated_dataset_id int4 - - -      
Indexes Unique Columns Method Comment
pk_datasets_idx_pk dataset_id  
Constraints Kind Expression Columns Comment
pk_datasets PRIMARY KEY   dataset_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_datasets" (
	"dataset_id" SERIAL NOT NULL,
	"biblio_id" int4,
	"data_type_id" int4 NOT NULL,
	"dataset_name" varchar(50) NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"master_set_id" int4,
	"method_id" int4,
	"project_id" int4,
	"updated_dataset_id" int4,
	CONSTRAINT "pk_datasets" PRIMARY KEY("dataset_id"),
	CONSTRAINT "fk_datasets_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_datasets_data_type_id" FOREIGN KEY ("data_type_id")
		REFERENCES "tbl_data_types"("data_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_datasets_master_set_id" FOREIGN KEY ("master_set_id")
		REFERENCES "tbl_dataset_masters"("master_set_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_datasets_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_datasets_updated_dataset_id" FOREIGN KEY ("updated_dataset_id")
		REFERENCES "tbl_datasets"("dataset_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_datasets_project_id" FOREIGN KEY ("project_id")
		REFERENCES "tbl_projects"("project_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_datasets" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_datasets" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_datasets" TO "seadworker";
4.32 tbl_dating_labs
Description
Radiocarbon laboratory identifiers and names from http://www.radiocarbon.org/Info/labcodes.html
Allows transparency in C14 and other radiometric dating records.
Annotation
 
Comment
20120504PIB: reduced this table and linked to tbl_contacts for address related data
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dating_lab_id int4      
contact_id int4 - - -     Address details are stored in tbl_contacts
country_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
international_lab_id varchar(10) - -     International standard radiocarbon lab identifier. From http://www.radiocarbon.org/Info/labcodes.html
lab_name varchar(100) - - -   NULL::character varying International standard name of radiocarbon lab, from http://www.radiocarbon.org/Info/labcodes.html
Indexes Unique Columns Method Comment
pk_dating_labs_idx_pk dating_lab_id  
Constraints Kind Expression Columns Comment
pk_dating_labs PRIMARY KEY   dating_lab_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dating_labs" (
	"dating_lab_id" SERIAL NOT NULL,
	"contact_id" int4,
	"country_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"international_lab_id" varchar(10) NOT NULL,
	"lab_name" varchar(100) DEFAULT NULL::character varying,
	CONSTRAINT "pk_dating_labs" PRIMARY KEY("dating_lab_id"),
	CONSTRAINT "fk_dating_labs_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dating_labs" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_dating_labs" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dating_labs" TO "seadworker";
4.33 tbl_dimensions
Description
Expanded scope to now include dimensions such as:
Pre-burned weight
Post-burning weight
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dimension_id int4      
date_updated timestamp with time zone - - -   now()  
dimension_abbrev varchar(10) - - -      
dimension_description text - - -      
dimension_name varchar(50) - -      
method_group_id int4 - - -     Limits choice of dimension by method group (e.g. size measurements, coordinate systems)
unit_id int4 - - -      
Indexes Unique Columns Method Comment
pk_dimensions_idx_pk dimension_id  
Constraints Kind Expression Columns Comment
pk_dimensions PRIMARY KEY   dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dimensions" (
	"dimension_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_abbrev" varchar(10),
	"dimension_description" text,
	"dimension_name" varchar(50) NOT NULL,
	"method_group_id" int4,
	"unit_id" int4,
	CONSTRAINT "pk_dimensions" PRIMARY KEY("dimension_id"),
	CONSTRAINT "fk_dimensions_unit_id" FOREIGN KEY ("unit_id")
		REFERENCES "tbl_units"("unit_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_dimensions_method_group_id" FOREIGN KEY ("method_group_id")
		REFERENCES "tbl_method_groups"("method_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_dimensions" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_dimensions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_dimensions" TO "seadworker";
4.34 tbl_ecocode_definitions
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ecocode_definition_id int4      
abbreviation varchar(10) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
definition text - - -      
ecocode_group_id int4 - - -   0  
label varchar(150) - - -   NULL::character varying  
notes text - - -      
sort_order int2 - - -   0  
Indexes Unique Columns Method Comment
pk_ecocode_definitions_idx_pk ecocode_definition_id  
Constraints Kind Expression Columns Comment
pk_ecocode_definitions PRIMARY KEY   ecocode_definition_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ecocode_definitions" (
	"ecocode_definition_id" SERIAL NOT NULL,
	"abbreviation" varchar(10) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	"definition" text,
	"ecocode_group_id" int4 DEFAULT 0,
	"label" varchar(150) DEFAULT NULL::character varying,
	"notes" text,
	"sort_order" int2 DEFAULT 0,
	CONSTRAINT "pk_ecocode_definitions" PRIMARY KEY("ecocode_definition_id"),
	CONSTRAINT "fk_ecocode_definitions_ecocode_group_id" FOREIGN KEY ("ecocode_group_id")
		REFERENCES "tbl_ecocode_groups"("ecocode_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_ecocode_definitions" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_definitions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_definitions" TO "seadworker";
4.35 tbl_ecocode_groups
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ecocode_group_id int4      
date_updated timestamp with time zone - - -   now()  
definition text - - -   NULL::character varying  
ecocode_system_id int4 - - -   0  
label varchar(150) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
tbl_ecocode_groups_idx_ecocodesystemid - ecocode_system_id BTREE  
tbl_ecocode_groups_idx_label - label BTREE  
pk_ecocode_groups_idx_pk ecocode_group_id  
Constraints Kind Expression Columns Comment
pk_ecocode_groups PRIMARY KEY   ecocode_group_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ecocode_groups" (
	"ecocode_group_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"definition" text DEFAULT NULL::character varying,
	"ecocode_system_id" int4 DEFAULT 0,
	"label" varchar(150) DEFAULT NULL::character varying,
	CONSTRAINT "pk_ecocode_groups" PRIMARY KEY("ecocode_group_id"),
	CONSTRAINT "fk_ecocode_groups_ecocode_system_id" FOREIGN KEY ("ecocode_system_id")
		REFERENCES "tbl_ecocode_systems"("ecocode_system_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_ecocode_groups_idx_ecocodesystemid" ON "tbl_ecocode_groups" USING BTREE (
	"ecocode_system_id"
);


CREATE INDEX "tbl_ecocode_groups_idx_label" ON "tbl_ecocode_groups" USING BTREE (
	"label"
);


ALTER TABLE "tbl_ecocode_groups" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_groups" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_groups" TO "seadworker";
4.36 tbl_ecocode_systems
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ecocode_system_id int4      
biblio_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
definition text - - -   NULL::character varying  
name varchar(50) - - -   NULL::character varying  
notes text - - -      
Indexes Unique Columns Method Comment
tbl_ecocode_systems_biblioid - biblio_id BTREE  
tbl_ecocode_systems_ecocodegroupid - name BTREE  
pk_ecocode_systems_idx_pk ecocode_system_id  
Constraints Kind Expression Columns Comment
pk_ecocode_systems PRIMARY KEY   ecocode_system_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ecocode_systems" (
	"ecocode_system_id" SERIAL NOT NULL,
	"biblio_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"definition" text DEFAULT NULL::character varying,
	"name" varchar(50) DEFAULT NULL::character varying,
	"notes" text,
	CONSTRAINT "pk_ecocode_systems" PRIMARY KEY("ecocode_system_id"),
	CONSTRAINT "fk_ecocode_systems_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_ecocode_systems_biblioid" ON "tbl_ecocode_systems" USING BTREE (
	"biblio_id"
);


CREATE INDEX "tbl_ecocode_systems_ecocodegroupid" ON "tbl_ecocode_systems" USING BTREE (
	"name"
);


ALTER TABLE "tbl_ecocode_systems" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_systems" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocode_systems" TO "seadworker";
4.37 tbl_ecocodes
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ecocode_id int4      
date_updated timestamp with time zone - - -   now()  
ecocode_definition_id int4 - - -   0  
taxon_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_ecocodes_idx_pk ecocode_id BTREE  
Constraints Kind Expression Columns Comment
pk_ecocodes PRIMARY KEY   ecocode_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ecocodes" (
	"ecocode_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"ecocode_definition_id" int4 DEFAULT 0,
	"taxon_id" int4 DEFAULT 0,
	CONSTRAINT "pk_ecocodes" PRIMARY KEY("ecocode_id"),
	CONSTRAINT "fk_ecocodes_ecocodedef_id" FOREIGN KEY ("ecocode_definition_id")
		REFERENCES "tbl_ecocode_definitions"("ecocode_definition_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_ecocodes_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_ecocodes" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocodes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_ecocodes" TO "seadworker";
4.38 tbl_feature_types
Description
Type of physical object or area being sampled, as defined by the collector (e.g. archaeologist, geomorphologist) or an accepted 
frame of refererence (e.g. book, survey). A physical sample may represent multiple features (e.g. a layer within a well could be 
recorded as two contexts/features) and thus more than one feature type. Similarly, a single feature may have more than one sample.
Annotation
Need to think about this and make sure it is well documented/explained for the users.
Watch for potential overlap with tbl_sample_group_sampling_contexts and tbl_sampling_methods.
This may become more complex than initially considered and require reworking, especially for the integration of dendro and 
ceramics data.

Hierarchy suggestion: Subterms include containing term in their description. See Building and Structure as example, or Pit and Well etc.
Majority of terms adopted from MoLAS: http://www.museumoflondonarchaeology.org.uk/NR/rdonlyres/056B4AFD-AB5F-45AF-9097-5A53FFDC1F94/0/MoLASManual94.pdf
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
feature_type_id int4      
date_updated timestamp with time zone - - -   now()  
feature_type_description text - - -      
feature_type_name varchar(128) - - -      
Indexes Unique Columns Method Comment
pk_feature_type_id_idx_pk feature_type_id  
Constraints Kind Expression Columns Comment
pk_feature_type_id PRIMARY KEY   feature_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_feature_types" (
	"feature_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"feature_type_description" text,
	"feature_type_name" varchar(128),
	CONSTRAINT "pk_feature_type_id" PRIMARY KEY("feature_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_feature_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_feature_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_feature_types" TO "seadworker";
4.39 tbl_features
Description
The actual physical object or area being sampled, as defined by the collector (e.g. archaeologist, geomorphologist) or an accepted 
frame of refererence (e.g. book, survey). A physical sample may represent multiple features (e.g. a layer within a well could be 
recorded as two contexts/features) and thus more than one feature type. Similarly, a single feature may have more than one sample.
Annotation
There is potential for confusion here, and we'll have to be careful about constraining/visualising relationships in interfaces.
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
feature_id int4      
date_updated timestamp with time zone - - -   now()  
feature_description text - - -     Description of the feature. May include any field notes, lab notes or interpretation information useful for interpreting the sample data.
feature_name varchar - - -     Estabilished reference name/number for the FEATURE (note: NOT the sample). E.g. Well 47, Anl.3, C107. Remember that a sample can come from multiple features (e.g. C107 in Well 47) but each feature should have a separate record.
feature_type_id int4 - -      
Indexes Unique Columns Method Comment
pk_features_idx_pk feature_id  
Constraints Kind Expression Columns Comment
pk_features PRIMARY KEY   feature_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_features" (
	"feature_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"feature_description" text,
	"feature_name" varchar,
	"feature_type_id" int4 NOT NULL,
	CONSTRAINT "pk_features" PRIMARY KEY("feature_id"),
	CONSTRAINT "fk_feature_type_id_feature_type_id" FOREIGN KEY ("feature_type_id")
		REFERENCES "tbl_feature_types"("feature_type_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_features" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_features" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_features" TO "seadworker";
4.40 tbl_geochron_refs
Description
Bibliographic records for specific geochronology dates.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
geochron_ref_id int4      
biblio_id int4 - -     Reference for specific date
date_updated timestamp with time zone - - -   now()  
geochron_id int4 - -      
Indexes Unique Columns Method Comment
pk_geochron_refs_idx_pk geochron_ref_id  
Constraints Kind Expression Columns Comment
pk_geochron_refs PRIMARY KEY   geochron_ref_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_geochron_refs" (
	"geochron_ref_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"geochron_id" int4 NOT NULL,
	CONSTRAINT "pk_geochron_refs" PRIMARY KEY("geochron_ref_id"),
	CONSTRAINT "fk_geochron_refs_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_geochron_refs_geochron_id" FOREIGN KEY ("geochron_id")
		REFERENCES "tbl_geochronology"("geochron_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_geochron_refs" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_geochron_refs" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_geochron_refs" TO "seadworker";
4.41 tbl_geochronology
Description
Geochronological, i.e. absolute, dating data.
Annotation
Note that age types (14C, Argon-Argon etc) are stored with the appropriate methods and accessed through tbl_analysis_entities 
and then datasets and methods.
Going to have to think about this one - storing identified material in abundances, or in new table attached here?
Comment
20130722PIB: Altered field uncertainty (varchar) to dating_uncertainty_id and linked to tbl_dating_uncertainty to enable lookup of uncertainty modifiers for dates
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
geochron_id int4      
age numeric(20,5) - - -     Radiocarbon (or other radiometric) age.
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
dating_lab_id int4 - - -      
delta_13c numeric(10,5) - - -     Delta 13C where available for calibration correction.
error_older numeric(20,5) - - -     Plus (+) side of the measured error (set same as error_younger if standard +/- error)
error_younger numeric(20,5) - - -     Minus (-) side of the measured error (set same as error_younger if standard +/- error)
lab_number varchar(40) - - -      
notes text - - -     Notes specific to this date
dating_uncertainty_id int4 - - -      
Indexes Unique Columns Method Comment
pk_geochronology_idx_pk geochron_id  
Constraints Kind Expression Columns Comment
pk_geochronology PRIMARY KEY   geochron_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_geochronology" (
	"geochron_id" SERIAL NOT NULL,
	"age" numeric(20,5),
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dating_lab_id" int4,
	"delta_13c" numeric(10,5),
	"error_older" numeric(20,5),
	"error_younger" numeric(20,5),
	"lab_number" varchar(40),
	"notes" text,
	"dating_uncertainty_id" int4,
	CONSTRAINT "pk_geochronology" PRIMARY KEY("geochron_id"),
	CONSTRAINT "fk_geochronology_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_geochronology_dating_labs_id" FOREIGN KEY ("dating_lab_id")
		REFERENCES "tbl_dating_labs"("dating_lab_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_geochronology_dating_uncertainty_id" FOREIGN KEY ("dating_uncertainty_id")
		REFERENCES "tbl_dating_uncertainty"("dating_uncertainty_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_geochronology" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_geochronology" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_geochronology" TO "seadworker";
4.42 tbl_horizons
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
horizon_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
horizon_name varchar(15) - -      
method_id int4 - -      
Indexes Unique Columns Method Comment
pk_horizons_idx_pk horizon_id  
Constraints Kind Expression Columns Comment
pk_horizons PRIMARY KEY   horizon_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_horizons" (
	"horizon_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"horizon_name" varchar(15) NOT NULL,
	"method_id" int4 NOT NULL,
	CONSTRAINT "pk_horizons" PRIMARY KEY("horizon_id"),
	CONSTRAINT "fk_horizons_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_horizons" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_horizons" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_horizons" TO "seadworker";
4.43 tbl_identification_levels
Description
Look-up data for taxonomic resolution/uncertainty in identification, and taxonomic level of uncertainty.
e.g. c.f. Family, c.f. Genus, c.f. Species.
Annotation
May need to test and think about the implimentation of this one. Does it smoothly cover the requirements, and allow easy export
of data in standard forms? E.g. Agabus c.f. bigutatus
Also need to enforce rules for which combinations are viable. E.g. not allow Genus Sp. defined to species level
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
identification_level_id int4      
date_updated timestamp with time zone - - -   now()  
identification_level_abbrev varchar(50) - - -   NULL::character varying  
identification_level_name varchar(50) - - -   NULL::character varying  
notes text - - -      
Indexes Unique Columns Method Comment
pk_identification_levels_idx_pk identification_level_id  
Constraints Kind Expression Columns Comment
pk_identification_levels PRIMARY KEY   identification_level_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_identification_levels" (
	"identification_level_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"identification_level_abbrev" varchar(50) DEFAULT NULL::character varying,
	"identification_level_name" varchar(50) DEFAULT NULL::character varying,
	"notes" text,
	CONSTRAINT "pk_identification_levels" PRIMARY KEY("identification_level_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_identification_levels" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_identification_levels" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_identification_levels" TO "seadworker";
4.44 tbl_image_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
image_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
image_type varchar(40) - -      
Indexes Unique Columns Method Comment
pk_image_types_idx_pk image_type_id  
Constraints Kind Expression Columns Comment
pk_image_types PRIMARY KEY   image_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_image_types" (
	"image_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"image_type" varchar(40) NOT NULL,
	CONSTRAINT "pk_image_types" PRIMARY KEY("image_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_image_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_image_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_image_types" TO "seadworker";
4.45 tbl_imported_taxa_replacements
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
imported_taxa_replacement_id int4      
date_updated timestamp with time zone - - -   now()  
imported_name_replaced varchar(100) - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_imported_taxa_replacements_idx_pk imported_taxa_replacement_id BTREE  
Constraints Kind Expression Columns Comment
pk_imported_taxa_replacements PRIMARY KEY   imported_taxa_replacement_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_imported_taxa_replacements" (
	"imported_taxa_replacement_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"imported_name_replaced" varchar(100) NOT NULL,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_imported_taxa_replacements" PRIMARY KEY("imported_taxa_replacement_id"),
	CONSTRAINT "fk_imported_taxa_replacements_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_imported_taxa_replacements" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_imported_taxa_replacements" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_imported_taxa_replacements" TO "seadworker";
4.46 tbl_languages
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
language_id int4      
date_updated timestamp with time zone - - -   now()  
language_name_english varchar(100) - - -   NULL::character varying  
language_name_native varchar(100) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
tbl_languages_language_id - language_id BTREE  
pk_languages_idx_pk language_id  
Constraints Kind Expression Columns Comment
pk_languages PRIMARY KEY   language_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_languages" (
	"language_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"language_name_english" varchar(100) DEFAULT NULL::character varying,
	"language_name_native" varchar(100) DEFAULT NULL::character varying,
	CONSTRAINT "pk_languages" PRIMARY KEY("language_id")
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_languages_language_id" ON "tbl_languages" USING BTREE (
	"language_id"
);


ALTER TABLE "tbl_languages" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_languages" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_languages" TO "seadworker";
4.47 tbl_lithology
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
lithology_id int4      
date_updated timestamp with time zone - - -   now()  
depth_bottom numeric(20,5) - - -      
depth_top numeric(20,5) - -      
description text - -      
lower_boundary varchar(255) - - -      
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_lithologies_idx_pk lithology_id BTREE  
Constraints Kind Expression Columns Comment
pk_lithologies PRIMARY KEY   lithology_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_lithology" (
	"lithology_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"depth_bottom" numeric(20,5),
	"depth_top" numeric(20,5) NOT NULL,
	"description" text NOT NULL,
	"lower_boundary" varchar(255),
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_lithologies" PRIMARY KEY("lithology_id"),
	CONSTRAINT "fk_lithology_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_lithology" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_lithology" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_lithology" TO "seadworker";
4.48 tbl_location_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
location_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
location_type varchar(40) - - -      
Indexes Unique Columns Method Comment
pk_location_types_idx_pk location_type_id  
Constraints Kind Expression Columns Comment
pk_location_types PRIMARY KEY   location_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_location_types" (
	"location_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"location_type" varchar(40),
	CONSTRAINT "pk_location_types" PRIMARY KEY("location_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_location_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_location_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_location_types" TO "seadworker";
4.49 tbl_locations
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
location_id int4      
date_updated timestamp with time zone - - -   now()  
default_lat_dd numeric(18,10) - - -     Default latitude in decimal degrees for location, e.g. mid point of country. Leave empty if not known.
default_long_dd numeric(18,10) - - -     Default longitude in decimal degrees for location, e.g. mid point of country
location_name varchar(255) - -      
location_type_id int4 - -      
Indexes Unique Columns Method Comment
pk_locations_idx_pk location_id  
Constraints Kind Expression Columns Comment
pk_locations PRIMARY KEY   location_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_locations" (
	"location_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"default_lat_dd" numeric(18,10),
	"default_long_dd" numeric(18,10),
	"location_name" varchar(255) NOT NULL,
	"location_type_id" int4 NOT NULL,
	CONSTRAINT "pk_locations" PRIMARY KEY("location_id"),
	CONSTRAINT "fk_locations_location_type_id" FOREIGN KEY ("location_type_id")
		REFERENCES "tbl_location_types"("location_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_locations" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_locations" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_locations" TO "seadworker";
4.50 tbl_mcr_names
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxon_id int4      
comparison_notes varchar(255) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
mcr_name_trim varchar(80) - - -   NULL::character varying  
mcr_number int2 - - -   0  
mcr_species_name varchar(200) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
pk_mcr_names_idx_pk taxon_id BTREE  
Constraints Kind Expression Columns Comment
pk_mcr_names PRIMARY KEY   taxon_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_mcr_names" (
	"taxon_id" SERIAL NOT NULL,
	"comparison_notes" varchar(255) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	"mcr_name_trim" varchar(80) DEFAULT NULL::character varying,
	"mcr_number" int2 DEFAULT 0,
	"mcr_species_name" varchar(200) DEFAULT NULL::character varying,
	CONSTRAINT "pk_mcr_names" PRIMARY KEY("taxon_id"),
	CONSTRAINT "fk_mcr_names_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_mcr_names" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcr_names" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcr_names" TO "seadworker";
4.51 tbl_mcr_summary_data
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
mcr_summary_data_id int4      
cog_mid_tmax int2 - - -   0  
cog_mid_trange int2 - - -   0  
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - -      
tmax_hi int2 - - -   0  
tmax_lo int2 - - -   0  
tmin_hi int2 - - -   0  
tmin_lo int2 - - -   0  
trange_hi int2 - - -   0  
trange_lo int2 - - -   0  
Indexes Unique Columns Method Comment
key_mcr_summary_data_taxon_id_idx_ui taxon_id BTREE  
pk_mcr_summary_data_idx_pk mcr_summary_data_id  
Constraints Kind Expression Columns Comment
pk_mcr_summary_data PRIMARY KEY   mcr_summary_data_id  
key_mcr_summary_data_taxon_id UNIQUE   taxon_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_mcr_summary_data" (
	"mcr_summary_data_id" SERIAL NOT NULL,
	"cog_mid_tmax" int2 DEFAULT 0,
	"cog_mid_trange" int2 DEFAULT 0,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 NOT NULL,
	"tmax_hi" int2 DEFAULT 0,
	"tmax_lo" int2 DEFAULT 0,
	"tmin_hi" int2 DEFAULT 0,
	"tmin_lo" int2 DEFAULT 0,
	"trange_hi" int2 DEFAULT 0,
	"trange_lo" int2 DEFAULT 0,
	CONSTRAINT "pk_mcr_summary_data" PRIMARY KEY("mcr_summary_data_id"),
	CONSTRAINT "key_mcr_summary_data_taxon_id" UNIQUE("taxon_id"),
	CONSTRAINT "fk_mcr_summary_data_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_mcr_summary_data" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcr_summary_data" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcr_summary_data" TO "seadworker";
4.52 tbl_mcrdata_birmbeetledat
Description
mcr_data should be bitstring datatype...? Ask Toby
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
mcrdata_birmbeetledat_id int4      
date_updated timestamp with time zone - - -   now()  
mcr_data text - - -      
mcr_row int2 - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_mcrdata_birmbeetledat_idx_pk mcrdata_birmbeetledat_id  
Constraints Kind Expression Columns Comment
pk_mcrdata_birmbeetledat PRIMARY KEY   mcrdata_birmbeetledat_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_mcrdata_birmbeetledat" (
	"mcrdata_birmbeetledat_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"mcr_data" text,
	"mcr_row" int2 NOT NULL,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_mcrdata_birmbeetledat" PRIMARY KEY("mcrdata_birmbeetledat_id"),
	CONSTRAINT "fk_mcrdata_birmbeetledat_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_mcrdata_birmbeetledat" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcrdata_birmbeetledat" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_mcrdata_birmbeetledat" TO "seadworker";
4.53 tbl_measured_values
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
measured_value_id int4      
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
measured_value numeric(20,10) - -      
Indexes Unique Columns Method Comment
pk_measured_values_idx_pk measured_value_id  
Constraints Kind Expression Columns Comment
pk_measured_values PRIMARY KEY   measured_value_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_measured_values" (
	"measured_value_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"measured_value" numeric(20,10) NOT NULL,
	CONSTRAINT "pk_measured_values" PRIMARY KEY("measured_value_id"),
	CONSTRAINT "fk_measured_values_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_measured_values" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_measured_values" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_measured_values" TO "seadworker";
4.54 tbl_measured_value_dimensions
Description
Stores eg. weight of samples used in measured value table, pre and post burning weights
But could also be volume or other dimension measures
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
measured_value_dimension_id int4      
date_updated timestamp with time zone - - -   now()  
dimension_id int4 - -      
dimension_value numeric(18,10) - -      
measured_value_id int4 - -      
Indexes Unique Columns Method Comment
pk_measured_weights_idx_pk measured_value_dimension_id  
Constraints Kind Expression Columns Comment
pk_measured_weights PRIMARY KEY   measured_value_dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_measured_value_dimensions" (
	"measured_value_dimension_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_id" int4 NOT NULL,
	"dimension_value" numeric(18,10) NOT NULL,
	"measured_value_id" int4 NOT NULL,
	CONSTRAINT "pk_measured_weights" PRIMARY KEY("measured_value_dimension_id"),
	CONSTRAINT "fk_measured_weights_value_id" FOREIGN KEY ("measured_value_id")
		REFERENCES "tbl_measured_values"("measured_value_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_measured_value_dimensions_dimension_id" FOREIGN KEY ("dimension_id")
		REFERENCES "tbl_dimensions"("dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_measured_value_dimensions" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_measured_value_dimensions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_measured_value_dimensions" TO "seadworker";
4.55 tbl_method_groups
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
method_group_id int4      
date_updated timestamp with time zone - - -   now()  
description text - -      
group_name varchar(100) - -      
Indexes Unique Columns Method Comment
pk_method_groups_idx_pk method_group_id  
Constraints Kind Expression Columns Comment
pk_method_groups PRIMARY KEY   method_group_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_method_groups" (
	"method_group_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text NOT NULL,
	"group_name" varchar(100) NOT NULL,
	CONSTRAINT "pk_method_groups" PRIMARY KEY("method_group_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_method_groups" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_method_groups" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_method_groups" TO "seadworker";
4.56 tbl_methods
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
method_id int4      
biblio_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
description text - -      
method_abbrev_or_alt_name varchar(50) - - -      
method_group_id int4 - -      
method_name varchar(50) - -      
record_type_id int4 - - -      
unit_id int4 - - -      
Indexes Unique Columns Method Comment
pk_methods_idx_pk method_id  
Constraints Kind Expression Columns Comment
pk_methods PRIMARY KEY   method_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_methods" (
	"method_id" SERIAL NOT NULL,
	"biblio_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text NOT NULL,
	"method_abbrev_or_alt_name" varchar(50),
	"method_group_id" int4 NOT NULL,
	"method_name" varchar(50) NOT NULL,
	"record_type_id" int4,
	"unit_id" int4,
	CONSTRAINT "pk_methods" PRIMARY KEY("method_id"),
	CONSTRAINT "fk_methods_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_methods_method_group_id" FOREIGN KEY ("method_group_id")
		REFERENCES "tbl_method_groups"("method_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_methods_record_type_id" FOREIGN KEY ("record_type_id")
		REFERENCES "tbl_record_types"("record_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_methods_unit_id" FOREIGN KEY ("unit_id")
		REFERENCES "tbl_units"("unit_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_methods" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_methods" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_methods" TO "seadworker";
4.57 tbl_modification_types
Description
Modifications/changes in state for (sub)fossils, e.g. carbonised, calcified, corroded, mineralised.
Annotation
Could be important taphonomic information for future studies.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
modification_type_id int4      
date_updated timestamp with time zone - - -   now()  
modification_type_description text - - -     Clear explanation of modification so that name makes sense to non-domain scientists
modification_type_name varchar(128) - - -     Short name of modification, e.g. carbonised
Indexes Unique Columns Method Comment
pk_modification_types_idx_pk modification_type_id  
Constraints Kind Expression Columns Comment
pk_modification_types PRIMARY KEY   modification_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_modification_types" (
	"modification_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"modification_type_description" text,
	"modification_type_name" varchar(128),
	CONSTRAINT "pk_modification_types" PRIMARY KEY("modification_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_modification_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_modification_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_modification_types" TO "seadworker";
4.58 tbl_physical_samples
Description
Physical samples represent a sampling unit from which analyses are extracted. For soil samples, these represent a lump of 
sediment, from which various analyses can be extracted (e.g. pollen, insects etc). 
For dendro data...
Annotation
Have to look at logic of object representation for dendro and ceramics...
Comment
20120504PIB: deleted columns XYZ and created external tbl_sample_coodinates
20120506PIB: deleted columns depth_top & depth_bottom and moved to tbl_sample_dimensions
20130416PIB: changed to date_sampled from date to varchar format to increase flexibility
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
physical_sample_id int4      
alt_ref_type_id int4 - - -     Type of name represented by primary sample name, e.g. Lab number, museum number etc.
date_sampled varchar - - -     Date samples were taken.
date_updated timestamp with time zone - - -   now()  
sample_group_id int4 - -   0  
sample_name varchar(50) - -     Reference number or name of sample. Multiple references/names can be added as alternative references.
sample_type_id int4 - -     Physical form of sample, e.g. bulk sample, kubienta subsample, core subsample, dendro core, dendro slice...
Indexes Unique Columns Method Comment
pk_physical_samples_idx_pk physical_sample_id  
Constraints Kind Expression Columns Comment
pk_physical_samples PRIMARY KEY   physical_sample_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_physical_samples" (
	"physical_sample_id" SERIAL NOT NULL,
	"alt_ref_type_id" int4,
	"date_sampled" varchar,
	"date_updated" timestamp with time zone DEFAULT now(),
	"sample_group_id" int4 NOT NULL DEFAULT 0,
	"sample_name" varchar(50) NOT NULL,
	"sample_type_id" int4 NOT NULL,
	CONSTRAINT "pk_physical_samples" PRIMARY KEY("physical_sample_id"),
	CONSTRAINT "fk_physical_samples_sample_name_type_id" FOREIGN KEY ("alt_ref_type_id")
		REFERENCES "tbl_alt_ref_types"("alt_ref_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_physical_samples_sample_type_id" FOREIGN KEY ("sample_type_id")
		REFERENCES "tbl_sample_types"("sample_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_samples_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_physical_samples" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_physical_samples" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_physical_samples" TO "seadworker";
4.59 tbl_projects
Description
Name and description of project context for datasets for site(s). 
Annotation
Originally as highest in hierarchy (projects-sites-sample_groups...), but changed to metadata with restructuring
to greater usage of datasets for project and site metadata. Was essentially metadata for Sites, but now a more 
flexible solution allowing many-many project-site.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
project_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -     Brief description of project and any useful information for finding out more.
project_abbrev_name varchar(25) - - -     Optional. Abbreviation of project name or acronym (e.g. VGV, SWEDAB)
project_name varchar(150) - - -     Name of project (e.g. Phil's PhD thesis, Malmö ringroad Vägverket)
project_stage_id int4 - - -      
project_type_id int4 - - -      
Indexes Unique Columns Method Comment
pk_projects_idx_pk project_id  
Constraints Kind Expression Columns Comment
pk_projects PRIMARY KEY   project_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_projects" (
	"project_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"project_abbrev_name" varchar(25),
	"project_name" varchar(150),
	"project_stage_id" int4,
	"project_type_id" int4,
	CONSTRAINT "pk_projects" PRIMARY KEY("project_id"),
	CONSTRAINT "fk_projects_project_type_id" FOREIGN KEY ("project_type_id")
		REFERENCES "tbl_project_types"("project_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_projects_project_stage_id" FOREIGN KEY ("project_stage_id")
		REFERENCES "tbl_project_stages"("project_stage_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_projects" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_projects" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_projects" TO "seadworker";
4.60 tbl_publication_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
publication_type_id int4      
date_updated timestamp with time zone - - -   now()  
publication_type varchar(30) - - -      
Indexes Unique Columns Method Comment
pk_publication_types_idx_pk publication_type_id  
Constraints Kind Expression Columns Comment
pk_publication_types PRIMARY KEY   publication_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_publication_types" (
	"publication_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"publication_type" varchar(30),
	CONSTRAINT "pk_publication_types" PRIMARY KEY("publication_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_publication_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_publication_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_publication_types" TO "seadworker";
4.61 tbl_publishers
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
publisher_id int4      
date_updated timestamp with time zone - - -   now()  
place_of_publishing_house varchar - - -      
publisher_name varchar(255) - - -      
Indexes Unique Columns Method Comment
pk_publishers_idx_pk publisher_id  
Constraints Kind Expression Columns Comment
pk_publishers PRIMARY KEY   publisher_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_publishers" (
	"publisher_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"place_of_publishing_house" varchar,
	"publisher_name" varchar(255),
	CONSTRAINT "pk_publishers" PRIMARY KEY("publisher_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_publishers" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_publishers" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_publishers" TO "seadworker";
4.62 tbl_radiocarbon_calibration
Description
Approximate calibration curve for quick calculation of age-depth curves and equivalent ages. Note that dates derived using this table
should in no way be considered acurate or statistically viable. Only full probability density function based calibrated ages should be
used in final calcualtions.
Annotation
Data derived from Neotoma htpp://www.neotomadb.org
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
radiocarbon_calibration_id int4      
c14_yr_bp int4 - -     Mid-point of C14 age.
cal_yr_bp int4 - -     Mid-point of calibrated age.
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
Constraints Kind Expression Columns Comment
pk_radiocarbon_calibration PRIMARY KEY   radiocarbon_calibration_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_radiocarbon_calibration" (
	"radiocarbon_calibration_id" SERIAL NOT NULL,
	"c14_yr_bp" int4 NOT NULL,
	"cal_yr_bp" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_radiocarbon_calibration" PRIMARY KEY("radiocarbon_calibration_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_radiocarbon_calibration" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_radiocarbon_calibration" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_radiocarbon_calibration" TO "seadworker";
4.63 tbl_rdb
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
rdb_id int4      
date_updated timestamp with time zone - - -   now()  
location_id int4 - -     Geographical source/relevance of the specific code. E.g. the international IUCN classification of species in the UK.
rdb_code_id int4 - - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_rdb_idx_pk rdb_id  
Constraints Kind Expression Columns Comment
pk_rdb PRIMARY KEY   rdb_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_rdb" (
	"rdb_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location_id" int4 NOT NULL,
	"rdb_code_id" int4,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_rdb" PRIMARY KEY("rdb_id"),
	CONSTRAINT "fk_rdb_rdb_code_id" FOREIGN KEY ("rdb_code_id")
		REFERENCES "tbl_rdb_codes"("rdb_code_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_rdb_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_tbl_rdb_tbl_location_id" FOREIGN KEY ("location_id")
		REFERENCES "tbl_locations"("location_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_rdb" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb" TO "seadworker";
4.64 tbl_rdb_codes
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
rdb_code_id int4      
date_updated timestamp with time zone - - -   now()  
rdb_category varchar(4) - - -   NULL::character varying  
rdb_definition varchar(200) - - -   NULL::character varying  
rdb_system_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_rdb_codes_idx_pk rdb_code_id  
Constraints Kind Expression Columns Comment
pk_rdb_codes PRIMARY KEY   rdb_code_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_rdb_codes" (
	"rdb_code_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"rdb_category" varchar(4) DEFAULT NULL::character varying,
	"rdb_definition" varchar(200) DEFAULT NULL::character varying,
	"rdb_system_id" int4 DEFAULT 0,
	CONSTRAINT "pk_rdb_codes" PRIMARY KEY("rdb_code_id"),
	CONSTRAINT "fk_rdb_codes_rdb_system_id" FOREIGN KEY ("rdb_system_id")
		REFERENCES "tbl_rdb_systems"("rdb_system_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_rdb_codes" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb_codes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb_codes" TO "seadworker";
4.65 tbl_rdb_systems
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
rdb_system_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
location_id int4 - -     geaographical relevance of rdb code system, e.g. UK, International, New Forest
rdb_first_published int2 - - -      
rdb_system varchar(10) - - -   NULL::character varying  
rdb_system_date int4 - - -      
rdb_version varchar(10) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
pk_rdb_systems_idx_pk rdb_system_id  
Constraints Kind Expression Columns Comment
pk_rdb_systems PRIMARY KEY   rdb_system_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_rdb_systems" (
	"rdb_system_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location_id" int4 NOT NULL,
	"rdb_first_published" int2,
	"rdb_system" varchar(10) DEFAULT NULL::character varying,
	"rdb_system_date" int4,
	"rdb_version" varchar(10) DEFAULT NULL::character varying,
	CONSTRAINT "pk_rdb_systems" PRIMARY KEY("rdb_system_id"),
	CONSTRAINT "fk_rdb_systems_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_rdb_systems_location_id" FOREIGN KEY ("location_id")
		REFERENCES "tbl_locations"("location_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_rdb_systems" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb_systems" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_rdb_systems" TO "seadworker";
4.66 tbl_record_types
Description
Used to group biological proxies according to the way they are generally used, and/or their origin. It is used to provide useful subsets of the full taxonomic
index, abundance_elements (i.e. so that "seed" is not an available option for insect data) and methods.
Groups are made at the "Order" level in the taxonomic tree.
E.g. "Insects & similar" groups insects with arthropods and other animals commonly extracted during insect work. "Plants & pollen" are 
grouped as they have similar ecological implications (i.e. presence of plant).
Annotation
Groups may need to be expanded or changed as user base expands. The combining of "Plants & pollen" may prove troublesome as
the two proxies have very different methods and uses.
Comment
May also use this to group methods - e.g. Phosphate analyses (whereas tbl_method_groups would store the larger group "Palaeo chemical/physical" methods)
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
record_type_id int4      
date_updated timestamp with time zone - - -   now()  
record_type_description text - - -     Detailed description of group and explanation for grouping
record_type_name varchar(50) - - -   NULL::character varying Short name of proxy/proxies in group
Indexes Unique Columns Method Comment
pk_record_types_idx_pk record_type_id  
Constraints Kind Expression Columns Comment
pk_record_types PRIMARY KEY   record_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_record_types" (
	"record_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"record_type_description" text,
	"record_type_name" varchar(50) DEFAULT NULL::character varying,
	CONSTRAINT "pk_record_types" PRIMARY KEY("record_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_record_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_record_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_record_types" TO "seadworker";
4.67 tbl_relative_age_refs
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
relative_age_ref_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
relative_age_id int4 - -      
Indexes Unique Columns Method Comment
pk_relative_age_refs_idx_pk relative_age_ref_id BTREE  
Constraints Kind Expression Columns Comment
pk_relative_age_refs PRIMARY KEY   relative_age_ref_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_relative_age_refs" (
	"relative_age_ref_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"relative_age_id" int4 NOT NULL,
	CONSTRAINT "pk_relative_age_refs" PRIMARY KEY("relative_age_ref_id"),
	CONSTRAINT "fk_relative_age_refs_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_relative_age_refs_relative_age_id" FOREIGN KEY ("relative_age_id")
		REFERENCES "tbl_relative_ages"("relative_age_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_relative_age_refs" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_age_refs" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_age_refs" TO "seadworker";
4.68 tbl_relative_ages
Description
Relative ages are both period and calendar (single or year range) chronological definitions. Period definitions are stored only once per 
geographical occurence (e.g. Germany and Sweden can have different Bronze Age definitions), and assigned to samples through the
relative dates table.
Annotation
 
Comment
20120504PIB: removed biblio_id as is replaced by tbl_relative_age_refs
20130722PIB: changed colour in model to AliceBlue to reflect degree of user addition possible (i.e. ages can be added for reference in tbl_relative_dates)
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
relative_age_id int4      
Abbreviation varchar - - -     Standard abbreviated form of name if available
c14_age_older numeric(20,5) - - -     C14 age of younger boundary of period (where relevant).
c14_age_younger numeric(20,5) - - -     C14 age of later boundary of period (where relevant). Leave blank for calendar ages.
cal_age_older numeric(20,5) - - -     (Approximate) age before present (1950) of earliest boundary of period. Or if calendar age then the calendar age converted to BP.
cal_age_younger numeric(20,5) - - -     (Approximate) age before present (1950) of latest boundary of period. Or if calendar age then the calendar age converted to BP.
date_updated timestamp with time zone - - -   now()  
description text - - -     A description of the (usually) period.
location_id int4 - - -      
notes text - - -     Any further notes not included in the description, such as reliability of definition or fuzzyness of boundaries.
relative_age_name varchar(50) - - -     Name of the dating period, e.g. Bronze Age. Calendar ages should be given appropriate names such as AD 1492, 74 BC
relative_age_type_id int4 - - -      
Indexes Unique Columns Method Comment
pk_relative_ages_idx_pk relative_age_id  
Constraints Kind Expression Columns Comment
pk_relative_ages PRIMARY KEY   relative_age_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_relative_ages" (
	"relative_age_id" SERIAL NOT NULL,
	"Abbreviation" varchar,
	"c14_age_older" numeric(20,5),
	"c14_age_younger" numeric(20,5),
	"cal_age_older" numeric(20,5),
	"cal_age_younger" numeric(20,5),
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"location_id" int4,
	"notes" text,
	"relative_age_name" varchar(50),
	"relative_age_type_id" int4,
	CONSTRAINT "pk_relative_ages" PRIMARY KEY("relative_age_id"),
	CONSTRAINT "fk_relative_ages_relative_age_type_id" FOREIGN KEY ("relative_age_type_id")
		REFERENCES "tbl_relative_age_types"("relative_age_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_relative_ages_location_id" FOREIGN KEY ("location_id")
		REFERENCES "tbl_locations"("location_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_relative_ages" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_ages" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_ages" TO "seadworker";
4.69 tbl_relative_dates
Description
Relative dates represent specific instances of relative ages (calendar or period based) assigned to a physical sample.
Also indicates method used to associate sample with date (e.g. strategraphic dating, typological association).
Uncertainty should only include "from", "to", "ca.", "from ca.", "to ca.", "?" to indicate termini or approximation and uses the lookup table tbl_dating_uncertainty.
Annotation
 
Comment
20120504PIB: Added method_id to store dating method used to attribute sample to period or calendar date (e.g. strategraphic dating, typological)
20130722PIB: addded field dating_uncertainty_id to cater for "from", "to" and "ca." etc. especially from import of BugsCEP
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
relative_date_id int4      
date_updated timestamp with time zone - - -   now()  
method_id int4 - - -     Dating method used to attribute sample to period or calendar date.
notes text - - -     Any notes specific to the dating of this sample to this calendar or period based age
physical_sample_id int4 - -      
dating_uncertainty_id int4 - - -      
relative_age_id int4 - - -      
Indexes Unique Columns Method Comment
pk_relative_dates_idx_pk relative_date_id  
Constraints Kind Expression Columns Comment
pk_relative_dates PRIMARY KEY   relative_date_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_relative_dates" (
	"relative_date_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4,
	"notes" text,
	"physical_sample_id" int4 NOT NULL,
	"dating_uncertainty_id" int4,
	"relative_age_id" int4,
	CONSTRAINT "pk_relative_dates" PRIMARY KEY("relative_date_id"),
	CONSTRAINT "fk_relative_dates_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_relative_dates_relative_age_id" FOREIGN KEY ("relative_age_id")
		REFERENCES "tbl_relative_ages"("relative_age_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_relative_dates_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_relative_dates_dating_uncertainty_id" FOREIGN KEY ("dating_uncertainty_id")
		REFERENCES "tbl_dating_uncertainty"("dating_uncertainty_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_relative_dates" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_dates" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_relative_dates" TO "seadworker";
4.70 tbl_sample_alt_refs
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_alt_ref_id int4      
alt_ref varchar(40) - -      
alt_ref_type_id int4 - -      
date_updated timestamp with time zone - - -   now()  
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_alt_refs_idx_pk sample_alt_ref_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_alt_refs PRIMARY KEY   sample_alt_ref_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_alt_refs" (
	"sample_alt_ref_id" SERIAL NOT NULL,
	"alt_ref" varchar(40) NOT NULL,
	"alt_ref_type_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_alt_refs" PRIMARY KEY("sample_alt_ref_id"),
	CONSTRAINT "fk_sample_alt_refs_alt_ref_type_id" FOREIGN KEY ("alt_ref_type_id")
		REFERENCES "tbl_alt_ref_types"("alt_ref_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_alt_refs_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_alt_refs" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_alt_refs" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_alt_refs" TO "seadworker";
4.71 tbl_sample_colours
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_colour_id int4      
colour_id int4 - -      
date_updated timestamp with time zone - - -   now()  
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_colours_idx_pk sample_colour_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_colours PRIMARY KEY   sample_colour_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_colours" (
	"sample_colour_id" SERIAL NOT NULL,
	"colour_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_colours" PRIMARY KEY("sample_colour_id"),
	CONSTRAINT "fk_sample_colours_colour_id" FOREIGN KEY ("colour_id")
		REFERENCES "tbl_colours"("colour_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_colours_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_colours" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_colours" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_colours" TO "seadworker";
4.72 tbl_sample_dimensions
Description
Measurable dimension type data for samples, excluding coordinates, but including depth or sample in stratigraphy or core.
E.g. size of sample on arrival (volume, weight etc), depth of sample top, depth of sample bottom. Note that the name and 
description of the dimension are stored in tbl_dimension.
Annotation
A methods should exist for each dimension so as to ensure consistency of use.
Comment
20120506PIB: depth measurements for samples moved here from tbl_physical_samples
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_dimension_id int4      
date_updated timestamp with time zone - - -   now()  
dimension_id int4 - -     Details of the dimension measured
dimension_value numeric(20,10) - -     Numerical value of dimension, in the units indicated in the documentation and interface.
method_id int4 - -     Method describing dimension measurement, with link to units used
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_dimensions_idx_pk sample_dimension_id  
Constraints Kind Expression Columns Comment
pk_sample_dimensions PRIMARY KEY   sample_dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_dimensions" (
	"sample_dimension_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_id" int4 NOT NULL,
	"dimension_value" numeric(20,10) NOT NULL,
	"method_id" int4 NOT NULL,
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_dimensions" PRIMARY KEY("sample_dimension_id"),
	CONSTRAINT "fk_sample_dimensions_dimension_id" FOREIGN KEY ("dimension_id")
		REFERENCES "tbl_dimensions"("dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_dimensions_measurement_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_dimensions_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_dimensions" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_dimensions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_dimensions" TO "seadworker";
4.73 tbl_sample_group_dimensions
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_dimension_id int4      
date_updated timestamp with time zone - - -   now()  
dimension_id int4 - -      
dimension_value numeric(20,5) - -      
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_group_dimensions_idx_pk sample_group_dimension_id  
Constraints Kind Expression Columns Comment
pk_sample_group_dimensions PRIMARY KEY   sample_group_dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_dimensions" (
	"sample_group_dimension_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_id" int4 NOT NULL,
	"dimension_value" numeric(20,5) NOT NULL,
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_group_dimensions" PRIMARY KEY("sample_group_dimension_id"),
	CONSTRAINT "fk_sample_group_dimensions_dimension_id" FOREIGN KEY ("dimension_id")
		REFERENCES "tbl_dimensions"("dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_group_dimensions_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_group_dimensions" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_dimensions" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_dimensions" TO "seadworker";
4.74 tbl_sample_group_images
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_image_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
image_location text - -      
image_name varchar(80) - - -      
image_type_id int4 - -      
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_group_images_idx_pk sample_group_image_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_group_images PRIMARY KEY   sample_group_image_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_images" (
	"sample_group_image_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"image_location" text NOT NULL,
	"image_name" varchar(80),
	"image_type_id" int4 NOT NULL,
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_group_images" PRIMARY KEY("sample_group_image_id"),
	CONSTRAINT "fk_sample_group_images_image_type_id" FOREIGN KEY ("image_type_id")
		REFERENCES "tbl_image_types"("image_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_group_images_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_group_images" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_images" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_images" TO "seadworker";
4.75 tbl_sample_group_references
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_reference_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
sample_group_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_sample_group_references_idx_pk sample_group_reference_id BTREE  
idx_biblio_id - biblio_id BTREE  
idx_sample_group_id - sample_group_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_group_references PRIMARY KEY   sample_group_reference_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_references" (
	"sample_group_reference_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"sample_group_id" int4 DEFAULT 0,
	CONSTRAINT "pk_sample_group_references" PRIMARY KEY("sample_group_reference_id"),
	CONSTRAINT "fk_sample_group_references_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_group_references_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "idx_biblio_id" ON "tbl_sample_group_references" USING BTREE (
	"biblio_id"
);


CREATE INDEX "idx_sample_group_id" ON "tbl_sample_group_references" USING BTREE (
	"sample_group_id"
);


ALTER TABLE "tbl_sample_group_references" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_references" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_references" TO "seadworker";
4.76 tbl_sample_group_sampling_contexts
Description
Type of situation under which the sample group was collected, investigation purpose, or other initial collection purpose. 
E.g. stratigraphic sequence, invertebrate survey, archaeological excavation, Quantitative vegetation survey.
It provides a collection context for the sample group and provides for extraction/analysis on only samples collected in a specific 
type of project. For example, climate reconstruction using only strategraphic sequences, habitat analysis using only modern
survey data.
Annotation
For dendro include records "Dendro archaeological investigation", "Dendro building survey", Dendro shipwreck" etc.
For ceramics the initial definition may have to be expanded...
Comment
Type=lookup
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sampling_context_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -     Full explanation of the grouping term
sampling_context varchar(40) - -     Short but meaningful name defining sample group context, e.g. Stratigraphic sequence, Archaeological excavation
sort_order int2 - -   0 Allows lists to group similar or associated group context close to each other, e.g. modern investigations together, palaeo investigations together
Indexes Unique Columns Method Comment
pk_sample_group_sampling_contexts_idx_pk sampling_context_id  
Constraints Kind Expression Columns Comment
pk_sample_group_sampling_contexts PRIMARY KEY   sampling_context_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_sampling_contexts" (
	"sampling_context_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"sampling_context" varchar(40) NOT NULL,
	"sort_order" int2 NOT NULL DEFAULT 0,
	CONSTRAINT "pk_sample_group_sampling_contexts" PRIMARY KEY("sampling_context_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_group_sampling_contexts" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_sampling_contexts" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_group_sampling_contexts" TO "seadworker";
4.77 tbl_sample_groups
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_id int4      
date_updated timestamp with time zone - - -   now()  
method_id int4 - -     Sampling method, e.g. Russian auger core, Pitfall traps. Note different from context in that it is specific to method of sample retrieval and not type of investigation.
sample_group_description text - - -      
sample_group_name varchar(100) - - -   NULL::character varying Name which identifies the collection of samples. For ceramics, use vessel number.
sampling_context_id int4 - - -      
site_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_sample_groups_idx_pk sample_group_id  
Constraints Kind Expression Columns Comment
pk_sample_groups PRIMARY KEY   sample_group_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_groups" (
	"sample_group_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4 NOT NULL,
	"sample_group_description" text,
	"sample_group_name" varchar(100) DEFAULT NULL::character varying,
	"sampling_context_id" int4,
	"site_id" int4 DEFAULT 0,
	CONSTRAINT "pk_sample_groups" PRIMARY KEY("sample_group_id"),
	CONSTRAINT "fk_sample_group_sampling_context_id" FOREIGN KEY ("sampling_context_id")
		REFERENCES "tbl_sample_group_sampling_contexts"("sampling_context_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_groups_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_groups_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_groups" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_groups" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_groups" TO "seadworker";
4.78 tbl_sample_horizons
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_horizon_id int4      
date_updated timestamp with time zone - - -   now()  
horizon_id int4 - -      
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_horizons_idx_pk sample_horizon_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_horizons PRIMARY KEY   sample_horizon_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_horizons" (
	"sample_horizon_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"horizon_id" int4 NOT NULL,
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_horizons" PRIMARY KEY("sample_horizon_id"),
	CONSTRAINT "fk_sample_horizons_horizon_id" FOREIGN KEY ("horizon_id")
		REFERENCES "tbl_horizons"("horizon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_horizons_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_horizons" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_horizons" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_horizons" TO "seadworker";
4.79 tbl_sample_images
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_image_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
image_location text - -      
image_name varchar(80) - - -      
image_type_id int4 - -      
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_images_idx_pk sample_image_id BTREE  
Constraints Kind Expression Columns Comment
pk_sample_images PRIMARY KEY   sample_image_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_images" (
	"sample_image_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"image_location" text NOT NULL,
	"image_name" varchar(80),
	"image_type_id" int4 NOT NULL,
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_images" PRIMARY KEY("sample_image_id"),
	CONSTRAINT "fk_sample_images_image_type_id" FOREIGN KEY ("image_type_id")
		REFERENCES "tbl_image_types"("image_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_images_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_images" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_images" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_images" TO "seadworker";
4.80 tbl_sample_notes
Description
Any notes associated with the sample, including an indication of what type of notes they are. One record per note.
E.g. note_type="Field note" note="Possible contamination"
Annotation
Could probably benefit from a look-up table for note_type, but may be best to fill with data first in order to assess usage and scope.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_note_id int4      
date_updated timestamp with time zone - - -   now()  
note text - -     Note contents
note_type varchar - - -     Origin of the note, e.g. field note, lab note
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_notes_idx_pk sample_note_id  
Constraints Kind Expression Columns Comment
pk_sample_notes PRIMARY KEY   sample_note_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_notes" (
	"sample_note_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"note" text NOT NULL,
	"note_type" varchar,
	"physical_sample_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_notes" PRIMARY KEY("sample_note_id"),
	CONSTRAINT "fk_sample_notes_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_notes" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_notes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_notes" TO "seadworker";
4.81 tbl_sample_types
Description
Physical form of the sample. Normally these will either be subsamples relating the sample group sampling method, or bulk samples.
e.g. Core subsample, grab sample, bulk (bag) sample
Annotation
May have to think about setting up filters/relationships to ensure can't have illogical combinations of sample_group sampling method
and sample_type, e.g. bulk sample from core.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
type_name varchar(40) - -      
Indexes Unique Columns Method Comment
pk_sample_types_idx_pk sample_type_id  
Constraints Kind Expression Columns Comment
pk_sample_types PRIMARY KEY   sample_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_types" (
	"sample_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"type_name" varchar(40) NOT NULL,
	CONSTRAINT "pk_sample_types" PRIMARY KEY("sample_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sample_types" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sample_types" TO "seadworker";
4.82 tbl_season_types
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
season_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
season_type varchar(30) - - -      
Indexes Unique Columns Method Comment
pk_season_types_idx_pk season_type_id  
Constraints Kind Expression Columns Comment
pk_season_types PRIMARY KEY   season_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_season_types" (
	"season_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"season_type" varchar(30),
	CONSTRAINT "pk_season_types" PRIMARY KEY("season_type_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_season_types" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_season_types" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_season_types" TO "seadworker";
4.83 tbl_seasons
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
season_id int4      
date_updated timestamp with time zone - - -   now()  
season_name varchar(20) - - -   NULL::character varying  
season_type varchar(30) - - -   NULL::character varying  
season_type_id int4 - - -      
sort_order int2 - - -   0  
Indexes Unique Columns Method Comment
pk_seasons_idx_pk season_id  
Constraints Kind Expression Columns Comment
pk_seasons PRIMARY KEY   season_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_seasons" (
	"season_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"season_name" varchar(20) DEFAULT NULL::character varying,
	"season_type" varchar(30) DEFAULT NULL::character varying,
	"season_type_id" int4,
	"sort_order" int2 DEFAULT 0,
	CONSTRAINT "pk_seasons" PRIMARY KEY("season_id"),
	CONSTRAINT "fk_seasons_season_type_id" FOREIGN KEY ("season_type_id")
		REFERENCES "tbl_season_types"("season_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_seasons" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_seasons" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_seasons" TO "seadworker";
4.84 tbl_site_images
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_image_id int4      
contact_id int4 - - -      
credit varchar(100) - - -      
date_taken date - - -      
date_updated timestamp with time zone - - -   now()  
description text - - -      
image_location text - -      
image_name varchar(80) - - -      
image_type_id int4 - -      
site_id int4 - -      
Indexes Unique Columns Method Comment
pk_site_images_idx_pk site_image_id BTREE  
Constraints Kind Expression Columns Comment
pk_site_images PRIMARY KEY   site_image_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_images" (
	"site_image_id" SERIAL NOT NULL,
	"contact_id" int4,
	"credit" varchar(100),
	"date_taken" date,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"image_location" text NOT NULL,
	"image_name" varchar(80),
	"image_type_id" int4 NOT NULL,
	"site_id" int4 NOT NULL,
	CONSTRAINT "pk_site_images" PRIMARY KEY("site_image_id"),
	CONSTRAINT "fk_site_images_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_images_image_type_id" FOREIGN KEY ("image_type_id")
		REFERENCES "tbl_image_types"("image_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_images_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_site_images" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_images" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_images" TO "seadworker";
4.85 tbl_site_locations
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_location_id int4      
date_updated timestamp with time zone - - -   now()  
location_id int4 - -      
site_id int4 - -      
Indexes Unique Columns Method Comment
pk_site_location_idx_pk site_location_id BTREE  
Constraints Kind Expression Columns Comment
pk_site_location PRIMARY KEY   site_location_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_locations" (
	"site_location_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location_id" int4 NOT NULL,
	"site_id" int4 NOT NULL,
	CONSTRAINT "pk_site_location" PRIMARY KEY("site_location_id"),
	CONSTRAINT "fk_locations_location_id" FOREIGN KEY ("location_id")
		REFERENCES "tbl_locations"("location_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_locations_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_site_locations" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_locations" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_locations" TO "seadworker";
4.86 tbl_site_other_records
Description
Information on the availability of data not currently stored in SEAD.
One record should be used for each proxy type even if they refer to the same publication.
Note that some of these data may be entered into SEAD at a later date.
Submission requests may be made to the SEAD project group.
Annotation
Partly a legacy data table from BugsCEP, and so relates to all data not stored in Bugs. Table therefore needs assessment to make sure that it does not
refer to data that have already been entered into SEAD.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_other_records_id int4      
biblio_id int4 - - -     Reference to publication containing data
date_updated timestamp with time zone - - -   now()  
description text - - -      
record_type_id int4 - - -     Reference to type of data (proxy)
site_id int4 - - -      
Indexes Unique Columns Method Comment
pk_site_other_records_idx_pk site_other_records_id  
Constraints Kind Expression Columns Comment
pk_site_other_records PRIMARY KEY   site_other_records_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_other_records" (
	"site_other_records_id" SERIAL NOT NULL,
	"biblio_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"record_type_id" int4,
	"site_id" int4,
	CONSTRAINT "pk_site_other_records" PRIMARY KEY("site_other_records_id"),
	CONSTRAINT "fk_site_other_records_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_other_records_record_type_id" FOREIGN KEY ("record_type_id")
		REFERENCES "tbl_record_types"("record_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_other_records_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_site_other_records" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_other_records" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_other_records" TO "seadworker";
4.87 tbl_site_references
Description
Allows listing of publications at site level.
Note that publications refering to only a set of sample_groups or physical_samples or specific dataset etc are stored at the appropriate lower level in the hierarchy
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_reference_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
site_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_site_references_idx_pk site_reference_id  
Constraints Kind Expression Columns Comment
pk_site_references PRIMARY KEY   site_reference_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_references" (
	"site_reference_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"site_id" int4 DEFAULT 0,
	CONSTRAINT "pk_site_references" PRIMARY KEY("site_reference_id"),
	CONSTRAINT "fk_site_references_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_references_site_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_site_references" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_references" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_site_references" TO "seadworker";
4.88 tbl_sites
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_id int4      
altitude numeric(18,10) - - -      
date_updated timestamp with time zone - - -   now()  
latitude_dd numeric(18,10) - - -      
longitude_dd numeric(18,10) - - -      
national_site_identifier varchar(255) - - -      
site_description text - - -   NULL::character varying  
site_name varchar(50) - - -   NULL::character varying  
site_preservation_status_id int4 - - -      
Indexes Unique Columns Method Comment
pk_sites_idx_pk site_id  
Constraints Kind Expression Columns Comment
pk_sites PRIMARY KEY   site_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sites" (
	"site_id" SERIAL NOT NULL,
	"altitude" numeric(18,10),
	"date_updated" timestamp with time zone DEFAULT now(),
	"latitude_dd" numeric(18,10),
	"longitude_dd" numeric(18,10),
	"national_site_identifier" varchar(255),
	"site_description" text DEFAULT NULL::character varying,
	"site_name" varchar(50) DEFAULT NULL::character varying,
	"site_preservation_status_id" int4,
	CONSTRAINT "pk_sites" PRIMARY KEY("site_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_sites" OWNER TO "seadworker";

GRANT ALL PRIVILEGES ON TABLE "tbl_sites" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_sites" TO "seadworker";
4.89 tbl_species_associations
Description
Describes the relationship of one taxon to others, such as predation, paracitism, common habitat etc. Also includes synonym links. Note that the direction of the association is important in most cases (e.g. x predates on y)
Annotation
 
Comment
20131001PIB: removed not null constraint from biblio_id to allow associations without reference
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
species_association_id int4      
associated_taxon_id int4 - -     Taxon with which the primary taxon (taxon_id) is associated.
association_type_id int4 - - -     Type of association between primary taxon (taxon_id) and associated taxon. Note that the direction of the association is important in most cases (e.g. x predates on y)
biblio_id int4 - - -     Reference where relationship between taxa is described or mentioned
date_updated timestamp with time zone - - -   now()  
referencing_type text - - -      
taxon_id int4 - -     Primary taxon in relationship, i.e. this taxon has x relationship with the associated taxon
Indexes Unique Columns Method Comment
pk_species_associations_idx_pk species_association_id  
Constraints Kind Expression Columns Comment
pk_species_associations PRIMARY KEY   species_association_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_species_associations" (
	"species_association_id" SERIAL NOT NULL,
	"associated_taxon_id" int4 NOT NULL,
	"association_type_id" int4,
	"biblio_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"referencing_type" text,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_species_associations" PRIMARY KEY("species_association_id"),
	CONSTRAINT "fk_species_associations_associated_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_species_associations_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_species_associations_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_species_associations_association_type_id" FOREIGN KEY ("association_type_id")
		REFERENCES "tbl_species_association_types"("association_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_species_associations" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_species_associations" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_species_associations" TO "seadworker";
4.90 tbl_taxa_common_names
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxon_common_name_id int4      
common_name varchar(255) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
language_id int4 - - -   0  
taxon_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_taxa_common_names_idx_pk taxon_common_name_id  
Constraints Kind Expression Columns Comment
pk_taxa_common_names PRIMARY KEY   taxon_common_name_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_common_names" (
	"taxon_common_name_id" SERIAL NOT NULL,
	"common_name" varchar(255) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	"language_id" int4 DEFAULT 0,
	"taxon_id" int4 DEFAULT 0,
	CONSTRAINT "pk_taxa_common_names" PRIMARY KEY("taxon_common_name_id"),
	CONSTRAINT "fk_taxa_common_names_language_id" FOREIGN KEY ("language_id")
		REFERENCES "tbl_languages"("language_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_common_names_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxa_common_names" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_common_names" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_common_names" TO "seadworker";
4.91 tbl_taxa_measured_attributes
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
measured_attribute_id int4      
attribute_measure varchar(20) - - -   NULL::character varying  
attribute_type varchar(25) - - -   NULL::character varying  
attribute_units varchar(10) - - -   NULL::character varying  
data numeric(18,10) - - -   0  
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_taxa_measured_attributes_idx_pk measured_attribute_id BTREE  
Constraints Kind Expression Columns Comment
pk_taxa_measured_attributes PRIMARY KEY   measured_attribute_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_measured_attributes" (
	"measured_attribute_id" SERIAL NOT NULL,
	"attribute_measure" varchar(20) DEFAULT NULL::character varying,
	"attribute_type" varchar(25) DEFAULT NULL::character varying,
	"attribute_units" varchar(10) DEFAULT NULL::character varying,
	"data" numeric(18,10) DEFAULT 0,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_taxa_measured_attributes" PRIMARY KEY("measured_attribute_id"),
	CONSTRAINT "fk_taxa_measured_attributes_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxa_measured_attributes" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_measured_attributes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_measured_attributes" TO "seadworker";
4.92 tbl_taxa_seasonality
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
seasonality_id int4      
activity_type_id int4 - -      
date_updated timestamp with time zone - - -   now()  
location_id int4 - -     Geographical relevance of seasonality data
season_id int4 - - -   0  
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_taxa_seasonality_idx_pk seasonality_id  
Constraints Kind Expression Columns Comment
pk_taxa_seasonality PRIMARY KEY   seasonality_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_seasonality" (
	"seasonality_id" SERIAL NOT NULL,
	"activity_type_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location_id" int4 NOT NULL,
	"season_id" int4 DEFAULT 0,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_taxa_seasonality" PRIMARY KEY("seasonality_id"),
	CONSTRAINT "fk_taxa_seasonality_activity_type_id" FOREIGN KEY ("activity_type_id")
		REFERENCES "tbl_activity_types"("activity_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_seasonality_season_id" FOREIGN KEY ("season_id")
		REFERENCES "tbl_seasons"("season_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_seasonality_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_seasonality_location_id" FOREIGN KEY ("location_id")
		REFERENCES "tbl_locations"("location_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxa_seasonality" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_seasonality" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_seasonality" TO "seadworker";
4.93 tbl_taxa_synonyms
Description
Alternative scientific names of taxa, including primary reference for definition or use.
Annotation
Synonyms are mapped to their appropriate table level via an id (taxon_id, genus_id etc), only one id is used per record (i.e. taxon, family, genus are mutually exclusive).
Author synonyms are, however, included in the master authors list (as there shouldn't be too many), and always linked by fk.
Comment
20131001PIB: This table will be made obsolete through the use of tbl_species_associations for recording synonyms. This transition will require modification of Toby's Bugs to SEAD scripts, which will be taken care of later
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
synonym_id int4      
author_id int4 - - -      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
family_id int4 - - -      
genus_id int4 - - -      
notes text - - -   NULL::character varying Any information useful to the history or usage of the synonym.
reference_type varchar - - -     Form of information relating to the synonym in the given bibliographic link, e.g. by use, definition, incorrect usage.
synonym varchar(255) - - -     Synonym at level defined by id level. I.e. if synonym is at genus level, then only the genus synonym is included here. Another synonym record is used for the species level synonym for the same taxon only if the name is different to that used in the master list.
taxon_id int4 - - -      
Indexes Unique Columns Method Comment
pk_taxa_synonyms_idx_pk synonym_id  
Constraints Kind Expression Columns Comment
pk_taxa_synonyms PRIMARY KEY   synonym_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_synonyms" (
	"synonym_id" SERIAL NOT NULL,
	"author_id" int4,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"family_id" int4,
	"genus_id" int4,
	"notes" text DEFAULT NULL::character varying,
	"reference_type" varchar,
	"synonym" varchar(255),
	"taxon_id" int4,
	CONSTRAINT "pk_taxa_synonyms" PRIMARY KEY("synonym_id"),
	CONSTRAINT "fk_taxa_synonyms_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_synonyms_family_id" FOREIGN KEY ("family_id")
		REFERENCES "tbl_taxa_tree_families"("family_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_synonyms_genus_id" FOREIGN KEY ("genus_id")
		REFERENCES "tbl_taxa_tree_genera"("genus_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_synonyms_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_synonyms_taxa_tree_author_id" FOREIGN KEY ("author_id")
		REFERENCES "tbl_taxa_tree_authors"("author_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxa_synonyms" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_synonyms" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_synonyms" TO "seadworker";
4.94 tbl_taxa_tree_authors
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
author_id int4      
author_name varchar(100) - - -   NULL::character varying  
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
tbl_taxa_tree_authors_name - author_name BTREE  
pk_taxa_tree_authors_idx_pk author_id  
Constraints Kind Expression Columns Comment
pk_taxa_tree_authors PRIMARY KEY   author_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_tree_authors" (
	"author_id" SERIAL NOT NULL,
	"author_name" varchar(100) DEFAULT NULL::character varying,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_taxa_tree_authors" PRIMARY KEY("author_id")
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxa_tree_authors_name" ON "tbl_taxa_tree_authors" USING BTREE (
	"author_name"
);


ALTER TABLE "tbl_taxa_tree_authors" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_authors" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_authors" TO "seadworker";
4.95 tbl_taxa_tree_families
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
family_id int4      
date_updated timestamp with time zone - - -   now()  
family_name varchar(100) - - -   NULL::character varying  
order_id int4 - -      
Indexes Unique Columns Method Comment
tbl_taxa_tree_families_name - family_name BTREE  
tbl_taxa_tree_families_order_id - order_id BTREE  
pk_taxa_tree_families_idx_pk family_id  
Constraints Kind Expression Columns Comment
pk_taxa_tree_families PRIMARY KEY   family_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_tree_families" (
	"family_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"family_name" varchar(100) DEFAULT NULL::character varying,
	"order_id" int4 NOT NULL,
	CONSTRAINT "pk_taxa_tree_families" PRIMARY KEY("family_id"),
	CONSTRAINT "fk_taxa_tree_families_order_id" FOREIGN KEY ("order_id")
		REFERENCES "tbl_taxa_tree_orders"("order_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxa_tree_families_name" ON "tbl_taxa_tree_families" USING BTREE (
	"family_name"
);


CREATE INDEX "tbl_taxa_tree_families_order_id" ON "tbl_taxa_tree_families" USING BTREE (
	"order_id"
);


ALTER TABLE "tbl_taxa_tree_families" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_families" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_families" TO "seadworker";
4.96 tbl_taxa_tree_genera
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
genus_id int4      
date_updated timestamp with time zone - - -   now()  
family_id int4 - - -      
genus_name varchar(100) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
tbl_taxa_tree_genera_family_id - family_id BTREE  
tbl_taxa_tree_genera_name - genus_name BTREE  
pk_taxa_tree_genera_idx_pk genus_id  
Constraints Kind Expression Columns Comment
pk_taxa_tree_genera PRIMARY KEY   genus_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_tree_genera" (
	"genus_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"family_id" int4,
	"genus_name" varchar(100) DEFAULT NULL::character varying,
	CONSTRAINT "pk_taxa_tree_genera" PRIMARY KEY("genus_id"),
	CONSTRAINT "fk_taxa_tree_genera_family_id" FOREIGN KEY ("family_id")
		REFERENCES "tbl_taxa_tree_families"("family_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxa_tree_genera_family_id" ON "tbl_taxa_tree_genera" USING BTREE (
	"family_id"
);


CREATE INDEX "tbl_taxa_tree_genera_name" ON "tbl_taxa_tree_genera" USING BTREE (
	"genus_name"
);


ALTER TABLE "tbl_taxa_tree_genera" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_genera" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_genera" TO "seadworker";
4.97 tbl_taxa_tree_master
Description
Need to add pseudotaxa for 
No identifiable XXX material (where XXX=each proxy)
Material has potential
Annotation
 
Comment
20131001PIB: Scope of table expanded to include synonyms as records in this table only. Synonym references are now made using tbl_species_associations to link two taxa - one as master, the other as synonym.
20130416PIB: removed default=0 for author_id and genus_id as was incorrect
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxon_id int4      
author_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
genus_id int4 - - -      
species varchar(255) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
pk_taxa_tree_master_idx_pk taxon_id  
Constraints Kind Expression Columns Comment
pk_taxa_tree_master PRIMARY KEY   taxon_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_tree_master" (
	"taxon_id" SERIAL NOT NULL,
	"author_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"genus_id" int4,
	"species" varchar(255) DEFAULT NULL::character varying,
	CONSTRAINT "pk_taxa_tree_master" PRIMARY KEY("taxon_id"),
	CONSTRAINT "fk_taxa_tree_master_author_id" FOREIGN KEY ("author_id")
		REFERENCES "tbl_taxa_tree_authors"("author_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_tree_master_genus_id" FOREIGN KEY ("genus_id")
		REFERENCES "tbl_taxa_tree_genera"("genus_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxa_tree_master" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_master" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_master" TO "seadworker";
4.98 tbl_taxa_tree_orders
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
order_id int4      
date_updated timestamp with time zone - - -   now()  
order_name varchar(50) - - -   NULL::character varying  
record_type_id int4 - - -      
sort_order int4 - - -      
Indexes Unique Columns Method Comment
tbl_taxa_tree_orders_order_id - order_id BTREE  
pk_taxa_tree_orders_idx_pk order_id  
Constraints Kind Expression Columns Comment
pk_taxa_tree_orders PRIMARY KEY   order_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_tree_orders" (
	"order_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"order_name" varchar(50) DEFAULT NULL::character varying,
	"record_type_id" int4,
	"sort_order" int4,
	CONSTRAINT "pk_taxa_tree_orders" PRIMARY KEY("order_id"),
	CONSTRAINT "fk_taxa_tree_orders_record_type_id" FOREIGN KEY ("record_type_id")
		REFERENCES "tbl_record_types"("record_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxa_tree_orders_order_id" ON "tbl_taxa_tree_orders" USING BTREE (
	"order_id"
);


ALTER TABLE "tbl_taxa_tree_orders" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_orders" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxa_tree_orders" TO "seadworker";
4.99 tbl_taxonomic_order
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxonomic_order_id int4      
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - - -   0  
taxonomic_code numeric(18,10) - - -   0  
taxonomic_order_system_id int4 - - -   0  
Indexes Unique Columns Method Comment
tbl_taxonomic_order_taxon_id - taxon_id BTREE  
tbl_taxonomic_order_taxonomic_code - taxonomic_code BTREE  
tbl_taxonomic_order_taxonomic_order_id - taxonomic_order_id BTREE  
tbl_taxonomic_order_taxonomic_system_id - taxonomic_order_system_id BTREE  
pk_taxonomic_order_idx_pk taxonomic_order_id  
Constraints Kind Expression Columns Comment
pk_taxonomic_order PRIMARY KEY   taxonomic_order_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxonomic_order" (
	"taxonomic_order_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 DEFAULT 0,
	"taxonomic_code" numeric(18,10) DEFAULT 0,
	"taxonomic_order_system_id" int4 DEFAULT 0,
	CONSTRAINT "pk_taxonomic_order" PRIMARY KEY("taxonomic_order_id"),
	CONSTRAINT "fk_taxonomic_order_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxonomic_order_taxonomic_order_system_id" FOREIGN KEY ("taxonomic_order_system_id")
		REFERENCES "tbl_taxonomic_order_systems"("taxonomic_order_system_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxonomic_order_taxon_id" ON "tbl_taxonomic_order" USING BTREE (
	"taxon_id"
);


CREATE INDEX "tbl_taxonomic_order_taxonomic_code" ON "tbl_taxonomic_order" USING BTREE (
	"taxonomic_code"
);


CREATE INDEX "tbl_taxonomic_order_taxonomic_order_id" ON "tbl_taxonomic_order" USING BTREE (
	"taxonomic_order_id"
);


CREATE INDEX "tbl_taxonomic_order_taxonomic_system_id" ON "tbl_taxonomic_order" USING BTREE (
	"taxonomic_order_system_id"
);


ALTER TABLE "tbl_taxonomic_order" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order" TO "seadworker";
4.100 tbl_taxonomic_order_biblio
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxonomic_order_biblio_id int4      
biblio_id int4 - - -   0  
date_updated timestamp with time zone - - -   now()  
taxonomic_order_system_id int4 - - -   0  
Indexes Unique Columns Method Comment
pk_taxonomic_order_biblio_idx_pk taxonomic_order_biblio_id BTREE  
tbl_taxonomic_order_biblio_biblio_id - biblio_id BTREE  
tbl_taxonomic_order_biblio_taxonomic_order_biblio_id - taxonomic_order_biblio_id BTREE  
tbl_taxonomic_order_biblio_taxonomic_order_system_id - taxonomic_order_system_id BTREE  
Constraints Kind Expression Columns Comment
pk_taxonomic_order_biblio PRIMARY KEY   taxonomic_order_biblio_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxonomic_order_biblio" (
	"taxonomic_order_biblio_id" SERIAL NOT NULL,
	"biblio_id" int4 DEFAULT 0,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxonomic_order_system_id" int4 DEFAULT 0,
	CONSTRAINT "pk_taxonomic_order_biblio" PRIMARY KEY("taxonomic_order_biblio_id"),
	CONSTRAINT "fk_taxonomic_order_biblio_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxonomic_order_biblio_taxonomic_order_system_id" FOREIGN KEY ("taxonomic_order_system_id")
		REFERENCES "tbl_taxonomic_order_systems"("taxonomic_order_system_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxonomic_order_biblio_biblio_id" ON "tbl_taxonomic_order_biblio" USING BTREE (
	"biblio_id"
);


CREATE INDEX "tbl_taxonomic_order_biblio_taxonomic_order_biblio_id" ON "tbl_taxonomic_order_biblio" USING BTREE (
	"taxonomic_order_biblio_id"
);


CREATE INDEX "tbl_taxonomic_order_biblio_taxonomic_order_system_id" ON "tbl_taxonomic_order_biblio" USING BTREE (
	"taxonomic_order_system_id"
);


ALTER TABLE "tbl_taxonomic_order_biblio" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order_biblio" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order_biblio" TO "seadworker";
4.101 tbl_taxonomic_order_systems
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxonomic_order_system_id int4      
date_updated timestamp with time zone - - -   now()  
system_description text - - -      
system_name varchar(50) - - -   NULL::character varying  
Indexes Unique Columns Method Comment
tbl_taxonomic_order_systems_taxonomic_system_id - taxonomic_order_system_id BTREE  
pk_taxonomic_order_systems_idx_pk taxonomic_order_system_id  
Constraints Kind Expression Columns Comment
pk_taxonomic_order_systems PRIMARY KEY   taxonomic_order_system_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxonomic_order_systems" (
	"taxonomic_order_system_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"system_description" text,
	"system_name" varchar(50) DEFAULT NULL::character varying,
	CONSTRAINT "pk_taxonomic_order_systems" PRIMARY KEY("taxonomic_order_system_id")
)
WITH (
	OIDS = False
);

CREATE INDEX "tbl_taxonomic_order_systems_taxonomic_system_id" ON "tbl_taxonomic_order_systems" USING BTREE (
	"taxonomic_order_system_id"
);


ALTER TABLE "tbl_taxonomic_order_systems" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order_systems" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomic_order_systems" TO "seadworker";
4.102 tbl_taxonomy_notes
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxonomy_notes_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - -      
taxonomy_notes text - - -      
Indexes Unique Columns Method Comment
pk_taxonomy_notes_idx_pk taxonomy_notes_id BTREE  
Constraints Kind Expression Columns Comment
pk_taxonomy_notes PRIMARY KEY   taxonomy_notes_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxonomy_notes" (
	"taxonomy_notes_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 NOT NULL,
	"taxonomy_notes" text,
	CONSTRAINT "pk_taxonomy_notes" PRIMARY KEY("taxonomy_notes_id"),
	CONSTRAINT "fk_taxonomy_notes_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxonomy_notes_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_taxonomy_notes" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomy_notes" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_taxonomy_notes" TO "seadworker";
4.103 tbl_tephra_dates
Description
 
Annotation
 
Comment
20130722PIB: Added field dating_uncertainty_id to cater for >< etc.
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
tephra_date_id int4      
analysis_entity_id int4 - -      
dating_uncertainty_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
notes text - - -      
tephra_id int4 - -      
Indexes Unique Columns Method Comment
pk_tephra_dates_idx_pk tephra_date_id  
Constraints Kind Expression Columns Comment
pk_tephra_dates PRIMARY KEY   tephra_date_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_tephra_dates" (
	"tephra_date_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"dating_uncertainty_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"notes" text,
	"tephra_id" int4 NOT NULL,
	CONSTRAINT "pk_tephra_dates" PRIMARY KEY("tephra_date_id"),
	CONSTRAINT "fk_tephra_dates_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_tephra_dates_tephra_id" FOREIGN KEY ("tephra_id")
		REFERENCES "tbl_tephras"("tephra_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_tephra_dates_dating_uncertainty_id" FOREIGN KEY ("dating_uncertainty_id")
		REFERENCES "tbl_dating_uncertainty"("dating_uncertainty_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_tephra_dates" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephra_dates" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephra_dates" TO "seadworker";
4.104 tbl_tephra_refs
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
tephra_ref_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
tephra_id int4 - -      
Indexes Unique Columns Method Comment
pk_tephra_refs_idx_pk tephra_ref_id BTREE  
Constraints Kind Expression Columns Comment
pk_tephra_refs PRIMARY KEY   tephra_ref_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_tephra_refs" (
	"tephra_ref_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"tephra_id" int4 NOT NULL,
	CONSTRAINT "pk_tephra_refs" PRIMARY KEY("tephra_ref_id"),
	CONSTRAINT "fk_tephra_refs_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_tephra_refs_tephra_id" FOREIGN KEY ("tephra_id")
		REFERENCES "tbl_tephras"("tephra_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_tephra_refs" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephra_refs" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephra_refs" TO "seadworker";
4.105 tbl_tephras
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
tephra_id int4      
c14_age numeric(20,5) - - -      
c14_age_older numeric(20,5) - - -      
c14_age_younger numeric(20,5) - - -      
cal_age numeric(20,5) - - -      
cal_age_older numeric(20,5) - - -      
cal_age_younger numeric(20,5) - - -      
date_updated timestamp with time zone - - -   now()  
notes text - - -      
tephra_name varchar(80) - - -      
Indexes Unique Columns Method Comment
pk_tephras_idx_pk tephra_id  
Constraints Kind Expression Columns Comment
pk_tephras PRIMARY KEY   tephra_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_tephras" (
	"tephra_id" SERIAL NOT NULL,
	"c14_age" numeric(20,5),
	"c14_age_older" numeric(20,5),
	"c14_age_younger" numeric(20,5),
	"cal_age" numeric(20,5),
	"cal_age_older" numeric(20,5),
	"cal_age_younger" numeric(20,5),
	"date_updated" timestamp with time zone DEFAULT now(),
	"notes" text,
	"tephra_name" varchar(80),
	CONSTRAINT "pk_tephras" PRIMARY KEY("tephra_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_tephras" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephras" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_tephras" TO "seadworker";
4.106 tbl_text_biology
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
biology_id int4      
biblio_id int4 - -      
biology_text text - - -      
date_updated timestamp with time zone - - -   now()  
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_text_biology_idx_pk biology_id BTREE  
Constraints Kind Expression Columns Comment
pk_text_biology PRIMARY KEY   biology_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_text_biology" (
	"biology_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"biology_text" text,
	"date_updated" timestamp with time zone DEFAULT now(),
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_text_biology" PRIMARY KEY("biology_id"),
	CONSTRAINT "fk_text_biology_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_text_biology_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_text_biology" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_biology" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_biology" TO "seadworker";
4.107 tbl_text_distribution
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
distribution_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
distribution_text text - - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_text_distribution_idx_pk distribution_id BTREE  
Constraints Kind Expression Columns Comment
pk_text_distribution PRIMARY KEY   distribution_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_text_distribution" (
	"distribution_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"distribution_text" text,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_text_distribution" PRIMARY KEY("distribution_id"),
	CONSTRAINT "fk_text_distribution_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_text_distribution_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_text_distribution" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_distribution" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_distribution" TO "seadworker";
4.108 tbl_text_identification_keys
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
key_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
key_text text - - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
pk_text_identification_keys_idx_pk key_id BTREE  
Constraints Kind Expression Columns Comment
pk_text_identification_keys PRIMARY KEY   key_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_text_identification_keys" (
	"key_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"key_text" text,
	"taxon_id" int4 NOT NULL,
	CONSTRAINT "pk_text_identification_keys" PRIMARY KEY("key_id"),
	CONSTRAINT "fk_text_identification_keys_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_text_identification_keys_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_text_identification_keys" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_identification_keys" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_text_identification_keys" TO "seadworker";
4.109 tbl_units
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
unit_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
unit_abbrev varchar(15) - - -      
unit_name varchar(50) - -      
Indexes Unique Columns Method Comment
pk_units_idx_pk unit_id  
Constraints Kind Expression Columns Comment
pk_units PRIMARY KEY   unit_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_units" (
	"unit_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"unit_abbrev" varchar(15),
	"unit_name" varchar(50) NOT NULL,
	CONSTRAINT "pk_units" PRIMARY KEY("unit_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_units" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_units" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_units" TO "seadworker";
4.110 tbl_updates_log
Description
 
Annotation
 
Comment
 
Owner
postgres
TemporaryWith OIDsFill Factor
-False
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
updates_log_id int4 -      
table_name varchar(150) - -      
last_updated date - -      
Indexes Unique Columns Method Comment
pk_updates_log updates_log_id BTREE  
Constraints Kind Expression Columns Comment
pk_updates_log PRIMARY KEY   updates_log_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_updates_log" (
	"updates_log_id" int4 NOT NULL,
	"table_name" varchar(150) NOT NULL,
	"last_updated" date NOT NULL,
	CONSTRAINT "pk_updates_log" PRIMARY KEY("updates_log_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "tbl_updates_log" OWNER TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_updates_log" TO "postgres";

GRANT ALL PRIVILEGES ON TABLE "tbl_updates_log" TO "seadworker";
4.111 tbl_site_preservation_status
Description
Information on preservation level of sites and risks to cultural heritage. One record per preservation status/threat
Table added at request of PAN (Polar Archaeology Network) members and is aimed at providing support for evaluation of threats to  (Arctic) cultural heritage.
Annotation
Note that publications relating to this table should be included in the site_references
(at error) This table needs work and discussion with the PAN network (Hans Peter Blankholm) in order to define necessary fields and content.
Need to cater for both preservation status (e.g. lost, damaged, at risk) and risk type (e.g. hydroelectric, oil exploitation, mining, forestry) and probably more.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_preservation_status_id int4      
Evaluation_date date - - -     Date of assessment, either formal or informal
assessment_author_contact_id int4 - - -     Person or authority in tbl_contacts responsible for the assessment of preservation status and threat
assessment_type varchar - - -     Type of assessment giving information on preservation status and threat, e.g. UNESCO report, archaeological survey
date_updated timestamp with time zone - - -   now()  
description text - - -     Brief description of site preservation status or threat to site preservation. Include data here that does not fit in the other fields (for now - we may expand these features later if demand exists)
preservation_status_or_threat varchar - - -     Descriptive name for: Preservation status, e.g. (e.g. lost, damaged, threatened) OR Main reason for potential or real risk to site (e.g. hydroelectric, oil exploitation, mining, forestry, climate change, erosion)
site_id int4 - - -     Allows multiple preservation/threat records per site
Indexes Unique Columns Method Comment
pk_site_preservation_status_idx_pk site_preservation_status_id  
Constraints Kind Expression Columns Comment
pk_site_preservation_status PRIMARY KEY   site_preservation_status_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_preservation_status" (
	"site_preservation_status_id" SERIAL NOT NULL,
	"Evaluation_date" date,
	"assessment_author_contact_id" int4,
	"assessment_type" varchar,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"preservation_status_or_threat" varchar,
	"site_id" int4,
	CONSTRAINT "pk_site_preservation_status" PRIMARY KEY("site_preservation_status_id"),
	CONSTRAINT "fk_site_preservation_status_site_id " FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

ALTER TABLE "tbl_site_preservation_status" OWNER TO "seadworker";
4.112 tbl_bugs_biblio
Description
Legacy date for synchronisation between BugsCEP and SEAD. May be removed when BugsCEP features are duplicated by SEAD.
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_biblio_id int4      
biblio_id int4 - -      
bugs_reference varchar - -     From bugsdata.mdb: TBiblio.REFERENCE
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
pk_bugs_biblio_idx_pk bugs_biblio_id  
Constraints Kind Expression Columns Comment
pk_bugs_biblio PRIMARY KEY   bugs_biblio_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_biblio" (
	"bugs_biblio_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"bugs_reference" varchar NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	CONSTRAINT "pk_bugs_biblio" PRIMARY KEY("bugs_biblio_id"),
	CONSTRAINT "fk_bugs_biblio_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);
4.113 tbl_physical_sample_features
Description
 
Annotation
Allows many to many features to samples so that a feature can include several samples, and a sample can come from a nested set of features.
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
physical_sample_feature_id int4      
date_updated timestamp with time zone - - -   now()  
feature_id int4 - -      
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk physical_sample_feature_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   physical_sample_feature_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_physical_sample_features" (
	"physical_sample_feature_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"feature_id" int4 NOT NULL,
	"physical_sample_id" int4 NOT NULL,
	PRIMARY KEY("physical_sample_feature_id"),
	CONSTRAINT "fk_physical_sample_features_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_physical_sample_features_feature_id" FOREIGN KEY ("feature_id")
		REFERENCES "tbl_features"("feature_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE
);
4.114 tbl_keywords
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
keyword_id int4      
date_updated timestamp with time zone - - -   now()  
definition text - - -      
keyword varchar - - -      
Indexes Unique Columns Method Comment
_idx_pk keyword_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   keyword_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_keywords" (
	"keyword_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"definition" text,
	"keyword" varchar,
	PRIMARY KEY("keyword_id")
);
4.115 tbl_biblio_keywords
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
biblio_keyword_id int4      
biblio_id int4 - -      
date_updated timestamp with time zone - - -   now()  
keyword_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk biblio_keyword_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   biblio_keyword_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_biblio_keywords" (
	"biblio_keyword_id" SERIAL NOT NULL,
	"biblio_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"keyword_id" int4 NOT NULL,
	PRIMARY KEY("biblio_keyword_id"),
	CONSTRAINT "fk_biblio_keywords_keyword_id" FOREIGN KEY ("keyword_id")
		REFERENCES "tbl_keywords"("keyword_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_biblio_keywords_biblio_id" FOREIGN KEY ("biblio_id")
		REFERENCES "tbl_biblio"("biblio_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);
4.116 tbl_analysis_entity_dimensions
Description
Physical sample size used for analysis (often a subsample of physical sample)
analysed sample
non-analysed residue
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
analysis_entity_dimension_id int4      
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
dimension_id int4 - -      
dimension_value numeric - -      
Indexes Unique Columns Method Comment
_idx_pk analysis_entity_dimension_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   analysis_entity_dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_analysis_entity_dimensions" (
	"analysis_entity_dimension_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_id" int4 NOT NULL,
	"dimension_value" numeric NOT NULL,
	PRIMARY KEY("analysis_entity_dimension_id"),
	CONSTRAINT "fk_analysis_entity_dimensions_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE CASCADE
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_analysis_entity_dimensions_dimension_id" FOREIGN KEY ("dimension_id")
		REFERENCES "tbl_dimensions"("dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

ALTER TABLE "tbl_analysis_entity_dimensions" OWNER TO "seadworker";
4.117 tbl_site_natgridrefs
Description
General site coordinates using national grid system, e.g. UK Ordinanace Survey National Grid, Swedish SWEREF99.
A site can be defined on multiple grids (e.g. Swedish RT90 and SWEREF99TM)
Annotation
Details of national grid systems stored in tbl_methods. Country storage in tbl_sites, also possible in tbl_site_locations, so 
not necessary to store country along with national grid system (even though they are intimately related).
Comment
20120507PIB: removed tbl_national_grids and trasfered storage of coordinate systems to tbl_methods
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
site_natgridref_id int4      
date_updated timestamp with time zone - - -   now()  
method_id int4 - -     Points to coordinate system.
natgridref varchar - -      
site_id int4 - -      
Indexes Unique Columns Method Comment
pk_sitenatgridrefs_idx_pk site_natgridref_id  
Constraints Kind Expression Columns Comment
pk_sitenatgridrefs PRIMARY KEY   site_natgridref_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_site_natgridrefs" (
	"site_natgridref_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4 NOT NULL,
	"natgridref" varchar NOT NULL,
	"site_id" int4 NOT NULL,
	CONSTRAINT "pk_sitenatgridrefs" PRIMARY KEY("site_natgridref_id"),
	CONSTRAINT "fk_site_natgridrefs_sites_id" FOREIGN KEY ("site_id")
		REFERENCES "tbl_sites"("site_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_site_natgridrefs_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_site_natgridrefs" OWNER TO "seadworker";
4.118 tbl_project_stages
Description
Stages of investigation, e.g. desktop study, prospection, full excavation, reanalysis
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
project_stage_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -     Explanation of stage name term, including details of purpose and general contents
stage_name varchar - - -     Stage of project in investigative cycle, e.g. desktop study, prospection, final excavation
Indexes Unique Columns Method Comment
dataset_stage_id_idx_pk project_stage_id  
Constraints Kind Expression Columns Comment
dataset_stage_id PRIMARY KEY   project_stage_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_project_stages" (
	"project_stage_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"stage_name" varchar,
	CONSTRAINT "dataset_stage_id" PRIMARY KEY("project_stage_id")
);

ALTER TABLE "tbl_project_stages" OWNER TO "seadworker";
4.119 tbl_project_types
Description
Describes type of investigation in terms of main purpose/aim, e.g. teaching, consultanct, research, or combinations of these
Annotation
Moved from site level (and before that own project level), so that can allow site datasets to be mapped to different instances 
of same project (e.g. teaching/prospection, consultancy/full excavation)
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
project_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -     Project type combinations can be used where appropriate, e.g. Teaching/research
project_type_name varchar - - -     Descriptive name for project type, e.g. Consultancy, Research, Teaching; also combinations Consultancy/teaching
Indexes Unique Columns Method Comment
pk_project_type_id_idx_pk project_type_id  
Constraints Kind Expression Columns Comment
pk_project_type_id PRIMARY KEY   project_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_project_types" (
	"project_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"project_type_name" varchar,
	CONSTRAINT "pk_project_type_id" PRIMARY KEY("project_type_id")
);

ALTER TABLE "tbl_project_types" OWNER TO "seadworker";
4.120 tbl_bugs_dates_radio
Description
Legacy date for synchronisation between BugsCEP and SEAD. May be removed when BugsCEP features are duplicated by SEAD.
Annotation
 
Comment
20130722PIB: Added fields bugs_samplecode and bugs_materialtype to help verify sync with BugsCEP
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_dates_radio_id int4      
bugs_datecode varchar - -     From Bugsdata.mdb: TDatesRadio.DateCODE
date_updated timestamp with time zone - - -   now()  
geochron_id int4 - -      
bugs_samplecode varchar - - -      
bugs_materialtype text - - -      
Indexes Unique Columns Method Comment
_idx_pk bugs_dates_radio_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   bugs_dates_radio_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_dates_radio" (
	"bugs_dates_radio_id" SERIAL NOT NULL,
	"bugs_datecode" varchar NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"geochron_id" int4 NOT NULL,
	"bugs_samplecode" varchar,
	"bugs_materialtype" text,
	PRIMARY KEY("bugs_dates_radio_id"),
	CONSTRAINT "fk_bugs_dates_radio_geochron_id" FOREIGN KEY ("geochron_id")
		REFERENCES "tbl_geochronology"("geochron_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_bugs_dates_radio" OWNER TO "seadworker";
4.121 tbl_bugs_dates_calendar
Description
Legacy date for synchronisation between BugsCEP and SEAD. May be removed when BugsCEP features are duplicated by SEAD.
Stores references to Bugs calendar dates and period definitions (but not the periods dates themselves, which are stored in 
tbl_bugs_dates_period)
Annotation
 
Comment
20130722PIB: Added field bugs_samplecode to help verify sync with BugsCEP
20130722PIB: Realigned reference to tbl_relative_dates from tbl_relative_ages to correctly mirror bugsdata
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_dates_calendar_id int4      
bugs_calendarcode varchar - -     From Bugsdata.mdb: TDatesCalendar.CalendarCODE or TPeriods.PeriodCODE
date_updated timestamp with time zone - - -   now()  
relative_date_id int4 - -      
bugs_samplecode varchar - - -      
Indexes Unique Columns Method Comment
_idx_pk bugs_dates_calendar_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   bugs_dates_calendar_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_dates_calendar" (
	"bugs_dates_calendar_id" SERIAL NOT NULL,
	"bugs_calendarcode" varchar NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"relative_date_id" int4 NOT NULL,
	"bugs_samplecode" varchar,
	PRIMARY KEY("bugs_dates_calendar_id"),
	CONSTRAINT "fk_bugs_dates_calendar_relative_dates_id" FOREIGN KEY ("relative_date_id")
		REFERENCES "tbl_relative_dates"("relative_date_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_bugs_dates_calendar" OWNER TO "seadworker";
4.122 tbl_sample_coordinates
Description
Physical location of sample, where available, as recorded on local grid or converted to other standard. Uses methods table to look up
type of grid, which enables all samples recorded with Lat/Long to be easily plotted on large scale maps, but local grids to be plotted on 
a site/project basis.
Annotation
At moment allow only one coordinate set for a sample in interface, even though structure allows for multiple?
May need to reassess this later if need arrises for custom grids which can be converted to national/international coordinate
 systems on the fly (as in original SEAD design).
Grid systems like UK National Grid may require on the fly conversion on data entry do convert single value entry to lat/long.
Idea: Convert all national grid based entries to lat/long and store in parallel - would allow for smoother rendering of sample 
points on large scale maps.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_coordinate_id int4      
accuracy numeric(20,10) - - -     GPS type accuracy, e.g. 5m 10m 0.01m
coordinate_method_dimension_id int4 - -      
date_updated timestamp with time zone - - -   now()  
measurement numeric(20,10) - -      
physical_sample_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk sample_coordinate_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_coordinate_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_coordinates" (
	"sample_coordinate_id" SERIAL NOT NULL,
	"accuracy" numeric(20,10),
	"coordinate_method_dimension_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"measurement" numeric(20,10) NOT NULL,
	"physical_sample_id" int4 NOT NULL,
	PRIMARY KEY("sample_coordinate_id"),
	CONSTRAINT "fk_sample_coordinates_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_coordinates_coordinate_method_dimension_id" FOREIGN KEY ("coordinate_method_dimension_id")
		REFERENCES "tbl_coordinate_method_dimensions"("coordinate_method_dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_coordinates" OWNER TO "seadworker";
4.123 tbl_relative_age_types
Description
Relative age types, e.g. archaeological, Blytt-Sernander, Geological. Can be either quite precise in their definition 
(e.g. Blytt-Sernander) or generalising (e.g. Geological) as required.
Annotation
 
Comment
20130723PIB: replaced date_updated column with new one with same name but correct data type
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
relative_age_type_id int4      
age_type varchar - - -     Name of chronological age type, e.g. Archaeological period, Single calendar date, Calendar age range, Blytt-Sernander
date_updated timestamp with time zone - - -   now()  
description text - - -     Description of chronological age type, e.g. Period defined by archaeological and or geological dates representing cultural activity period, Climate period defined by palaeo-vegetation records
Indexes Unique Columns Method Comment
_idx_pk relative_age_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   relative_age_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_relative_age_types" (
	"relative_age_type_id" SERIAL NOT NULL,
	"age_type" varchar,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	PRIMARY KEY("relative_age_type_id")
);

ALTER TABLE "tbl_relative_age_types" OWNER TO "seadworker";
4.124 tbl_bugs_dates_period
Description
Legacy data for synchronisation between SEAD and BugsCEP. May be deleted whne all features of BugsCEP are in SEAD.
Stores period dates assigned to samples in BugsCEP
Annotation
 
Comment
20130722PIB: Added field bugs_samplecode to help verify sync with BugsCEP
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_dates_period_id int4      
bugs_perioddatecode varchar - -     From Bugsdata.mdb: TDatesPeriod.PeriodDateCODE
date_updated timestamp with time zone - - -   now()  
relative_date_id int4 - -      
bugs_samplecode varchar - - -      
Indexes Unique Columns Method Comment
_idx_pk bugs_dates_period_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   bugs_dates_period_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_dates_period" (
	"bugs_dates_period_id" SERIAL NOT NULL,
	"bugs_perioddatecode" varchar NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"relative_date_id" int4 NOT NULL,
	"bugs_samplecode" varchar,
	PRIMARY KEY("bugs_dates_period_id"),
	CONSTRAINT "fk_bugs_dates_period_relative_date_id" FOREIGN KEY ("relative_date_id")
		REFERENCES "tbl_relative_dates"("relative_date_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_bugs_dates_period" OWNER TO "seadworker";
4.125 tbl_analysis_entity_prep_methods
Description
Allows multiple preparation methods for a specific proxy analysis on physical sample. E.g. Seive with 250um; paraffin
floatation; sort under binocular microscope.
Annotation
 
Comment
20120506PIB: created to cater for multiple preparation methods for analysis but maintaining simple dataset concept.
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
analysis_entity_prep_method_id int4      
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
method_id int4 - -     Preparation methods only
Indexes Unique Columns Method Comment
_idx_pk analysis_entity_prep_method_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   analysis_entity_prep_method_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_analysis_entity_prep_methods" (
	"analysis_entity_prep_method_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4 NOT NULL,
	PRIMARY KEY("analysis_entity_prep_method_id"),
	CONSTRAINT "fk_analysis_entity_prep_methods_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_analysis_entity_prep_methods_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_analysis_entity_prep_methods" OWNER TO "seadworker";
4.126 tbl_sample_group_descriptions
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_description_id int4      
date_updated timestamp with time zone - - -   now()  
group_description varchar - - -      
sample_group_description_type_id int4 - -      
sample_group_id int4 - - -      
Indexes Unique Columns Method Comment
pk_sample_group_description_id_idx_pk sample_group_description_id  
Constraints Kind Expression Columns Comment
pk_sample_group_description_id PRIMARY KEY   sample_group_description_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_descriptions" (
	"sample_group_description_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"group_description" varchar,
	"sample_group_description_type_id" int4 NOT NULL,
	"sample_group_id" int4,
	CONSTRAINT "pk_sample_group_description_id" PRIMARY KEY("sample_group_description_id"),
	CONSTRAINT "fk_sample_group_descriptions_sample_group_description_type_id" FOREIGN KEY ("sample_group_description_type_id")
		REFERENCES "tbl_sample_group_description_types"("sample_group_description_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_groups_sample_group_descriptions_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_group_descriptions" OWNER TO "seadworker";
4.127 tbl_sample_group_notes
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_note_id int4      
date_updated timestamp with time zone - - -   now()  
note varchar - - -      
sample_group_id int4 - -      
Indexes Unique Columns Method Comment
pk_sample_group_note_id_idx_pk sample_group_note_id  
Constraints Kind Expression Columns Comment
pk_sample_group_note_id PRIMARY KEY   sample_group_note_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_notes" (
	"sample_group_note_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"note" varchar,
	"sample_group_id" int4 NOT NULL,
	CONSTRAINT "pk_sample_group_note_id" PRIMARY KEY("sample_group_note_id"),
	CONSTRAINT "fk_tbl_sample_group_notes_sample_groups" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_group_notes" OWNER TO "seadworker";
4.128 tbl_sample_group_description_type_sampling_contexts
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_description_type_sampling_context_id int4      
date_updated timestamp with time zone - - -   now()  
sample_group_description_type_id int4 - -      
sampling_context_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk sample_group_description_type_sampling_context_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_group_description_type_sampling_context_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_description_type_sampling_contexts" (
	"sample_group_description_type_sampling_context_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"sample_group_description_type_id" int4 NOT NULL,
	"sampling_context_id" int4 NOT NULL,
	PRIMARY KEY("sample_group_description_type_sampling_context_id"),
	CONSTRAINT "fk_sample_group_description_type_sampling_context_id" FOREIGN KEY ("sample_group_description_type_id")
		REFERENCES "tbl_sample_group_description_types"("sample_group_description_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_group_sampling_context_id0" FOREIGN KEY ("sampling_context_id")
		REFERENCES "tbl_sample_group_sampling_contexts"("sampling_context_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_group_description_type_sampling_contexts" OWNER TO "seadworker";
4.129 tbl_sample_group_description_types
Description
Defines the type of a description for a sample group. Definition types include building structure data information (roof, structure type, etc) or dendro annotation data surrounding wrecks. 
These descriptions should not be used to determine sampled features. They are defined for samples.
User added lookup data.
Annotation
Building structure type: Wall type, roof type
Wreckage information: ship type, repository water type (salt water, bracke water, lagoon, etc).
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_description_type_id int4      
date_updated timestamp with time zone - - -   now()  
type_description varchar - - -      
type_name varchar(255) - - -      
Indexes Unique Columns Method Comment
_idx_pk sample_group_description_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_group_description_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_description_types" (
	"sample_group_description_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"type_description" varchar,
	"type_name" varchar(255),
	PRIMARY KEY("sample_group_description_type_id")
);
4.130 tbl_sample_group_coordinates
Description
Coordinates for sample groups. E.g. top of core, top of profile dendrochronological object, ceramic object.  
Annotation
Only structural difference between this table and sample coordinates is that here accuracy can be text.
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_group_position_id int4      
coordinate_method_dimension_id int4 - -      
date_updated timestamp with time zone - - -   now()  
position_accuracy varchar(128) - - -      
sample_group_id int4 - -      
sample_group_position numeric(20,10) - - -      
Indexes Unique Columns Method Comment
_idx_pk sample_group_position_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_group_position_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_group_coordinates" (
	"sample_group_position_id" SERIAL NOT NULL,
	"coordinate_method_dimension_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"position_accuracy" varchar(128),
	"sample_group_id" int4 NOT NULL,
	"sample_group_position" numeric(20,10),
	PRIMARY KEY("sample_group_position_id"),
	CONSTRAINT "fk_sample_group_positions_sample_group_id" FOREIGN KEY ("sample_group_id")
		REFERENCES "tbl_sample_groups"("sample_group_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_group_positions_coordinate_method_dimension_id" FOREIGN KEY ("coordinate_method_dimension_id")
		REFERENCES "tbl_coordinate_method_dimensions"("coordinate_method_dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);
4.131 tbl_sample_descriptions
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_description_id int4      
date_updated timestamp with time zone - - -   now()  
description varchar - - -      
physical_sample_id int4 - -      
sample_description_type_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk sample_description_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_description_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_descriptions" (
	"sample_description_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" varchar,
	"physical_sample_id" int4 NOT NULL,
	"sample_description_type_id" int4 NOT NULL,
	PRIMARY KEY("sample_description_id"),
	CONSTRAINT "fk_sample_descriptions_sample_description_type_id" FOREIGN KEY ("sample_description_type_id")
		REFERENCES "tbl_sample_description_types"("sample_description_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_descriptions_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);
4.132 tbl_sample_locations
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_location_id int4      
date_updated timestamp with time zone - - -   now()  
location varchar(255) - - -      
physical_sample_id int4 - -      
sample_location_type_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk sample_location_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_location_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_locations" (
	"sample_location_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location" varchar(255),
	"physical_sample_id" int4 NOT NULL,
	"sample_location_type_id" int4 NOT NULL,
	PRIMARY KEY("sample_location_id"),
	CONSTRAINT "fk_sample_locations_physical_sample_id" FOREIGN KEY ("physical_sample_id")
		REFERENCES "tbl_physical_samples"("physical_sample_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_locations_sample_location_type_id" FOREIGN KEY ("sample_location_type_id")
		REFERENCES "tbl_sample_location_types"("sample_location_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);
4.133 tbl_sample_description_sample_group_contexts
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_description_sample_group_context_id int4      
date_updated timestamp with time zone - - -   now()  
sample_description_type_id int4 - - -      
sampling_context_id int4 - - -      
Indexes Unique Columns Method Comment
_idx_pk sample_description_sample_group_context_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_description_sample_group_context_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_description_sample_group_contexts" (
	"sample_description_sample_group_context_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"sample_description_type_id" int4,
	"sampling_context_id" int4,
	PRIMARY KEY("sample_description_sample_group_context_id"),
	CONSTRAINT "fk_sample_description_types_sample_group_context_id" FOREIGN KEY ("sample_description_type_id")
		REFERENCES "tbl_sample_description_types"("sample_description_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_description_sample_group_contexts_sampling_context_id" FOREIGN KEY ("sampling_context_id")
		REFERENCES "tbl_sample_group_sampling_contexts"("sampling_context_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_description_sample_group_contexts" OWNER TO "seadworker";
4.134 tbl_sample_description_types
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_description_type_id int4      
date_updated timestamp with time zone - - -   now()  
type_description text - - -      
type_name varchar(255) - - -      
Indexes Unique Columns Method Comment
_idx_pk sample_description_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_description_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_description_types" (
	"sample_description_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"type_description" text,
	"type_name" varchar(255),
	PRIMARY KEY("sample_description_type_id")
);
4.135 tbl_sample_location_types
Description
 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_location_type_id int4      
date_updated timestamp with time zone - - -   now()  
location_type varchar(255) - - -      
location_type_description text - - -      
Indexes Unique Columns Method Comment
_idx_pk sample_location_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_location_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_location_types" (
	"sample_location_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"location_type" varchar(255),
	"location_type_description" text,
	PRIMARY KEY("sample_location_type_id")
);
4.136 tbl_sample_location_type_sampling_contexts
Description
 
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
sample_location_type_sampling_context_id int4      
sampling_context_id int4 - -      
sample_location_type_id int4 -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
_idx_pk sample_location_type_sampling_context_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   sample_location_type_sampling_context_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_sample_location_type_sampling_contexts" (
	"sample_location_type_sampling_context_id" SERIAL NOT NULL,
	"sampling_context_id" int4 NOT NULL,
	"sample_location_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	PRIMARY KEY("sample_location_type_sampling_context_id"),
	CONSTRAINT "fk_sample_location_sampling_contexts_sampling_context_id" FOREIGN KEY ("sample_location_type_id")
		REFERENCES "tbl_sample_location_types"("sample_location_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_sample_location_type_sampling_context_id" FOREIGN KEY ("sampling_context_id")
		REFERENCES "tbl_sample_group_sampling_contexts"("sampling_context_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_sample_location_type_sampling_contexts" OWNER TO "seadworker";
4.137 tbl_coordinate_method_dimensions
Description
Support for which dimensions and their legal values for coordinate system methods.
A list per coordinate system method with dimensions associated with that method. 
Additionally, the legal values (if any) of the given dimension can be specified. 
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
coordinate_method_dimension_id int4      
date_updated timestamp with time zone - - -   now()  
dimension_id int4 - -      
limit_lower numeric(18,10) - - -      
limit_upper numeric(18,10) - - -      
method_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk coordinate_method_dimension_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   coordinate_method_dimension_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_coordinate_method_dimensions" (
	"coordinate_method_dimension_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dimension_id" int4 NOT NULL,
	"limit_lower" numeric(18,10),
	"limit_upper" numeric(18,10),
	"method_id" int4 NOT NULL,
	PRIMARY KEY("coordinate_method_dimension_id"),
	CONSTRAINT "fk_coordinate_method_dimensions_dimensions_id" FOREIGN KEY ("dimension_id")
		REFERENCES "tbl_dimensions"("dimension_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE,
	CONSTRAINT "fk_coordinate_method_dimensions_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE CASCADE
	NOT DEFERRABLE
);
4.138 tbl_dendro
Description
Support/ supplemental data from dendro analyses.
Annotation
Almost everything that isn't the final felling date.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dendro_id int4      
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
dendro_measurement_id int4 - -      
measurement_value varchar - -      
Indexes Unique Columns Method Comment
_idx_pk dendro_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dendro_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dendro" (
	"dendro_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dendro_measurement_id" int4 NOT NULL,
	"measurement_value" varchar NOT NULL,
	PRIMARY KEY("dendro_id"),
	CONSTRAINT "fk_dendro_dendro_measurement_id" FOREIGN KEY ("dendro_measurement_id")
		REFERENCES "tbl_dendro_measurements"("dendro_measurement_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_dendro_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dendro" OWNER TO "seadworker";
4.139 tbl_dendro_measurements
Description
Defines measurements stored in tbl_dendro and allows values selected from list in tbl_dendro_measurement_lookup.
Annotation
May move this all over to methods if more appropriate/useful. There may be aspects of dendro which do not work with either 
option and may be moved up to sample level. Preveniens might be awkward.
Comment
Type=lookup
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dendro_measurement_id int4      
date_updated timestamp with time zone - - -   now()  
method_id int4 - - -      
Indexes Unique Columns Method Comment
_idx_pk dendro_measurement_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dendro_measurement_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dendro_measurements" (
	"dendro_measurement_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4,
	PRIMARY KEY("dendro_measurement_id"),
	CONSTRAINT "fk_dendro_measurements_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dendro_measurements" OWNER TO "seadworker";
4.140 tbl_ceramics
Description
Results data for ceramics analyses. Methods selected from predefined list with limited value lists as appropriate.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ceramics_id int4      
analysis_entity_id int4 - -      
ceramics_measurement_id int4 - -      
date_updated timestamp with time zone - - -   now()  
measurement_value varchar - -      
Indexes Unique Columns Method Comment
_idx_pk ceramics_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   ceramics_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ceramics" (
	"ceramics_id" SERIAL NOT NULL,
	"analysis_entity_id" int4 NOT NULL,
	"ceramics_measurement_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"measurement_value" varchar NOT NULL,
	PRIMARY KEY("ceramics_id"),
	CONSTRAINT "fk_ceramics_ceramics_measurement_id" FOREIGN KEY ("ceramics_measurement_id")
		REFERENCES "tbl_ceramics_measurements"("ceramics_measurement_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_ceramics_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_ceramics" OWNER TO "seadworker";
4.141 tbl_ceramics_measurements
Description
Measurent/description categories for describing ceramics. List of acceptable values is provided where appropriate.
Annotation
 
Comment
Type=lookup
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ceramics_measurement_id int4      
date_updated timestamp with time zone - - -   now()  
method_id int4 - - -      
Indexes Unique Columns Method Comment
_idx_pk ceramics_measurement_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   ceramics_measurement_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ceramics_measurements" (
	"ceramics_measurement_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"method_id" int4,
	PRIMARY KEY("ceramics_measurement_id"),
	CONSTRAINT "fk_ceramics_measurements_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_ceramics_measurements" OWNER TO "seadworker";
4.142 tbl_ceramics_measurement_lookup
Description
Lookup data for permissable values for ceramics measurements.
I.e. constrins values for tbl_ceramics_measurements
Annotation
Think about how to best handle measurements without predefined list - probably no values in this table.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
ceramics_measurement_lookup_id int4      
ceramics_measurement_id int4 - -      
date_updated timestamp with time zone - - -   now()  
value varchar - -      
Indexes Unique Columns Method Comment
_idx_pk ceramics_measurement_lookup_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   ceramics_measurement_lookup_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_ceramics_measurement_lookup" (
	"ceramics_measurement_lookup_id" SERIAL NOT NULL,
	"ceramics_measurement_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"value" varchar NOT NULL,
	PRIMARY KEY("ceramics_measurement_lookup_id"),
	CONSTRAINT "fk_ceramics_measurement_lookup_ceramics_measurements_id" FOREIGN KEY ("ceramics_measurement_id")
		REFERENCES "tbl_ceramics_measurements"("ceramics_measurement_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_ceramics_measurement_lookup" OWNER TO "seadworker";
4.143 tbl_dendro_dates
Description
Dendrochronological dates (calculated felling year).
Annotation
May be able to rationalise this with tbl_relative_ages, although might be simpler to keep them separate. Another 
alternative is a calendar dates table that includes dendro data.
Comment
20130722PIB: Added field dating_uncertainty_id to cater for >< etc.
20130722PIB: prefixed fieldnames age_younger and age_older with "cal_" to conform with equivalent names in other tables
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dendro_date_id int4      
cal_age_older int4 - - -      
cal_age_younger int4 - - -      
analysis_entity_id int4 - -      
date_updated timestamp with time zone - - -   now()  
error int4 - - -      
season_or_qualifier_id int4 - - -      
years_type_id int4 - - -      
dating_uncertainty_id int4 - - -      
Indexes Unique Columns Method Comment
_idx_pk dendro_date_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dendro_date_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dendro_dates" (
	"dendro_date_id" SERIAL NOT NULL,
	"cal_age_older" int4,
	"cal_age_younger" int4,
	"analysis_entity_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"error" int4,
	"season_or_qualifier_id" int4,
	"years_type_id" int4,
	"dating_uncertainty_id" int4,
	PRIMARY KEY("dendro_date_id"),
	CONSTRAINT "fk_dendro_dates_years_type_id" FOREIGN KEY ("years_type_id")
		REFERENCES "tbl_years_types"("years_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_dendro_dates_analysis_entity_id" FOREIGN KEY ("analysis_entity_id")
		REFERENCES "tbl_analysis_entities"("analysis_entity_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_dendro_dates_dating_uncertainty_id" FOREIGN KEY ("dating_uncertainty_id")
		REFERENCES "tbl_dating_uncertainty"("dating_uncertainty_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dendro_dates" OWNER TO "seadworker";
4.144 tbl_years_types
Description
Age defininition in terms off AD, BC or BP.
Annotation
May be useful elsewhere... might even be catered for through methods.
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
years_type_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
name varchar - -      
Indexes Unique Columns Method Comment
_idx_pk years_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   years_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_years_types" (
	"years_type_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"name" varchar NOT NULL,
	PRIMARY KEY("years_type_id")
);

ALTER TABLE "tbl_years_types" OWNER TO "seadworker";
4.145 tbl_dendro_date_notes
Description
Notes specific to date, such as accuracy of date, information on uncertainty that does not fit into the usual fields.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dendro_date_note_id int4      
date_updated timestamp with time zone - - -   now()  
dendro_date_id int4 - -      
note text - - -      
Indexes Unique Columns Method Comment
_idx_pk dendro_date_note_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dendro_date_note_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dendro_date_notes" (
	"dendro_date_note_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dendro_date_id" int4 NOT NULL,
	"note" text,
	PRIMARY KEY("dendro_date_note_id"),
	CONSTRAINT "fk_dendro_date_notes_dendro_date_id" FOREIGN KEY ("dendro_date_id")
		REFERENCES "tbl_dendro_dates"("dendro_date_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dendro_date_notes" OWNER TO "seadworker";
4.146 tbl_species_association_types
Description
List of permissable associations between species, e.g. predates on; is predated by; parasitic on; parasitized by etc.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
association_type_id int4      
association_description text - - -      
association_type_name varchar(255) - - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
_idx_pk association_type_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   association_type_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_species_association_types" (
	"association_type_id" SERIAL NOT NULL,
	"association_description" text,
	"association_type_name" varchar(255),
	"date_updated" timestamp with time zone DEFAULT now(),
	PRIMARY KEY("association_type_id")
);

ALTER TABLE "tbl_species_association_types" OWNER TO "seadworker";
4.147 tbl_taxa_images
Description
Stores links to images of taxa either stored in another database or online.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxa_images_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
image_location text - - -      
image_name varchar - - -      
image_type_id int4 - - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk taxa_images_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   taxa_images_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_images" (
	"taxa_images_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"image_location" text,
	"image_name" varchar,
	"image_type_id" int4,
	"taxon_id" int4 NOT NULL,
	PRIMARY KEY("taxa_images_id"),
	CONSTRAINT "fk_taxa_images_taxa_tree_master_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_images_image_type_id" FOREIGN KEY ("image_type_id")
		REFERENCES "tbl_image_types"("image_type_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_taxa_images" OWNER TO "seadworker";
4.148 tbl_taxa_reference_specimens
Description
Information on reference and type specimens used for primary description of species.
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
taxa_reference_specimen_id int4      
contact_id int4 - -      
date_updated timestamp with time zone - - -   now()  
notes text - - -      
taxon_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk taxa_reference_specimen_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   taxa_reference_specimen_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_taxa_reference_specimens" (
	"taxa_reference_specimen_id" SERIAL NOT NULL,
	"contact_id" int4 NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"notes" text,
	"taxon_id" int4 NOT NULL,
	PRIMARY KEY("taxa_reference_specimen_id"),
	CONSTRAINT "fk_taxa_reference_specimens_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_taxa_reference_specimens_contact_id" FOREIGN KEY ("contact_id")
		REFERENCES "tbl_contacts"("contact_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_taxa_reference_specimens" OWNER TO "seadworker";
4.149 tbl_dendro_measurement_lookup
Description
Lists available values for each dendro measurement method where appropriate (i.e. categorical, rather than numerical values).
Annotation
This list feature may be best catered for in this way, rather than through methods?
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dendro_measurement_lookup_id int4      
date_updated timestamp with time zone - - -   now()  
dendro_measurement_id int4 - -      
value varchar - -      
Indexes Unique Columns Method Comment
_idx_pk dendro_measurement_lookup_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dendro_measurement_lookup_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dendro_measurement_lookup" (
	"dendro_measurement_lookup_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"dendro_measurement_id" int4 NOT NULL,
	"value" varchar NOT NULL,
	PRIMARY KEY("dendro_measurement_lookup_id"),
	CONSTRAINT "fk_dendro_measurement_lookup_dendro_measurement_id" FOREIGN KEY ("dendro_measurement_id")
		REFERENCES "tbl_dendro_measurements"("dendro_measurement_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dendro_measurement_lookup" OWNER TO "seadworker";
4.150 tbl_dating_material
Description
Details on material used for dating. Material may be either connected to a specific taxon (or pseudotaxon if at higher taxonomic level than species) or described in the material_dated
text field. Specific abundance elements (e.g. seed, elytron) may also be selected. A date may consist of multiple dated materials.
Annotation
 
Comment
20130722PIB: Added field date_updated
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dating_material_id int4      
abundance_element_id int4 - - -      
date_updated timestamp with time zone - - -   now()  
description text - - -      
geochron_id int4 - -      
material_dated varchar - - -      
taxon_id int4 - - -      
Indexes Unique Columns Method Comment
_idx_pk dating_material_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dating_material_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dating_material" (
	"dating_material_id" SERIAL NOT NULL,
	"abundance_element_id" int4,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"geochron_id" int4 NOT NULL,
	"material_dated" varchar,
	"taxon_id" int4,
	PRIMARY KEY("dating_material_id"),
	CONSTRAINT "fk_dating_material_abundance_elements_id" FOREIGN KEY ("abundance_element_id")
		REFERENCES "tbl_abundance_elements"("abundance_element_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_dating_material_taxa_tree_master_taxon_id" FOREIGN KEY ("taxon_id")
		REFERENCES "tbl_taxa_tree_master"("taxon_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE,
	CONSTRAINT "fk_dating_material_geochronology_geochron_id" FOREIGN KEY ("geochron_id")
		REFERENCES "tbl_geochronology"("geochron_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_dating_material" OWNER TO "seadworker";
4.151 tbl_bugs_periods
Description
Legacy date for synchronisation between BugsCEP and SEAD. May be removed when BugsCEP features are duplicated by SEAD.
Stores references to Bugs period definitions (but not the periods dates themselves, which are stored in 
tbl_bugs_dates_period)
Annotation
 
Comment
 
Owner
seadworker
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_dates_relative_id int4      
bugs_periodcode varchar - -     From Bugsdata.mdb: TDatesCalendar.CalendarCODE or TPeriods.PeriodCODE
date_updated timestamp with time zone - - -   now()  
relative_age_id int4 - -      
Indexes Unique Columns Method Comment
_idx_pk bugs_dates_relative_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   bugs_dates_relative_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_periods" (
	"bugs_dates_relative_id" SERIAL NOT NULL,
	"bugs_periodcode" varchar NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"relative_age_id" int4 NOT NULL,
	PRIMARY KEY("bugs_dates_relative_id"),
	CONSTRAINT "fk_bugs_periods_relative_ages_id" FOREIGN KEY ("relative_age_id")
		REFERENCES "tbl_relative_ages"("relative_age_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);

ALTER TABLE "tbl_bugs_periods" OWNER TO "seadworker";
4.152 tbl_dating_uncertainty
Description
Lookup table listing possible forms of uncertainty for dates, such as "from", "to", "Ca." and "?". These are used either to establish ranges for ages (e.g. from mesolithic to neolithic; from AD100 to AD300)
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
dating_uncertainty_id int4      
date_updated timestamp with time zone - - -   now()  
description text - - -      
uncertainty varchar - - -      
Indexes Unique Columns Method Comment
_idx_pk dating_uncertainty_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   dating_uncertainty_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_dating_uncertainty" (
	"dating_uncertainty_id" SERIAL NOT NULL,
	"date_updated" timestamp with time zone DEFAULT now(),
	"description" text,
	"uncertainty" varchar,
	PRIMARY KEY("dating_uncertainty_id")
);
4.153 tbl_bugs_datesmethods
Description
Used to store data from BugsCEP's TDatesMethods and allow for easier sync between the two systems. TDatesMethods stores names and types of dating methods.
Annotation
 
Comment
 
Owner
 
TemporaryWith OIDsFill Factor
-
Columns Data Type Primary Key Not Null AutoInc Flags Default Value Comment
bugs_datesmethods_id int4      
method_id int4 - - -      
bugs_abbrev varchar - - -      
bugs_method varchar - - -      
date_updated timestamp with time zone - - -   now()  
Indexes Unique Columns Method Comment
_idx_pk bugs_datesmethods_id  
Constraints Kind Expression Columns Comment
  PRIMARY KEY   bugs_datesmethods_id  
RulesKindInsteadExpressionBodyComment
TriggersTimeProcedureEventsFor Each RowComment
Create SQL
CREATE TABLE "tbl_bugs_datesmethods" (
	"bugs_datesmethods_id" SERIAL NOT NULL,
	"method_id" int4,
	"bugs_abbrev" varchar,
	"bugs_method" varchar,
	"date_updated" timestamp with time zone DEFAULT now(),
	PRIMARY KEY("bugs_datesmethods_id"),
	CONSTRAINT "fk_bugs_datesmethods_method_id" FOREIGN KEY ("method_id")
		REFERENCES "tbl_methods"("method_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE
);
5. References
5.1 fk_abundance_elements_record_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_record_types tbl_abundance_elements NO ACTION CASCADE record_type_id=record_type_id
5.2 fk_abundance_ident_levels_abundance_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_abundances tbl_abundance_ident_levels NO ACTION NO ACTION abundance_id=abundance_id
5.3 fk_abundance_ident_levels_identification_level_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_identification_levels tbl_abundance_ident_levels NO ACTION CASCADE identification_level_id=identification_level_id
5.4 fk_abundance_modifications_abundance_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_abundances tbl_abundance_modifications NO ACTION CASCADE abundance_id=abundance_id
5.5 fk_abundance_modifications_modification_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_modification_types tbl_abundance_modifications NO ACTION CASCADE modification_type_id=modification_type_id
5.6 fk_abundances_abundance_elements_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_abundance_elements tbl_abundances NO ACTION CASCADE abundance_element_id=abundance_element_id
5.7 fk_abundances_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_abundances NO ACTION CASCADE analysis_entity_id=analysis_entity_id
5.8 fk_abundances_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_abundances CASCADE CASCADE taxon_id=taxon_id
5.9 fk_aggregate_datasets_aggregate_order_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_aggregate_order_types tbl_aggregate_datasets NO ACTION CASCADE aggregate_order_type_id=aggregate_order_type_id
5.10 fk_aggregate_datasets_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_aggregate_datasets NO ACTION CASCADE biblio_id=biblio_id
5.11 fk_aggregate_sample_ages_aggregate_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_aggregate_datasets tbl_aggregate_sample_ages NO ACTION CASCADE aggregate_dataset_id=aggregate_dataset_id
5.12 fk_aggregate_sample_ages_analysis_entity_age_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entity_ages tbl_aggregate_sample_ages NO ACTION CASCADE analysis_entity_age_id=analysis_entity_age_id
5.13 fk_aggragate_samples_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_aggregate_samples NO ACTION CASCADE analysis_entity_id=analysis_entity_id
5.14 fk_aggregate_samples_aggregate_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_aggregate_datasets tbl_aggregate_samples NO ACTION CASCADE aggregate_dataset_id=aggregate_dataset_id
5.15 fk_analysis_entities_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_datasets tbl_analysis_entities NO ACTION CASCADE dataset_id=dataset_id
5.16 fk_analysis_entities_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_analysis_entities NO ACTION NO ACTION physical_sample_id=physical_sample_id
5.17 fk_analysis_entity_ages_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_analysis_entity_ages NO ACTION CASCADE analysis_entity_id=analysis_entity_id
5.18 fk_analysis_entity_ages_chronology_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_chronologies tbl_analysis_entity_ages NO ACTION CASCADE chronology_id=chronology_id
5.19 fk_biblio_collections_or_journals_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_collections_or_journals tbl_biblio NO ACTION CASCADE collection_or_journal_id=collection_or_journal_id
5.20 fk_biblio_publication_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_publication_types tbl_biblio NO ACTION CASCADE publication_type_id=publication_type_id
5.21 fk_biblio_publisher_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_publishers tbl_biblio NO ACTION CASCADE publisher_id=publisher_id
5.22 fk_bugs_abundance_codes_abundance_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_abundances tbl_bugs_abundance_codes CASCADE CASCADE abundance_id=abundance_id
5.23 fk_bugs_physical_samples_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_bugs_physical_samples NO ACTION CASCADE physical_sample_id=physical_sample_id
5.24 fk_bugs_sample_groups_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_bugs_sample_groups NO ACTION CASCADE sample_group_id=sample_group_id
5.25 fk_bugs_sites_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_bugs_sites NO ACTION CASCADE site_id=site_id
5.26 fk_chron_controls_chron_control_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_chron_control_types tbl_chron_controls NO ACTION CASCADE chron_control_type_id=chron_control_type_id
5.27 fk_chron_controls_chronology_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_chronologies tbl_chron_controls NO ACTION CASCADE chronology_id=chronology_id
5.28 fk_chronologies_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_chronologies NO ACTION CASCADE contact_id=contact_id
5.29 fk_chronologies_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_chronologies NO ACTION CASCADE sample_group_id=sample_group_id
5.30 fk_collections_or_journals_publisher_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_publishers tbl_collections_or_journals NO ACTION CASCADE publisher_id=publisher_id
5.31 fk_colours_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_colours NO ACTION CASCADE method_id=method_id
5.32 fk_data_types_data_type_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_data_type_groups tbl_data_types NO ACTION CASCADE data_type_group_id=data_type_group_id
5.33 fk_dataset_contacts_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_dataset_contacts NO ACTION CASCADE contact_id=contact_id
5.34 fk_dataset_contacts_contact_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contact_types tbl_dataset_contacts NO ACTION CASCADE contact_type_id=contact_type_id
5.35 fk_dataset_contacts_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_datasets tbl_dataset_contacts NO ACTION CASCADE dataset_id=dataset_id
5.36 fk_dataset_masters_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_dataset_masters NO ACTION CASCADE contact_id=contact_id
5.37 fk_dataset_masters_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_dataset_masters NO ACTION NO ACTION biblio_id=biblio_id
5.38 fk_dataset_submission_submission_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dataset_submission_types tbl_dataset_submissions NO ACTION CASCADE submission_type_id=submission_type_id
5.39 fk_dataset_submissions_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_dataset_submissions NO ACTION CASCADE contact_id=contact_id
5.40 fk_dataset_submissions_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_datasets tbl_dataset_submissions NO ACTION CASCADE dataset_id=dataset_id
5.41 fk_datasets_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_datasets NO ACTION CASCADE biblio_id=biblio_id
5.42 fk_datasets_data_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_data_types tbl_datasets NO ACTION CASCADE data_type_id=data_type_id
5.43 fk_datasets_master_set_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dataset_masters tbl_datasets NO ACTION CASCADE master_set_id=master_set_id
5.44 fk_datasets_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_datasets NO ACTION CASCADE method_id=method_id
5.45 fk_datasets_updated_dataset_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_datasets tbl_datasets NO ACTION CASCADE dataset_id=updated_dataset_id
5.46 fk_datasets_project_id
Description
 
Annotation
One project has many datasets
Parent Table Child Table Delete Action Update Action Link
tbl_projects tbl_datasets NO ACTION NO ACTION project_id=project_id
5.47 fk_dating_labs_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_dating_labs NO ACTION NO ACTION contact_id=contact_id
5.48 fk_dimensions_unit_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_units tbl_dimensions NO ACTION CASCADE unit_id=unit_id
5.49 fk_dimensions_method_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_method_groups tbl_dimensions NO ACTION NO ACTION method_group_id=method_group_id
5.50 fk_ecocode_definitions_ecocode_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_ecocode_groups tbl_ecocode_definitions NO ACTION CASCADE ecocode_group_id=ecocode_group_id
5.51 fk_ecocode_groups_ecocode_system_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_ecocode_systems tbl_ecocode_groups NO ACTION CASCADE ecocode_system_id=ecocode_system_id
5.52 fk_ecocode_systems_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_ecocode_systems NO ACTION CASCADE biblio_id=biblio_id
5.53 fk_ecocodes_ecocodedef_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_ecocode_definitions tbl_ecocodes NO ACTION CASCADE ecocode_definition_id=ecocode_definition_id
5.54 fk_ecocodes_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_ecocodes NO ACTION CASCADE taxon_id=taxon_id
5.55 fk_feature_type_id_feature_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_feature_types tbl_features CASCADE CASCADE feature_type_id=feature_type_id
5.56 fk_geochron_refs_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_geochron_refs NO ACTION CASCADE biblio_id=biblio_id
5.57 fk_geochron_refs_geochron_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_geochronology tbl_geochron_refs NO ACTION CASCADE geochron_id=geochron_id
5.58 fk_geochronology_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_geochronology NO ACTION CASCADE analysis_entity_id=analysis_entity_id
5.59 fk_geochronology_dating_labs_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dating_labs tbl_geochronology NO ACTION CASCADE dating_lab_id=dating_lab_id
5.60 fk_geochronology_dating_uncertainty_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dating_uncertainty tbl_geochronology NO ACTION NO ACTION dating_uncertainty_id=dating_uncertainty_id
5.61 fk_horizons_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_horizons NO ACTION CASCADE method_id=method_id
5.62 fk_imported_taxa_replacements_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_imported_taxa_replacements CASCADE CASCADE taxon_id=taxon_id
5.63 fk_lithology_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_lithology NO ACTION CASCADE sample_group_id=sample_group_id
5.64 fk_locations_location_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_location_types tbl_locations NO ACTION CASCADE location_type_id=location_type_id
5.65 fk_mcr_names_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_mcr_names NO ACTION CASCADE taxon_id=taxon_id
5.66 fk_mcr_summary_data_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_mcr_summary_data NO ACTION CASCADE taxon_id=taxon_id
5.67 fk_mcrdata_birmbeetledat_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_mcrdata_birmbeetledat NO ACTION CASCADE taxon_id=taxon_id
5.68 fk_measured_values_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_measured_values NO ACTION NO ACTION analysis_entity_id=analysis_entity_id
5.69 fk_measured_weights_value_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_measured_values tbl_measured_value_dimensions NO ACTION CASCADE measured_value_id=measured_value_id
5.70 fk_measured_value_dimensions_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dimensions tbl_measured_value_dimensions NO ACTION CASCADE dimension_id=dimension_id
5.71 fk_methods_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_methods NO ACTION CASCADE biblio_id=biblio_id
5.72 fk_methods_method_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_method_groups tbl_methods NO ACTION CASCADE method_group_id=method_group_id
5.73 fk_methods_record_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_record_types tbl_methods NO ACTION CASCADE record_type_id=record_type_id
5.74 fk_methods_unit_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_units tbl_methods NO ACTION CASCADE unit_id=unit_id
5.75 fk_physical_samples_sample_name_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_alt_ref_types tbl_physical_samples NO ACTION CASCADE alt_ref_type_id=alt_ref_type_id
5.76 fk_physical_samples_sample_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_types tbl_physical_samples NO ACTION CASCADE sample_type_id=sample_type_id
5.77 fk_samples_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_physical_samples NO ACTION CASCADE sample_group_id=sample_group_id
5.78 fk_projects_project_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_project_types tbl_projects NO ACTION NO ACTION project_type_id=project_type_id
5.79 fk_projects_project_stage_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_project_stages tbl_projects NO ACTION NO ACTION project_stage_id=project_stage_id
5.80 fk_rdb_rdb_code_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_rdb_codes tbl_rdb NO ACTION NO ACTION rdb_code_id=rdb_code_id
5.81 fk_rdb_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_rdb CASCADE CASCADE taxon_id=taxon_id
5.82 fk_tbl_rdb_tbl_location_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_locations tbl_rdb NO ACTION NO ACTION location_id=location_id
5.83 fk_rdb_codes_rdb_system_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_rdb_systems tbl_rdb_codes NO ACTION NO ACTION rdb_system_id=rdb_system_id
5.84 fk_rdb_systems_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_rdb_systems NO ACTION CASCADE biblio_id=biblio_id
5.85 fk_rdb_systems_location_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_locations tbl_rdb_systems NO ACTION NO ACTION location_id=location_id
5.86 fk_relative_age_refs_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_relative_age_refs NO ACTION CASCADE biblio_id=biblio_id
5.87 fk_relative_age_refs_relative_age_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_ages tbl_relative_age_refs NO ACTION CASCADE relative_age_id=relative_age_id
5.88 fk_relative_ages_relative_age_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_age_types tbl_relative_ages NO ACTION NO ACTION relative_age_type_id=relative_age_type_id
5.89 fk_relative_ages_location_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_locations tbl_relative_ages NO ACTION NO ACTION location_id=location_id
5.90 fk_relative_dates_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_relative_dates NO ACTION CASCADE physical_sample_id=physical_sample_id
5.91 fk_relative_dates_relative_age_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_ages tbl_relative_dates NO ACTION CASCADE relative_age_id=relative_age_id
5.92 fk_relative_dates_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_relative_dates NO ACTION NO ACTION method_id=method_id
5.93 fk_relative_dates_dating_uncertainty_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dating_uncertainty tbl_relative_dates NO ACTION NO ACTION dating_uncertainty_id=dating_uncertainty_id
5.94 fk_sample_alt_refs_alt_ref_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_alt_ref_types tbl_sample_alt_refs NO ACTION CASCADE alt_ref_type_id=alt_ref_type_id
5.95 fk_sample_alt_refs_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_alt_refs NO ACTION CASCADE physical_sample_id=physical_sample_id
5.96 fk_sample_colours_colour_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_colours tbl_sample_colours NO ACTION CASCADE colour_id=colour_id
5.97 fk_sample_colours_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_colours NO ACTION CASCADE physical_sample_id=physical_sample_id
5.98 fk_sample_dimensions_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dimensions tbl_sample_dimensions NO ACTION CASCADE dimension_id=dimension_id
5.99 fk_sample_dimensions_measurement_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_sample_dimensions NO ACTION CASCADE method_id=method_id
5.100 fk_sample_dimensions_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_dimensions NO ACTION CASCADE physical_sample_id=physical_sample_id
5.101 fk_sample_group_dimensions_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dimensions tbl_sample_group_dimensions NO ACTION CASCADE dimension_id=dimension_id
5.102 fk_sample_group_dimensions_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_dimensions NO ACTION CASCADE sample_group_id=sample_group_id
5.103 fk_sample_group_images_image_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_image_types tbl_sample_group_images NO ACTION CASCADE image_type_id=image_type_id
5.104 fk_sample_group_images_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_images NO ACTION NO ACTION sample_group_id=sample_group_id
5.105 fk_sample_group_references_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_sample_group_references NO ACTION CASCADE biblio_id=biblio_id
5.106 fk_sample_group_references_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_references NO ACTION CASCADE sample_group_id=sample_group_id
5.107 fk_sample_group_sampling_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_sampling_contexts tbl_sample_groups NO ACTION CASCADE sampling_context_id=sampling_context_id
5.108 fk_sample_groups_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_sample_groups NO ACTION CASCADE method_id=method_id
5.109 fk_sample_groups_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_sample_groups NO ACTION CASCADE site_id=site_id
5.110 fk_sample_horizons_horizon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_horizons tbl_sample_horizons NO ACTION CASCADE horizon_id=horizon_id
5.111 fk_sample_horizons_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_horizons NO ACTION CASCADE physical_sample_id=physical_sample_id
5.112 fk_sample_images_image_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_image_types tbl_sample_images NO ACTION CASCADE image_type_id=image_type_id
5.113 fk_sample_images_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_images NO ACTION CASCADE physical_sample_id=physical_sample_id
5.114 fk_sample_notes_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_notes NO ACTION CASCADE physical_sample_id=physical_sample_id
5.115 fk_seasons_season_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_season_types tbl_seasons NO ACTION CASCADE season_type_id=season_type_id
5.116 fk_site_images_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_site_images NO ACTION CASCADE contact_id=contact_id
5.117 fk_site_images_image_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_image_types tbl_site_images NO ACTION CASCADE image_type_id=image_type_id
5.118 fk_site_images_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_images NO ACTION NO ACTION site_id=site_id
5.119 fk_locations_location_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_locations tbl_site_locations NO ACTION NO ACTION location_id=location_id
5.120 fk_locations_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_locations NO ACTION NO ACTION site_id=site_id
5.121 fk_site_other_records_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_site_other_records NO ACTION CASCADE biblio_id=biblio_id
5.122 fk_site_other_records_record_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_record_types tbl_site_other_records NO ACTION CASCADE record_type_id=record_type_id
5.123 fk_site_other_records_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_other_records NO ACTION CASCADE site_id=site_id
5.124 fk_site_references_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_site_references NO ACTION CASCADE biblio_id=biblio_id
5.125 fk_site_references_site_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_references NO ACTION CASCADE site_id=site_id
5.126 fk_species_associations_associated_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_species_associations NO ACTION CASCADE taxon_id=taxon_id
5.127 fk_species_associations_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_species_associations NO ACTION CASCADE biblio_id=biblio_id
5.128 fk_species_associations_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_species_associations NO ACTION NO ACTION taxon_id=taxon_id
5.129 fk_species_associations_association_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_species_association_types tbl_species_associations NO ACTION NO ACTION association_type_id=association_type_id
5.130 fk_taxa_common_names_language_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_languages tbl_taxa_common_names NO ACTION CASCADE language_id=language_id
5.131 fk_taxa_common_names_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_common_names NO ACTION CASCADE taxon_id=taxon_id
5.132 fk_taxa_measured_attributes_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_measured_attributes CASCADE CASCADE taxon_id=taxon_id
5.133 fk_taxa_seasonality_activity_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_activity_types tbl_taxa_seasonality NO ACTION CASCADE activity_type_id=activity_type_id
5.134 fk_taxa_seasonality_season_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_seasons tbl_taxa_seasonality NO ACTION CASCADE season_id=season_id
5.135 fk_taxa_seasonality_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_seasonality CASCADE CASCADE taxon_id=taxon_id
5.136 fk_taxa_seasonality_location_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_locations tbl_taxa_seasonality NO ACTION NO ACTION location_id=location_id
5.137 fk_taxa_synonyms_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_taxa_synonyms NO ACTION CASCADE biblio_id=biblio_id
5.138 fk_taxa_synonyms_family_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_families tbl_taxa_synonyms NO ACTION CASCADE family_id=family_id
5.139 fk_taxa_synonyms_genus_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_genera tbl_taxa_synonyms NO ACTION CASCADE genus_id=genus_id
5.140 fk_taxa_synonyms_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_synonyms CASCADE CASCADE taxon_id=taxon_id
5.141 fk_taxa_synonyms_taxa_tree_author_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_authors tbl_taxa_synonyms NO ACTION NO ACTION author_id=author_id
5.142 fk_taxa_tree_families_order_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_orders tbl_taxa_tree_families CASCADE CASCADE order_id=order_id
5.143 fk_taxa_tree_genera_family_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_families tbl_taxa_tree_genera CASCADE CASCADE family_id=family_id
5.144 fk_taxa_tree_master_author_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_authors tbl_taxa_tree_master NO ACTION CASCADE author_id=author_id
5.145 fk_taxa_tree_master_genus_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_genera tbl_taxa_tree_master CASCADE CASCADE genus_id=genus_id
5.146 fk_taxa_tree_orders_record_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_record_types tbl_taxa_tree_orders NO ACTION CASCADE record_type_id=record_type_id
5.147 fk_taxonomic_order_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxonomic_order CASCADE CASCADE taxon_id=taxon_id
5.148 fk_taxonomic_order_taxonomic_order_system_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxonomic_order_systems tbl_taxonomic_order NO ACTION CASCADE taxonomic_order_system_id=taxonomic_order_system_id
5.149 fk_taxonomic_order_biblio_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_taxonomic_order_biblio NO ACTION CASCADE biblio_id=biblio_id
5.150 fk_taxonomic_order_biblio_taxonomic_order_system_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxonomic_order_systems tbl_taxonomic_order_biblio NO ACTION CASCADE taxonomic_order_system_id=taxonomic_order_system_id
5.151 fk_taxonomy_notes_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_taxonomy_notes NO ACTION CASCADE biblio_id=biblio_id
5.152 fk_taxonomy_notes_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxonomy_notes CASCADE CASCADE taxon_id=taxon_id
5.153 fk_tephra_dates_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_tephra_dates NO ACTION CASCADE analysis_entity_id=analysis_entity_id
5.154 fk_tephra_dates_tephra_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_tephras tbl_tephra_dates NO ACTION CASCADE tephra_id=tephra_id
5.155 fk_tephra_dates_dating_uncertainty_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dating_uncertainty tbl_tephra_dates NO ACTION NO ACTION dating_uncertainty_id=dating_uncertainty_id
5.156 fk_tephra_refs_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_tephra_refs NO ACTION CASCADE biblio_id=biblio_id
5.157 fk_tephra_refs_tephra_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_tephras tbl_tephra_refs NO ACTION CASCADE tephra_id=tephra_id
5.158 fk_text_biology_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_text_biology NO ACTION CASCADE biblio_id=biblio_id
5.159 fk_text_biology_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_text_biology CASCADE CASCADE taxon_id=taxon_id
5.160 fk_text_distribution_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_text_distribution NO ACTION CASCADE biblio_id=biblio_id
5.161 fk_text_distribution_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_text_distribution CASCADE CASCADE taxon_id=taxon_id
5.162 fk_text_identification_keys_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_text_identification_keys NO ACTION CASCADE biblio_id=biblio_id
5.163 fk_text_identification_keys_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_text_identification_keys CASCADE CASCADE taxon_id=taxon_id
5.164 fk_site_preservation_status_site_id
Description
Allows mulitple site preservation/threat records per site
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_preservation_status NO ACTION CASCADE site_id=site_id
5.165 fk_bugs_biblio_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_bugs_biblio NO ACTION NO ACTION biblio_id=biblio_id
5.166 fk_physical_sample_features_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_physical_sample_features CASCADE CASCADE physical_sample_id=physical_sample_id
5.167 fk_physical_sample_features_feature_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_features tbl_physical_sample_features CASCADE CASCADE feature_id=feature_id
5.168 fk_biblio_keywords_keyword_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_keywords tbl_biblio_keywords NO ACTION CASCADE keyword_id=keyword_id
5.169 fk_biblio_keywords_biblio_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_biblio tbl_biblio_keywords NO ACTION CASCADE biblio_id=biblio_id
5.170 fk_analysis_entity_dimensions_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_analysis_entity_dimensions CASCADE CASCADE analysis_entity_id=analysis_entity_id
5.171 fk_analysis_entity_dimensions_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dimensions tbl_analysis_entity_dimensions NO ACTION CASCADE dimension_id=dimension_id
5.172 fk_site_natgridrefs_sites_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sites tbl_site_natgridrefs NO ACTION NO ACTION site_id=site_id
5.173 fk_site_natgridrefs_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_site_natgridrefs NO ACTION NO ACTION method_id=method_id
5.174 fk_bugs_dates_radio_geochron_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_geochronology tbl_bugs_dates_radio NO ACTION NO ACTION geochron_id=geochron_id
5.175 fk_bugs_dates_calendar_relative_dates_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_dates tbl_bugs_dates_calendar NO ACTION NO ACTION relative_date_id=relative_date_id
5.176 fk_sample_coordinates_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_coordinates NO ACTION NO ACTION physical_sample_id=physical_sample_id
5.177 fk_sample_coordinates_coordinate_method_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_coordinate_method_dimensions tbl_sample_coordinates NO ACTION CASCADE coordinate_method_dimension_id=coordinate_method_dimension_id
5.178 fk_bugs_dates_period_relative_date_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_dates tbl_bugs_dates_period NO ACTION NO ACTION relative_date_id=relative_date_id
5.179 fk_analysis_entity_prep_methods_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_analysis_entity_prep_methods NO ACTION NO ACTION analysis_entity_id=analysis_entity_id
5.180 fk_analysis_entity_prep_methods_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_analysis_entity_prep_methods NO ACTION NO ACTION method_id=method_id
5.181 fk_sample_group_descriptions_sample_group_description_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_description_types tbl_sample_group_descriptions NO ACTION CASCADE sample_group_description_type_id=sample_group_description_type_id
5.182 fk_sample_groups_sample_group_descriptions_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_descriptions NO ACTION CASCADE sample_group_id=sample_group_id
5.183 fk_tbl_sample_group_notes_sample_groups
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_notes NO ACTION CASCADE sample_group_id=sample_group_id
5.184 fk_sample_group_description_type_sampling_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_description_types tbl_sample_group_description_type_sampling_contexts NO ACTION NO ACTION sample_group_description_type_id=sample_group_description_type_id
5.185 fk_sample_group_sampling_context_id0
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_sampling_contexts tbl_sample_group_description_type_sampling_contexts NO ACTION NO ACTION sampling_context_id=sampling_context_id
5.186 fk_sample_group_positions_sample_group_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_groups tbl_sample_group_coordinates NO ACTION NO ACTION sample_group_id=sample_group_id
5.187 fk_sample_group_positions_coordinate_method_dimension_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_coordinate_method_dimensions tbl_sample_group_coordinates NO ACTION CASCADE coordinate_method_dimension_id=coordinate_method_dimension_id
5.188 fk_sample_descriptions_sample_description_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_description_types tbl_sample_descriptions NO ACTION NO ACTION sample_description_type_id=sample_description_type_id
5.189 fk_sample_descriptions_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_descriptions NO ACTION NO ACTION physical_sample_id=physical_sample_id
5.190 fk_sample_locations_physical_sample_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_physical_samples tbl_sample_locations NO ACTION NO ACTION physical_sample_id=physical_sample_id
5.191 fk_sample_locations_sample_location_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_location_types tbl_sample_locations NO ACTION NO ACTION sample_location_type_id=sample_location_type_id
5.192 fk_sample_description_types_sample_group_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_description_types tbl_sample_description_sample_group_contexts NO ACTION NO ACTION sample_description_type_id=sample_description_type_id
5.193 fk_sample_description_sample_group_contexts_sampling_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_sampling_contexts tbl_sample_description_sample_group_contexts NO ACTION NO ACTION sampling_context_id=sampling_context_id
5.194 fk_sample_location_sampling_contexts_sampling_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_location_types tbl_sample_location_type_sampling_contexts NO ACTION NO ACTION sample_location_type_id=sample_location_type_id
5.195 fk_sample_location_type_sampling_context_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_sample_group_sampling_contexts tbl_sample_location_type_sampling_contexts NO ACTION NO ACTION sampling_context_id=sampling_context_id
5.196 fk_coordinate_method_dimensions_dimensions_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dimensions tbl_coordinate_method_dimensions NO ACTION CASCADE dimension_id=dimension_id
5.197 fk_coordinate_method_dimensions_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_coordinate_method_dimensions NO ACTION CASCADE method_id=method_id
5.198 fk_dendro_dendro_measurement_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dendro_measurements tbl_dendro NO ACTION NO ACTION dendro_measurement_id=dendro_measurement_id
5.199 fk_dendro_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_dendro NO ACTION NO ACTION analysis_entity_id=analysis_entity_id
5.200 fk_dendro_measurements_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_dendro_measurements NO ACTION NO ACTION method_id=method_id
5.201 fk_ceramics_ceramics_measurement_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_ceramics_measurements tbl_ceramics NO ACTION NO ACTION ceramics_measurement_id=ceramics_measurement_id
5.202 fk_ceramics_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_ceramics NO ACTION NO ACTION analysis_entity_id=analysis_entity_id
5.203 fk_ceramics_measurements_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_ceramics_measurements NO ACTION NO ACTION method_id=method_id
5.204 fk_ceramics_measurement_lookup_ceramics_measurements_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_ceramics_measurements tbl_ceramics_measurement_lookup NO ACTION NO ACTION ceramics_measurement_id=ceramics_measurement_id
5.205 fk_dendro_dates_years_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_years_types tbl_dendro_dates NO ACTION NO ACTION years_type_id=years_type_id
5.206 fk_dendro_dates_analysis_entity_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_analysis_entities tbl_dendro_dates NO ACTION NO ACTION analysis_entity_id=analysis_entity_id
5.207 fk_dendro_dates_dating_uncertainty_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dating_uncertainty tbl_dendro_dates NO ACTION NO ACTION dating_uncertainty_id=dating_uncertainty_id
5.208 fk_dendro_date_notes_dendro_date_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dendro_dates tbl_dendro_date_notes NO ACTION NO ACTION dendro_date_id=dendro_date_id
5.209 fk_taxa_images_taxa_tree_master_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_images NO ACTION NO ACTION taxon_id=taxon_id
5.210 fk_taxa_images_image_type_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_image_types tbl_taxa_images NO ACTION NO ACTION image_type_id=image_type_id
5.211 fk_taxa_reference_specimens_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_taxa_reference_specimens NO ACTION NO ACTION taxon_id=taxon_id
5.212 fk_taxa_reference_specimens_contact_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_contacts tbl_taxa_reference_specimens NO ACTION NO ACTION contact_id=contact_id
5.213 fk_dendro_measurement_lookup_dendro_measurement_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_dendro_measurements tbl_dendro_measurement_lookup NO ACTION NO ACTION dendro_measurement_id=dendro_measurement_id
5.214 fk_dating_material_abundance_elements_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_abundance_elements tbl_dating_material NO ACTION NO ACTION abundance_element_id=abundance_element_id
5.215 fk_dating_material_taxa_tree_master_taxon_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_taxa_tree_master tbl_dating_material NO ACTION NO ACTION taxon_id=taxon_id
5.216 fk_dating_material_geochronology_geochron_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_geochronology tbl_dating_material NO ACTION NO ACTION geochron_id=geochron_id
5.217 fk_bugs_periods_relative_ages_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_relative_ages tbl_bugs_periods NO ACTION NO ACTION relative_age_id=relative_age_id
5.218 fk_bugs_datesmethods_method_id
Description
 
Annotation
 
Parent Table Child Table Delete Action Update Action Link
tbl_methods tbl_bugs_datesmethods NO ACTION NO ACTION method_id=method_id
6. Stored Procedures
6.1 inttobool
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
IN int4
IN bool
Create SQL
CREATE OR REPLACE FUNCTION "inttobool" (IN  int4, IN  bool) 	
RETURNS bool AS
$BODY$
begin
  if $1=0 and not $2 then
  return true;
  elsif $1<>0 and $2 then
  return true;
  else
          return false;
  end if;
end;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "inttobool" (IN  int4, IN  bool) OWNER TO "postgres";

6.2 inttobool
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
IN bool
IN int4
Create SQL
CREATE OR REPLACE FUNCTION "inttobool" (IN  bool, IN  int4) 	
RETURNS bool AS
$BODY$
begin
  return inttobool($2, $1);
end;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "inttobool" (IN  bool, IN  int4) OWNER TO "postgres";

6.3 notinttobool
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
IN bool
IN int4
Create SQL
CREATE OR REPLACE FUNCTION "notinttobool" (IN  bool, IN  int4) 	
RETURNS bool AS
$BODY$
begin
  return not inttobool($2,$1);
end;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "notinttobool" (IN  bool, IN  int4) OWNER TO "postgres";

6.4 notinttobool
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
IN int4
IN bool
Create SQL
CREATE OR REPLACE FUNCTION "notinttobool" (IN  int4, IN  bool) 	
RETURNS bool AS
$BODY$
begin
return not inttobool($1,$2);
end;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "notinttobool" (IN  int4, IN  bool) OWNER TO "postgres";

6.5 plpgsql_call_handler
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
Create SQL
CREATE OR REPLACE FUNCTION "plpgsql_call_handler" () 	
RETURNS language_handler AS
'$libdir/plpgsql', 'plpgsql_call_handler'
	LANGUAGE c
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "plpgsql_call_handler" () OWNER TO "postgres";

6.6 update_dates
Description
 
Annotation
 
Comment
 
Owner
postgres
Param Name Param Type Data Type
Create SQL
CREATE OR REPLACE FUNCTION "update_dates" () 	
RETURNS int4 AS
$BODY$
declare
  t_name RECORD;
  res RECORD;
begin
  for t_name in select distinct t.table_name from information_schema.tables t where t.table_schema = 'public' loop
    -- execute 'update ' || t_name.table_name || ' set date_updated = now()';
    begin
      execute 'update ' || t_name.table_name || ' set date_updated = now() where date_updated is null';
      execute 'insert into sumry (select distinct ''' || t_name.table_name || '''
      , date_updated from ' || t_name.table_name || ') ';
    exception when undefined_column then
      --
    end;
  end loop;
  return 1;
end;
$BODY$
	LANGUAGE plpgsql
	CALLED ON NULL INPUT
	VOLATILE
	EXTERNAL SECURITY INVOKER;


ALTER FUNCTION "update_dates" () OWNER TO "postgres";

7. Views