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