Tables


SchemaSpy Analysis of default.public

Generated on Sat Jan 11 11:04 GMT 2025

XML Representation
Insertion Order Deletion Order
TABLES 55
VIEWS 3
COLUMNS 491
Constraints 40
Anomalies 5
Routines 808

Database Properties

Database Type: PostgreSQL - 16.4 (Debian 16.4-1.pgdg110+2)

Schema public

standard public schema

Tables

Table / View Children Parents Columns Rows Type Comments
violation_code 1 1 10 -1 Table

The alleged violation involved in the complaint. (E.g. ORV = Off Road Vehicles; PESTICDE = Pesticide)

geo_organization_unit_code 4 1 11 -1 Table

A geographical organization unit is a named geographical boundary that represents a physical location. The level of granularity can vary with Regions being the highest level - for example Okanagan, and Areas being the lowest level - for example Big White

geo_org_unit_structure 0 3 10 -1 Table

A geographical organization unit structure is a parent/child relationship between two geographical organization units.

staging_metadata_mapping 0 1 8 -1 Table
person_complaint_xref_code 1 0 9 -1 Table

Used to track the relationship type between person and complaint. For example: ‘ASSIGNEE’ = Assignee

flyway_schema_history 0 0 10 -1 Table
office_h 0 0 6 -1 Table

History table for office table

allegation_complaint 0 2 10 -1 Table

A complaint for which a caller believes that a Violation has occurred and should be investigated.

hwcr_complaint 1 3 9 -1 Table

A complaint that a caller believes could involve a conflict between Humans and Wildlife.

geo_org_unit_type_code 1 0 9 -1 Table

A geographical organization unit type describes the level of granularity for a given geographical organization unit. Supported geographical organization unit types are (ZONE = Zone; REGION = Region; OFFLOC = Office Location; AREA = Area).

complaint_method_received_code 1 0 9 -1 Table

Methods in which the complaint was received. Examples include: DGIR forward, Direct email or phone call,Minister’s office,RAPP ,Referral

agency_code 7 0 9 -1 Table

An agency is an organized and named grouping of people that interacts in some way with the Ministry.

complaint_status_code 1 0 10 -1 Table

The status of a Complaint. Values include OPEN = Open and CLOSED = Closed.

person 2 0 9 -1 Table

A person is an individual that is being tracked explicitly within the system. The criteria for being included as a trackable individual is the possession of a unique identifier that can be used for dealiasing. For example an IDIR or a BC Drivers Licence.

team_h 0 0 6 -1 Table

History table for team table

feature_agency_xref 0 2 8 -1 Table

Maintains a mapping between FEATURES and AGENCIES that allow for features to be displayed or hidden as required.

configuration 0 0 8 -1 Table

The configuration table is used to store constants which are expected to change over the lifecycle of the application, or have different values in different environments. By making changes to in the database the behaviour of the application can be altered without requiring a full deployment.

staging_complaint 0 3 9 -1 Table
allegation_complaint_h 0 0 6 -1 Table

History table for allegation_complaint table

geo_org_unit_structure_h 0 0 6 -1 Table

History table for geo_org_unit_structure table

species_code 1 0 11 -1 Table

The species involved in a Human Wildlife Conflict (E.g. BLKBEAR = Black Bear; WOLVERN = Wolverine)

entity_code 1 0 9 -1 Table
attractant_hwcr_xref_h 0 0 6 -1 Table

History table for attractant_hwcr_xref table

hwcr_complaint_nature_code 1 0 9 -1 Table

Modifier that further describes the nature of Human Wildlife Conflict complaints. (E.g. DEADNV = Dead wildlife - no violation suspected; TRAP = Wildlife in trap)

action_taken_h 0 0 6 -1 Table

History table for complaint table

reported_by_code 1 0 9 -1 Table
person_complaint_xref 0 3 9 -1 Table

Used to create a relationship between a person and a complaint. One person can play many roles on a complaint, and many people could be involved in a single complaint.

gir_complaint 0 2 7 -1 Table
spatial_ref_sys 2 0 5 8500 Table
officer_team_xref 0 2 8 -1 Table

Defines the teams an officer may be on

gir_type_code 1 0 9 -1 Table
action_taken 0 2 10 -1 Table

Contains information about who the call centre staff contacted and when to allow Officers to know who to follow up with and/or if someone else is already taking action.

person_h 0 0 6 -1 Table

History table for person table

attractant_hwcr_xref 0 2 8 -1 Table

Cross reference table for linking attractants to HWCR Complaints.

complaint_type_code 0 1 12 -1 Table
staging_activity_code 1 0 9 -1 Table
team_code 1 0 9 -1 Table

An team is an organized and named grouping of people that interacts in some way with the Ministry.

officer_team_xref_h 0 0 6 -1 Table

History table for officer_team_xref table

officer_h 0 0 6 -1 Table

History table for officer table

person_complaint_xref_h 0 0 6 -1 Table

History table for person_complaint_xref

feature_code 1 0 9 -1 Table

A list of FEATUREs that can be enabled or disabled on a per agency basis.

comp_mthd_recv_cd_agcy_cd_xref 1 2 8 -1 Table

complaint_method_received_code and AGENCIES that allow for features to be displayed or hidden as required.

configuration_h 0 0 6 -1 Table

History table for configuration table

attractant_code 1 0 9 -1 Table

A human factor contributing to a Human Wildlife Conflict (E.g. RESFRUIT = Residential Fruit/Berries; LVSFEED = Livestock Feed)

linked_complaint_xref 0 2 8 -1 Table

Provides the ability to link one COMPLAINT to another COMPLAINT. The initial use case for this table is to identify duplicate complaints, however additional linkages maybe possible in the future.

gir_complaint_h 0 0 6 -1 Table

History table for gir_complaint table

officer 1 2 10 -1 Table

An officer is a subtype of a person who can be identified through their IDIR.

complaint_update_h 0 0 6 -1 Table

History table for complaint_update table

complaint_h 0 0 6 -1 Table

History table for complaint table

complaint_update 1 1 12 -1 Table

Callers will sometimes call in with a COMPLAINT_UPDATE. This table is used to track items that are considered to be amendments to the complaint information such as additional details, or location information.

office 1 1 7 -1 Table

An office is a physical location that serves as a central organization point for groups of users of the system.

team 1 2 8 -1 Table

Contains a list of TEAM types

complaint 9 5 26 -1 Table

Initial information provided on a potential incident.

hwcr_complaint_h 0 0 6 -1 Table

History table for hwcr_complaint table

staging_status_code 1 0 9 -1 Table
geography_columns 0 1 7 0 View
cos_geo_org_unit_flat_vw 0 0 9 0 View
geometry_columns 0 1 7 0 View