GLHI Database HTML Dump
Previous topic Chapter index Next topic

Table: tblProject

 

 

Schema

public

 

Owner

wetlands

 

Tablespace

(default)

 

Descriptions

Main table for the GLHRI, all other tables are linked in some fashion to this in order to fully describe a project.

 

Fields

PK

FK

Name

Data type

Not null

Unique

Inherited

Default

Description

 

PROJECT_ID

integer

 

nextval('"tblProject_ProjID_seq"'::regclass)

PROJECT_ID is the NERI-derived master key for the GLHI database. All tables can be referenced using this key.

 

 

ProjectName

varchar(255)

 

 

 

Official title of the project

FORM: "Title"

 

 

ProjContactID

integer

 

 

0

key to contacts table for point of contact

FORM: "Project Point of Contact"

 

 

ProposalDate

timestamp

 

 

 

 

creation timestamp

 

StatusID

integer

 

 

 

key to status table lookup

FORM: "Project Status"

 

 

Abstract

text

 

 

 

Raw text describing the project

FORM: "Abstract"

 

 

PrpsdFunctionGain

integer

 

 

 

0

 

 

 

ProjNotes

text

 

 

 

 

Additional project notes

FORM: "Additional Project Information"

 

 

OriginalID

varchar(255)

 

 

 

 

interal field used to link against existing projects being uploaded into db

 

 

UpdDate

timestamp

 

 

 

 

last update timestamp

 

 

UpDaterNameID

integer

 

 

 

0

key to contacts table linked to the person who last updated table

 

ProjCompletenessID

integer

 

 

 

 

Completeness of restoration

 

 

MonitoringInfoID

integer

 

 

 

 

key to main monitoring table

 

 

FundingInfoID

integer

 

 

 

 

Key to funding table

 

GeographyID

integer

 

 

 

 

key to main geography table

 

AccessConstraintID

integer

 

 

 

 

key to access constraint table

FORM: "Access Constraints"

 

ProjSignificanceID

integer

 

 

 

 

key to significance table

 

CreatedByID

integer

 

 

 

key to contact table, capturing owner of dataset

 

 

ProjectDurationMonths

integer

 

 

 

 

FORM: "Project Duration"

 

 

USER_ENTERED

timestamp(0)

 

 

 

 

 

 

 

USER_UPDATED

timestamp(0)

 

 

 

 

 

 

 

ExternalProject

boolean

 

 

 

 

FORM: "Relation to Existing Projects, Is this project part of a larger habitat protection or restoration plan or project?"

 

 

ExternalProjectInfo

text

 

 

 

 

FORM: "Relation to Existing Projects, If yes, please enter the name of the related project or plan."

 

 

OrgRelevantRanking

text

 

 

 

 

Field to capture internal ranking information.

FORM: "Organizational Relevant Ranking"

 

 

LegalAuthorityandCostShare

text

 

 

 

 

Field to capture information on any legal considerations or local cost shares in place.

FORM: "Legal Authority and Cost Share Agreements in Place"

 

 

CompensatoryMitigation

boolean

 

 

 

 

Field to capture whether the project is part of a compensatory mitigation plan or other legal action.

FORM: "Compensatory Mitigation"

 

 

ProjectStartDate

date

 

 

 

 

Project start date

FORM: "Project Timeline"

 

 

ProjectEndDate

date

 

 

 

 

Project end date

FORM: "Project Timeline"

 

RestorationID

integer

 

 

 

 

 

 

Foreign Keys

Name

Fields

FK Table

FK Fields

Delete Action

Update Action

Deferrable

Check Time

Description

tblProject_fk

GeographyID

public.tblProjectGeography

GeographyID

No Action

No Action

 

Immediate

 

tblProject_fk1

RestorationID

public.tblRestoration

RestorationID

No Action

No Action

 

Immediate

 

tblProject_fk3

ProjSignificanceID

public.tblProjectSignificance

ProjectSignificanceID

No Action

No Action

 

Immediate

 

tblProject_fk4

StatusID

public.tblStatusLU

StatusLUID

No Action

No Action

 

Immediate

 

tblProject_fk5

ProjCompletenessID

public.tblProjectCompletenessLU

ProjCompletenessID

No Action

No Action

 

Immediate

 

tblProject_fk6

AccessConstraintID

public.tblAccessConstraintsLU

AccessConstraintLUID

No Action

No Action

 

Immediate

 

test

CreatedByID

public.tblContactsLU

ContactID

No Action

No Action

 

Immediate

 

 

Checks

There are no check constraints for table tblProject

 

Indices

Name

Type

Function

Fields

Primary Key

Unique

Description

tblProject_pkey

btree

 

PROJECT_ID

 

 

Triggers

There are no triggers for table tblProject

 

Rules

There are no rules for table tblProject

 

Referenced

Table

Schema

Foreign Key

Fields

FK Table

FK Fields

Delete Action

Update Action

Deferrable

Check Time

Description

PhotoUpload

public

PhotoUpload_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblCongressionalDistrict

public

tblCongressionalDistrict_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblFunding

public

tblFunding_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblHabitat

public

tblHabitat_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblHucs

public

tblHucs_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblMonitoring

public

Monitoring_fkey

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblProjectSignificance

public

tblProjectSignificance_fk

ProjectID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblRealEstate

public

tblRealEstate_fk2

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblRestoration

public

tblRestoration_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblSoils

public

tblSoils_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblStateCounty

public

tblStateCounty_fk

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

tblThreats

public

tblThreats_fk1

PROJECT_ID

public.tblProject

PROJECT_ID

No Action

No Action

 

Immediate

 

 

Options

Option

Value

Inherited From

 

Rows

0

Pages

0

System

 

Temporary

 

With OID

 

 

Definition


CREATE TABLE "public"."tblProject" (
  "PROJECT_ID" SERIAL, 
  "ProjectName" VARCHAR(255) NOT NULL, 
  "ProjContactID" INTEGER DEFAULT 0 NOT NULL, 
  "ProposalDate" TIMESTAMP WITHOUT TIME ZONE, 
  "StatusID" INTEGER NOT NULL, 
  "Abstract" TEXT NOT NULL, 
  "PrpsdFunctionGain" INTEGER DEFAULT 0, 
  "ProjNotes" TEXT, 
  "OriginalID" VARCHAR(255), 
  "UpdDate" TIMESTAMP WITHOUT TIME ZONE, 
  "UpDaterNameID" INTEGER DEFAULT 0, 
  "ProjCompletenessID" INTEGER, 
  "MonitoringInfoID" INTEGER, 
  "FundingInfoID" INTEGER, 
  "GeographyID" INTEGER, 
  "AccessConstraintID" INTEGER, 
  "ProjSignificanceID" INTEGER, 
  "CreatedByID" INTEGER NOT NULL, 
  "ProjectDurationMonths" INTEGER, 
  "USER_ENTERED" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "USER_UPDATED" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "ExternalProject" BOOLEAN, 
  "ExternalProjectInfo" TEXT, 
  "OrgRelevantRanking" TEXT, 
  "LegalAuthorityandCostShare" TEXT, 
  "CompensatoryMitigation" BOOLEAN, 
  "ProjectStartDate" DATE, 
  "ProjectEndDate" DATE, 
  "RestorationID" INTEGER, 
  CONSTRAINT "tblProject_pkey" PRIMARY KEY("PROJECT_ID"), 
  CONSTRAINT "tblProject_fk" FOREIGN KEY ("GeographyID")
    REFERENCES "public"."tblProjectGeography"("GeographyID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "tblProject_fk1" FOREIGN KEY ("RestorationID")
    REFERENCES "public"."tblRestoration"("RestorationID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "tblProject_fk3" FOREIGN KEY ("ProjSignificanceID")
    REFERENCES "public"."tblProjectSignificance"("ProjectSignificanceID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "tblProject_fk4" FOREIGN KEY ("StatusID")
    REFERENCES "public"."tblStatusLU"("StatusLUID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "tblProject_fk5" FOREIGN KEY ("ProjCompletenessID")
    REFERENCES "public"."tblProjectCompletenessLU"("ProjCompletenessID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "tblProject_fk6" FOREIGN KEY ("AccessConstraintID")
    REFERENCES "public"."tblAccessConstraintsLU"("AccessConstraintLUID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT "test" FOREIGN KEY ("CreatedByID")
    REFERENCES "public"."tblContactsLU"("ContactID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

COMMENT ON TABLE "public"."tblProject"
IS 'Main table for the GLHRI, all other tables are linked in some fashion to this in order to fully describe a project.';

COMMENT ON COLUMN "public"."tblProject"."PROJECT_ID"
IS 'PROJECT_ID is the NERI-derived master key for the GLHI database. All tables can be referenced using this key.';

COMMENT ON COLUMN "public"."tblProject"."ProjectName"
IS 'Official title of the project

FORM: "Title"';

COMMENT ON COLUMN "public"."tblProject"."ProjContactID"
IS 'key to contacts table for point of contact

FORM: "Project Point of Contact"';

COMMENT ON COLUMN "public"."tblProject"."ProposalDate"
IS 'creation timestamp';

COMMENT ON COLUMN "public"."tblProject"."StatusID"
IS 'key to status table lookup

FORM: "Project Status"';

COMMENT ON COLUMN "public"."tblProject"."Abstract"
IS 'Raw text describing the project

FORM: "Abstract"';

COMMENT ON COLUMN "public"."tblProject"."ProjNotes"
IS 'Additional project notes

FORM: "Additional Project Information"';

COMMENT ON COLUMN "public"."tblProject"."OriginalID"
IS 'interal field used to link against existing projects being uploaded into db';

COMMENT ON COLUMN "public"."tblProject"."UpdDate"
IS 'last update timestamp';

COMMENT ON COLUMN "public"."tblProject"."UpDaterNameID"
IS 'key to contacts table linked to the person who last updated table';

COMMENT ON COLUMN "public"."tblProject"."ProjCompletenessID"
IS 'Completeness of restoration';

COMMENT ON COLUMN "public"."tblProject"."MonitoringInfoID"
IS 'key to main monitoring table';

COMMENT ON COLUMN "public"."tblProject"."FundingInfoID"
IS 'Key to funding table';

COMMENT ON COLUMN "public"."tblProject"."GeographyID"
IS 'key to main geography table';

COMMENT ON COLUMN "public"."tblProject"."AccessConstraintID"
IS 'key to access constraint table

FORM: "Access Constraints"';

COMMENT ON COLUMN "public"."tblProject"."ProjSignificanceID"
IS 'key to significance table';

COMMENT ON COLUMN "public"."tblProject"."CreatedByID"
IS 'key to contact table, capturing owner of dataset';

COMMENT ON COLUMN "public"."tblProject"."ProjectDurationMonths"
IS 'FORM: "Project Duration"';

COMMENT ON COLUMN "public"."tblProject"."ExternalProject"
IS 'FORM: "Relation to Existing Projects, Is this project part of a larger habitat protection or restoration plan or project?"';

COMMENT ON COLUMN "public"."tblProject"."ExternalProjectInfo"
IS 'FORM: "Relation to Existing Projects, If yes, please enter the name of the related project or plan."';

COMMENT ON COLUMN "public"."tblProject"."OrgRelevantRanking"
IS 'Field to capture internal ranking information.

FORM: "Organizational Relevant Ranking"';

COMMENT ON COLUMN "public"."tblProject"."LegalAuthorityandCostShare"
IS 'Field to capture information on any legal considerations or local cost shares in place.

FORM: "Legal Authority and Cost Share Agreements in Place"';

COMMENT ON COLUMN "public"."tblProject"."CompensatoryMitigation"
IS 'Field to capture whether the project is part of a compensatory mitigation plan or other legal action.

FORM: "Compensatory Mitigation"';

COMMENT ON COLUMN "public"."tblProject"."ProjectStartDate"
IS 'Project start date

FORM: "Project Timeline"';

COMMENT ON COLUMN "public"."tblProject"."ProjectEndDate"
IS 'Project end date

FORM: "Project Timeline"';

Pete Giencke
pgiencke@glc.org
Previous topic Chapter index Next topic