Introduction

The Washington Department of Fish and Wildlife (WDFW) sport_sampling database, described below, is intended as a central repository for recreational fisheries data collected by the WDFW Puget Sound Sampling Program, the Ocean Sampling program, and the Columbia River Sport Sampling Program. The aim is to provide a common framework for the collection, storage, retrieval, and dissemination of fishery survey data collected by these programs.

Rationale

Development of the sport_sampling database was spurred primarily by two factors:

  • The need by harvest managers to consolidate disparate sets of data into one central location.
  • The need to simplify sharing of data with outside entities.

Prior to development of the sport_sampling database, data collected during surveys such as dockside creel surveys, aerial surveys, and test-fishing surveys, were manually entered into multiple stand-alone Access databases and spreadsheets. New versions of these databases and spreadsheets were then re-created each year. To estimate harvest or conduct analyses, considerable effort was required to combine these data. Analysis of trends over multiple years was particularly challenging. The process of merging disparate datasets would often expose errors that could have been avoided by having all data centralized in a properly structured database from the outset. In addition, the advent of mobile data collection technologies provided an opportunity to reduce both the cost and errors that typically result when transcribing data from paper to computer. To automate data collection and data entry a central database was needed.

To facilitate data sharing, the sport_sampling database has been intentionally designed using technology and features that make it easier to integrate data from multiple groups (programs) within the agency. For example, by using universally unique IDs (uuid) for database keys, the need for central coordination in assigning database keys has been eliminated. By hosting the database in the AWS cloud rather than on WDFW servers, there is more flexibility in engineering secure solutions to allow outside entities to easily access and use the data.

Notes on table descriptions

The sections below document table and field definitions for the sport_sampling database. Table descriptions are arranged in descending order starting with top-level header data for individual surveys, and ending with tables that store data for individual fish or individual redds. Each section describes one data table.

Within each section, finer-grained descriptions of columns are provided for each table. For cases where columns link to a set of look-up table values, the relevant look-up tables are displayed directly below the table descriptions.

The final four columns of each data table include:

  • created_datetime: timestamptz(6)
  • created_by: varchar(16)
  • modified_datetime: timestamptz(6)
  • modified_by: varchar(16)

These columns document the time (with timezone) that each record was created or modified, and the individual or application that created or modified the record. For brevity these columns will not be included in the data table descriptions below, but should be assumed to be present.

Look-up tables can be identified by the underscore lut suffix in the table name. For example, data_source_lut. The final two columns of each look-up table include:

  • obsolete_flag: boolean
  • obsolete_datetime timestamptz(6)

These columns document if a field has been retired or deemed obsolete, along with the date when the field was retired. For brevity these columns will not be included in the look-up table descriptions below, but should be assumed to be present.

Look-up tables that are linked to more than one data tables, such as species_lut are only displayed once, normally beneath the first data table in which it is used. Please use the database diagram to identify all occurrances.

Values recorded in each look-up table are displayed directly below the field definitions.

Disclaimer

The sport_sampling database is a work in progress and is subject to change. If you have any questions please contact the data manager Are Strom (Are.Strom@dfw.wa.gov).

Database diagram

Survey

Top level header information for surveys
Column name Data type Nullable? Key Description
survey_id uuid No Primary Universally unique primary key
survey_type_id uuid No Foreign Type of survey (Puget Sound creel, aerial, etc.)
survey_design_type_id uuid Yes Foreign Survey design used to monitor fishery (murthy, baseline, etc.)
sampling_program_id uuid No Foreign Sampling program or unit that conducted survey
location_id uuid No Foreign Location where survey was conducted
data_review_status_id uuid No Foreign Status of data review (final, preliminary, etc.)
survey_completion_status_id uuid No Foreign Was survey completed (partial, completed, etc.)
survey_datetime timestamptz (6) No Date of survey
start_datetime timestamptz (6) Yes Start time of survey
end_datetime timestamptz (6) Yes End time of survey
no_effort_indicator bool No Flag for no survey encounters
comment_text text Yes Additional survey comments

survey_type_lut

Type of survey conducted (Puget Sound creel, aerial, etc.)
Column name Data type Nullable? Key Description
survey_type_id uuid No Primary Universally unique primary key
survey_type_description text No Description of survey type
survey_type_id survey_type_description
b5967130-69ff-49b6-b300-5501e60ec349 Lower Columbia River creel survey
9c716688-c717-405d-93ea-83da15e46417 OSP Sport Interview Survey
c29b9752-6c0e-4e45-9eac-5e450dd10dcc Puget Sound aerial boat count survey
67909653-e362-4007-8ebf-648ca0508625 Puget Sound boat exit count survey
9472b010-806d-4e1f-8b5b-f356e5e0a7c0 Puget Sound boat ramp effort proportion survey
a1c68f6d-dcfe-4b86-a00d-d8fab7057e2e Puget Sound dockside creel survey
bf20fffe-17ad-423e-9802-090bcd1ba3cf Puget Sound shore effort survey
3d07d72c-18a6-47c4-805b-bd756afab088 Puget Sound test fishing survey
1dca9e54-1d3d-45fb-8d5a-de9054eb1503 Puget Sound voluntary trip report

survey_design_type_lut

Survey design used to monitor fishery (murthy, baseline, etc.)
Column name Data type Nullable? Key Description
survey_design_type_id uuid No Primary Universally unique primary key
survey_design_type_description text No Description of survey design categories
survey_design_type_id survey_design_type_description
029a5131-8328-4d42-83e7-0010d1dccec2 Full Murthy Aerial
9f86ef7c-df10-407d-be87-0d25db3887c6 Exploratory Reduced Murthy
c92551ee-ea12-4c67-ad79-17925e4338dd No data
4db3849e-0ac0-47b0-bd79-51c4ee744572 Baseline
10505fdc-6f5d-4bca-b372-6c6c2f3464d1 Full Murthy
16c26d10-0d67-46f7-a529-6c8abf9e2858 Reduced Murthy Aerial
0c464f71-8487-44e2-b60e-737a307e822e Not applicable
299534bb-4a27-49ae-94f2-7b1de26b6ec3 Reduced Murthy

sampling_program_lut

Sampling program or unit that conducted survey
Column name Data type Nullable? Key Description
sampling_program_id uuid No Primary Universally unique primary key
agency_id uuid No Foreign Parent agency of sampling program
sampling_program_code varchar (10) No Sampling program code
sampling_program_name varchar (100) No Sampling program name
sampling_program_id agency_id sampling_program_code sampling_program_name
eb7f8224-7dc7-4f14-8699-7788e6b1be4f bbe4126e-6081-44e3-af1b-012c98290603 CRSSP Columbia River Sport Sampling Program
1908cc7d-ef42-4345-9285-8961096c8613 bbe4126e-6081-44e3-af1b-012c98290603 PSSP Puget Sound Sampling Program
305d3c35-06d8-402e-9dc6-b4a830318869 bbe4126e-6081-44e3-af1b-012c98290603 OSP Ocean Sampling Program

agency_lut

Agency or organization that conducted survey
Column name Data type Nullable? Key Description
agency_id uuid No Primary Universally unique primary key
agency_code varchar (10) No Short code for agency
agency_name varchar (100) No Agency name
agency_id agency_code agency_name
bbe4126e-6081-44e3-af1b-012c98290603 WDFW Washington Department of Fish & Wildlife
9455db7d-f502-4c14-ac6d-5ea75be54245 NWIFC Northwest Indian Fisheries Commission

data_review_status_lut

Status of data review process
Column name Data type Nullable? Key Description
data_review_status_id uuid No Primary Universally unique primary key
data_review_status_description varchar (25) No Description of review categories
data_review_status_id data_review_status_description
48d228a2-6cdc-48e6-95e9-09b5e21d6489 Preliminary
bdefcb1f-80c4-4921-9cf4-66c8dde02d4b Final
acc0bee1-8404-4a52-b052-b176e5863cf8 Unknown
cfc533e5-d5ef-44dc-ae1b-b5a3cfd59016 Not applicable
59cbf9d7-9015-49b6-874d-ea37ef663c56 Reviewed

survey_completion_status_lut

Was the survey completed?
Column name Data type Nullable? Key Description
survey_completion_status_id uuid No Primary Universally unique primary key
completion_status_description varchar (100) No Description of status categories
survey_completion_status_id completion_status_description
149d7253-6a25-4c4c-a7e2-a6cbbbb9850a No survey conducted
71ab2649-62d9-4d48-bad1-0b8a41f352a4 Unknown
9c72357a-8e98-4c98-888c-3412902bf0bf Partial survey
d192b32e-0e4f-4719-9c9c-dec6593b1977 Completed survey

Location

Data associated with locations, includes both point locations and areas
Column name Data type Nullable? Key Description
location_id uuid No Primary Universally unique primary key
location_type_id uuid No Foreign Type of location (dock, pier, etc.)
location_code varchar (10) Yes Short code to identify location
location_name varchar (100) Yes Name of location (place name, etc.)
location_description text Yes Extended description of location
active_indicator bool No Flag indicating if location is still in use
active_datetime timestamptz (6) Yes Date that location became available or active
inactive_datetime timestamptz (6) Yes Date that location became unavailable or inactive
inactive_reason_text text Yes Reason that location became unavailable or inactive
comment_text text Yes Additional comments on location

location_type_lut

Type of location (dock, pier, etc.)
Column name Data type Nullable? Key Description
location_type_id uuid No Primary Universally unique primary key
location_type_description varchar (50) No Description of the location type
location_type_id location_type_description
1ea72e34-6e52-49d2-bd1f-1820827fdb94 Unknown
31e29406-ff29-4d87-8809-2a7dc37f35fd WDFW office
edf5a5b2-ce32-4550-81c0-40fe6505d84d Beach
fcf11156-2692-4581-a27c-4e177bcf9a0d Marina
995d4caf-e4fd-4ed7-85f5-99ddad05fcd5 Pier
017ef540-3f0e-4ac6-b1cd-b6796bdab5f7 Ramp
b31d5220-7f6d-4686-87b6-ba4120327b83 Sling
7d1a431c-a55c-4264-9f3e-ccdaf4c5f7fd Duty station
a06fe17f-b88b-4d91-8a0a-e43ecc9b3e24 Not applicable
120334ed-29fa-415c-a457-eced69eae459 Dock
e75b998d-ef12-42c3-93ff-0a4ad9528c38 DNA sample recovery site
07b8b7b3-10ec-4727-9a74-1ac2dd2b5120 CWT sample recovery site
7cf1d6aa-5a2f-474b-b095-3ce51e6467ec Special Area Fishery
fc2162b9-ae56-457c-a073-6fc53b2abf4a Catch Record Card area
7a72d2ee-8d21-4cb6-ac4b-9cb85cd8c02c Media recording point

Location_boundary

Spatial table holding boundary polygons for location areas
Column name Data type Nullable? Key Description
location_boundary_id uuid No Primary Universally unique primary key
location_id uuid No Foreign Link to location table
gid int4 No Unique integer ID, required to manually edit geometry
geom geometry No Polygons of the location boundaries stored in binary format, EPSG:2927

Location_coordinates

Spatial table holding coordinates (when available) for point_location records
Column name Data type Nullable? Key Description
location_coordinates_id uuid No Primary Universally unique primary key
location_id uuid No Foreign Link to location table
longitude numeric (14, 9) Yes Longitude in decimal degrees, EPSG:4326
latitude numeric (12, 9) Yes Latitude in decimal degrees, EPSG:4326
horizontal_accuracy numeric (6, 2) Yes Estimated accuracy of the point in meters
gid int4 No Unique integer ID, required to manually edit geometry
geom geometry No Points stored in binary format. EPSG:2927

Media_location

Identifies where to retrieve media (photo, video, audio files, etc.) recorded at point locations
Column name Data type Nullable? Key Description
media_location_id uuid No Primary Universally unique primary key
location_id uuid No Foreign Link to location table
media_type_id uuid No Foreign The type of media that was recorded (photo, video, audio, etc.)
media_url text No The secure file or web location where media files are stored

media_type_lut

Type of media that was recorded (photo, video, audio, etc.)
Column name Data type Nullable? Key Description
media_type_id uuid No Primary Universally unique primary key
media_type_code varchar (25) No Code describing the type of media
media_type_description varchar (200) No Description of the media type
media_type_id media_type_code media_type_description
ad673e4b-51d2-4eaf-b3bc-3159a504e6c9 Photo Photo capture
8f89025d-d674-451e-bcb7-ab8567c648e1 Video Video recording
bad7dad2-600e-4586-8428-3fbe26f5b70b Audio Audio recording

Boat_count_omitted

Table to record counts of boats not fishing or boats missed
Column name Data type Nullable? Key Description
boat_count_omitted_id uuid No Primary Universally unique primary key
survey_id uuid No Foreign Link to survey table
boat_not_fishing_count int4 Yes Count of boats not fishing
boat_missed_count int4 Yes Count of boats missed

Boat_trailer

Counts of boat trailers
Column name Data type Nullable? Key Description
boat_trailer_id uuid No Primary Universally unique primary key
survey_id uuid No Foreign Link to survey table
trailer_location_id uuid Yes Foreign Link to location table
start_survey_trailer_count int4 No Time trailer count started
end_survey_trailer_count int4 No Time trailer count ended
comment_text text Yes Additional comments for trailer counts

Survey_mobile_device

Associative table between survey and mobile_device tables
Column name Data type Nullable? Key Description
survey_mobile_device_id uuid No Primary Universally unique primary key
survey_id uuid No Foreign Link to survey table
mobile_device_id uuid No Foreign Link to mobile_device table

Mobile_device

Stores data for tracking mobile devices
Column name Data type Nullable? Key Description
mobile_device_id uuid No Primary Universally unique primary key
mobile_device_type_id uuid No Foreign Type of device (Pad, GPS, etc.)
mobile_equipment_identifier varchar (15) No Typically the MEID number printed on device
mobile_device_name varchar (50) No Name of device
mobile_device_description varchar (150) No Description of device
active_indicator bool No Is the device still active and being used?
inactive_datetime timestamptz (6) Yes Date the device was taken out of service

mobile_device_type_lut

Type of mobile device
Column name Data type Nullable? Key Description
mobile_device_type_id uuid No Primary Universally unique primary key
mobile_device_type_description varchar (50) No Type of device (Pad, GPS, etc.)
mobile_device_type_id mobile_device_type_description
074c894f-c0fa-40dc-b629-1876696fc1db Tablet or Pad
1954b23f-ce78-463a-b539-62e88983c057 External GPS
12ba1d65-3aaa-43a4-b49d-83c8a7896d5d Mobile phone
b4a07fb0-d7a3-46f4-8f0c-c3a6f7f9bb04 Notbook computer

Mobile_survey_form

Table for tracking source of data collected with mobile device
Column name Data type Nullable? Key Description
mobile_survey_form_id uuid No Primary Globally unique primary key
survey_id uuid No Foreign Link to survey table
parent_form_survey_id int4 No Unique ID (integer) generated in parent form
parent_form_survey_guid uuid Yes Foreign GUID generated in parent form
parent_form_name varchar (100) Yes Name of the parent form used to record data
parent_form_id varchar (40) Yes Integer ID of the parent form used to record data

Survey_sampler

Associative table between survey and sampler tables
Column name Data type Nullable? Key Description
survey_sampler_id uuid No Primary Universally unique primary key
survey_id uuid No Foreign Link to survey table
sampler_id uuid Yes Foreign Link to sampler table

Sampler

Base information for samplers
Column name Data type Nullable? Key Description
sampler_id uuid No Primary Universally unique primary key
first_name varchar (50) No First name of sampler
last_name varchar (50) No Last name of sampler
active_indicator bool No Flag to indicate if sampler is still employed

Sampler_schedule

Table to track when and where samplers are scheduled
Column name Data type Nullable? Key Description
sampler_schedule_id uuid No Primary Universally unique primary key
sampler_id uuid No Foreign Link to sampler table
survey_design_type_id uuid No Foreign Type of survey scheduled
location_id uuid No Foreign Location where survey is scheduled to occur
scheduled_start_datetime timestamptz (6) No Time that sampler is scheduled to start
scheduled_end_datetime timestamptz (6) No Time that sampler is scheduled to end

Sampler_status

Current and past status of samplers
Column name Data type Nullable? Key Description
sampler_status_id uuid No Primary Universally unique primary key
sampler_id uuid No Foreign Link to sampler table
sampling_program_id uuid No Foreign Program that that employs the sampler
job_class_id uuid Yes Foreign The job classification of the sampler
sampler_role_id uuid Yes Foreign The primary job assignment of the sampler
sampler_region_id uuid Yes Foreign In which region is the sampler employed (North, South, etc.)
duty_station_location_id uuid Yes Foreign Primary duty station of the sampler
employment_type_id uuid Yes Foreign Employment type of the sampler (temporary, permanent, etc.)
sampler_number varchar (3) Yes Non-permanent number assigned to sampler
supervisor_indicator bool No Flag to indicate if sampler is a supervisor
start_datetime timestamptz (6) Yes Start date of the status entry
end_datetime timestamptz (6) Yes End date of the status entry

job_class_lut

The job classification of the sampler (ST2, ST4, etc.)
Column name Data type Nullable? Key Description
job_class_id uuid No Primary Universally unique primary key
job_class_code varchar (15) No Short code for job class
job_class_name varchar (75) No Full name of job class
job_class_id job_class_code job_class_name
4e022cc2-a5c4-459c-9018-08e183ec3066 FWB4 Fish & Wildlife Biologist 4
f00ebea0-557f-4ed9-aa6c-2c7c80c56bf2 NRS1 Natural Resource Scientist 1
c045207a-d64e-4a95-99b5-333af3fc6cab ST3 Scientific Technician 3
70028d56-8755-4e96-ac64-3ae66834fd7f FWB1 Fish & Wildlife Biologist 1
5534d1f9-861b-44a3-9a3b-4f81515c61d3 ST4 Scientific Technician 4
46096dda-c0da-4888-b9b8-50d082782564 NRS3 Natural Resource Scientist 3
795ff4fa-cddf-4d1d-bc97-75620f79cb41 FWB2 Fish & Wildlife Biologist 2
4af8c707-e7b9-428e-9e75-758cd71e2135 NRS4 Natural Resource Scientist 4
83977f82-3984-4c54-ab63-960e6b25c0cc NRS2 Natural Resource Scientist 2
4eeaaa41-4ef6-4b66-b6bf-dad16712a887 FWB3 Fish & Wildlife Biologist 3
10515828-fe0d-4cd4-8c40-e392fbedf0f9 ST1 Scientific Technician 1
292507fc-84a1-4b51-91c7-f6eb5c2554b8 ST2 Scientific Technician 2

sampler_role_lut

The primary job assignment of the sampler (sport sampling, test fishing, etc.)
Column name Data type Nullable? Key Description
sampler_role_id uuid No Primary Universally unique primary key
sampler_role_description varchar (75) No Description of the role types
sampler_role_id sampler_role_description
b7d3ef66-135c-42a4-a741-20fb1c025a80 All roles
d76e3eff-a2e7-4a3f-a34c-27abdfdf9181 Boat Surveys
682eb246-b50f-4f08-8dc5-55b105555e14 Sport & Test fish sampling
ba1eb9eb-8189-4b4d-aa88-5abc39073e47 Commercial sampling
0fb8a348-58d7-4e0a-bcad-5bb5ce1666ae Data management
5f2aef0e-7b4b-4a7c-8fb6-6d6abb7e4d17 Sport & Charter sampling
3265d1ad-d4d0-4224-a60e-7f5317f34996 Sport sampling
a1e32172-bf23-433c-9a15-84422a13e042 Crab sampling
5ba3ee56-a3f0-45bb-a465-a3629ffc7a40 Sport & Commercial sampling
77bd9842-2e50-4fdd-971e-bcd91df5b203 Test fish sampling

sampler_region_lut

The primary region in which the sampler is employed (North, South, etc.)
Column name Data type Nullable? Key Description
sampler_region_id uuid No Primary Universally unique primary key
sampler_region_name varchar (35) No Region name
sampler_region_id sampler_region_name
ce689c17-2a38-4917-adec-03864ffdf021 Peninsula
e38cd3ea-55ea-418a-8bb2-78bc60494d23 North
830de026-25f2-478d-870c-8271ee9aade3 Headquarters
60a0f186-108b-4419-b797-9b9b72e01a4a Central
c7e59966-4217-4d2e-b85c-ab943b3bc0c3 South

employment_type_lut

Employment type of the sampler (temporary, permanent, etc.)
Column name Data type Nullable? Key Description
employment_type_id uuid No Primary Universally unique primary key
employment_type_description varchar (100) No Description of employment type categories
employment_type_id employment_type_description
1d39d35d-77da-4456-96ff-3539b30fc785 Career Seasonal
26c9c730-8ceb-4f69-acc4-50ed651adfdc Temporary
67311a37-5d34-45cd-a11d-d381c3198e86 Permanent

Survey_event

Interview or encounter level data
Column name Data type Nullable? Key Description
survey_event_id uuid No Primary Universally unique primary key
survey_id uuid No Foreign Link to survey table
catch_area_id uuid No Foreign Link to location table
encounter_location_id uuid No Foreign Link to location table
fishing_method_id uuid Yes Foreign Method used to fish (pier, shore, etc.)
encounter_number int4 Yes Sequence number for interview or encounter
encounter_datetime timestamptz (6) Yes Time that interview or encounter occurred
angler_count int4 Yes Number of anglers counted
sample_rate int4 Yes Proportion of possible interviews that were actually conducted
uncooperative_angler_indicator bool No Flag for uncooperative angler
incomplete_trip_indicator bool No Flag to indicate party interviewed intend to continue fishing
duplicate_encounter_indicator bool No Flag to indicate party was previously encountered
void_encounter_indicator bool No Flag to indicate interview or encounter should be omitted
void_encounter_text text Yes Explanation for why encounter should be omitted
comment_text text Yes Additional interview level comments

fishing_method_lut

Type of fishing method (pier, shore, etc.)
Column name Data type Nullable? Key Description
fishing_method_id uuid No Primary Universally unique primary key
fishing_method_short_description varchar (25) No Short description of fishing method
fishing_method_description text No Extended description of fishing method
fishing_method_id fishing_method_short_description fishing_method_description
eea01d23-ae2c-44c2-8f06-037fbce8b835 Shore diver Refers to fishing by diving from shore. This category is for marine fish only
6b275ca6-f904-4fe5-8a3a-06403ef3035b Charter angler Refers to anglers fishing from larger vessels that are hired to take anglers fishing
50baf4db-7d7b-4eca-b013-10f2fb7517ff Unknown Unknown. This could stem from issues such as an un-cooperative angler or incomplete data entry
ede0c04e-7252-42e9-ab5e-7716acd98c1f Shore angler Refers to anglers fishing from shore
27214a55-a5ee-4b4e-9e46-7def1e445f63 Not applicable Not applicable. For example, a no-effort survey
ce47c643-071e-4024-b236-9256dc2c3853 Kicker angler Refers to anglers fishing from a private vessel > 12 ft in length, with or without engine. Includes sailboats and larger pleasure craft
eb7bc797-6f7b-49cd-88da-9709008738a9 Kicker diver Refers to fishing by diving from a private vessel. This category is for marine fish only
50c13d2c-8e02-4ab6-865b-b028619499d3 Charter diver Refers to fishing by diving from a charter vessel. This category is for marine fish only
a958a1c7-c524-45fb-b472-fc17dbd9ad28 Pier angler Refers to fishing from docks, piers, floats, or any structure built over the water from which anglers may fish

Bird_encounter

Data for bird encounters
Column name Data type Nullable? Key Description
bird_encounter_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
species_id uuid No Foreign Link to species table
bird_count int4 No Number of birds counted
hook_swallowed_indicator bool No Flag to indicate if hook was swallowed
hook_removed_indicator bool No Flag to indicate if hook was removed
live_release_indicator bool No Flag to indicate if bird was alive when released
comment_text text Yes Additional comments on bird encounter

Boat_encounter

Boat encounter data
Column name Data type Nullable? Key Description
boat_encounter_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
ramp_location_id uuid No Foreign Link to location table
boat_count int4 No Number of boats counted
boat_name varchar (75) Yes Name of boat
sample_frame_indicator bool No Flag to indicate if count was within sample frame for survey
comment_text text Yes Additional comments on boat encounter

Dockside_encounter

Additional dockside creel data defined by start and end times
Column name Data type Nullable? Key Description
dockside_encounter_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
fish_start_datetime timestamptz (6) Yes Approximate time that angler started fishing
fish_end_datetime timestamptz (6) Yes Approximate time that angler stopped fishing
trip_start_datetime timestamptz (6) Yes Approximate time that the fishing trip commenced
trip_end_datetime timestamptz (6) Yes Approximate time that the fishing trip ended
angler_no_license_count int4 Yes Flag to indicate under-age angler not requiring a license
fishing_rod_count int4 Yes Count of fishing rods used by anglers onboard
out_of_sample_indicator bool No Flag to indicate this encounter portion should be omitted (i.e. overnight trips)

Fish_derby_encounter

Table linking specific encounters to fish derbies
Column name Data type Nullable? Key Description
fish_derby_encounter_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
fish_derby_id uuid No Foreign Link to table listing Washington State fish derbies

fish_derby_lut

Table listing Washington State fish derbies
Column name Data type Nullable? Key Description
fish_derby_id uuid No Primary Universally unique primary key
fish_derby_name varchar (100) No Name of fish derby
fish_derby_description varchar (150) No Description of fish derby
fish_derby_id fish_derby_name fish_derby_description
0d50b385-ed19-459c-942c-bcb02ef33038 Friday Harbor Classic Salmon Derby Blackmouth derby typically held first part of December, formerly Anacortes Salmon Derby
28c5ee53-0809-4c15-9c66-9229627a33a7 Resurrection Salmon Derby Blackmouth derby typically held first part of December
09f2b6fb-9d68-49a4-8bd8-43d8c91e1bf5 Everett No-Coho Blackmouth Salmon Derby Blackmouth derby typically held first part of November
2cb5e2d7-46cc-4ecd-a393-b29b8c333e93 Roche Harbor Salmon Derby Blackmouth derby typically mid January
8906f492-bf9b-448c-9d5d-eb7fc39be14f Olympic Peninsula Salmon Derby Blackmouth derby typically held mid February
d77ef05e-d456-470d-afb7-2a63ebf878bf Everett Blackmouth Salmon Derby Blackmouth derby typically held mid March
f069f23b-f538-4e73-adcb-5b6404569835 Tengu Salmon Derby 10 week derby held in Elliot Bay.
7e7e0adb-ca93-48b2-8118-49f9d7f56846 Everett Bayside Marine Salmon Derby Blackmouth derby typically held first part of November
a8a3c5ed-532f-49bf-ac37-24d98a735505 Stanwood Eagles Salmon Derby Blackmouth derby typically held mid April
d2154dd4-807a-4f7f-b329-f9d243c0f629 Bellingham Derby Bellingham Derby
244917ee-42fa-4b4c-a198-eacd5535a5f4 South King County PSA Salmon Derby Area 10,11, and 13. Typically held the begining of August
25c67b86-cdb9-4028-9170-6885fb755b1b unknown Anglers sampled for a derby that was unlisted
085b3a14-f77e-4836-b7d6-cb7e3132bd4b Gig Harbor Derby Chinook derby out of Gig Harbor typically held second weekend in August
aa8b191c-2ae4-4f06-a784-f6814f42f7df Hot Plug Derby Blackmouth derby - Late March
37a0bf3c-e225-432e-8910-3fb33eace1b9 Crosby Derby Black Mouth derby - Late April

Target_species

Which species types were the anglers targeting (can be multiple types)
Column name Data type Nullable? Key Description
target_species_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
target_species_type_id uuid No Foreign Link to table listing species types

target_species_type_lut

Which species types were the anglers targeting (can be multiple types)
Column name Data type Nullable? Key Description
target_species_type_id uuid No Primary Universally unique primary key
target_species_type_code varchar (15) No Short code for target species type
target_species_type_description text No Description of target species type
target_species_type_id target_species_type_code target_species_type_description
8aa00283-7918-417c-a44f-0bf149778747 Lingcod Lingcod
021e1d80-53fe-4537-b7db-1a5392f4210d Smelt Smelt
70b1d1cb-1cde-4145-8f01-32339deff00b Cutthroat Cutthroat
4080ae2c-01c3-4315-b380-652167103145 Unknown Unknown. Possibly due to an uncooperative angler
20d5fe4e-ad85-43f9-be53-6cda1e57343c Rockfish Rockfish
b02a809c-1abd-4177-a46b-7fd59e3c64a3 Marine Marine fish (no halibut or lingcod)
110c7653-15da-4202-918a-a8a5749c5d2e Steelhead Steelhead
2cd52c0a-8350-4c18-8238-ac6df032c196 Salmonid Salmonid (includes trout, cutthroat and steelhead)
d90a4b96-20bf-401f-a6d1-e0b04a235b0d Halibut Halibut
d7bc4c4b-2217-4547-b499-e43f8bfd19c1 Shad Shad
70abb636-3eeb-474a-a44c-fe3657bcfa93 Sturgeon Sturgeon
3f829b74-c994-4506-bc40-fe4e32f4accc Not applicable Not applicable
d8f8c122-ac66-4665-9da9-ff159a7bc756 Walleye Walleye
db45a8e2-ea38-40c5-b7ce-13f4100872d7 Dungeness Crab Dungeness Crab
0744ba4e-5558-4924-9fce-148296c4bda9 Chinook Chinook salmon
a41cf358-5cad-4846-b4ed-7cafbfaf4757 Coho Coho salmon

Fish_encounter

Data for fish encounters, includes data for one or multiple fish
Column name Data type Nullable? Key Description
fish_encounter_id uuid No Primary Universally unique primary key
survey_event_id uuid No Foreign Link to survey_event table
species_id uuid No Foreign Link to species table
adipose_clip_status_id uuid No Foreign Status of adipose fins (clipped, unknown, etc)
encounter_gear_type_id uuid No Foreign Type of fishing gear used (jig, troll, etc.)
encounter_depth_range_id uuid No Foreign Depth range where fish were encountered
fish_location_id uuid Yes Foreign Link to location table
cwt_detection_status_id uuid Yes Foreign Were coded-wire tags detected?
tag_disposition_id uuid No Foreign Status, or disposition of fish tags
legal_size_status_id uuid No Foreign Status of fish in terms of legal size
catch_result_type_id uuid No Foreign End result of fish encounter (kept, released, etc.)
release_health_type_id uuid No Foreign Condition of fish when released
descending_device_status_id uuid Yes Foreign Was a descending device used?
fish_count int4 No Number of fish counted
comment_text text Yes Additional comments on fish encounter

adipose_clip_status_lut

Status of adipose fins (clipped, unknown, etc)
Column name Data type Nullable? Key Description
adipose_clip_status_id uuid No Primary Universally unique primary key
adipose_clip_status_code varchar (2) No Short code for clip status
adipose_clip_status_description varchar (75) No Description clip status
adipose_clip_status_id adipose_clip_status_code adipose_clip_status_description
33b78489-7ad3-4482-9455-3988e05bfb28 NC Adipose fin not checked
f71eb6d8-cfb8-4873-a07e-44f6a0eb7c93 UD Adipose fin checked but undetermined, e.g., too deteriorated
66d9a635-a127-4d12-8998-6e86dd93afa6 UM Adipose fin not clipped
c989e267-c2cb-4d0a-842c-725f4257ace1 UN Undeterminable. Unable to check
92d81f24-b5cb-49fd-87d8-b3422ce717fb AD Adipose fin clipped
1d61246c-003b-49e9-b2a3-cffdddb3905c na Not applicable

encounter_gear_type_lut

Type of fishing gear used (jig, troll, etc.)
Column name Data type Nullable? Key Description
encounter_gear_type_id uuid No Primary Universally unique primary key
encounter_gear_type_code varchar (2) No Short code for gear type
gear_type_description varchar (50) No Description of gear type
encounter_gear_type_id encounter_gear_type_code gear_type_description
a0656bdb-09fa-481f-a43a-181e6504d47c BA Bottomfish artificial
9a3840c3-0e94-4c15-b22b-404d1f803708 WB Weight and bait
b896b52b-7a27-493e-800f-5f0badbdbdca UK Unknown
b6f316c7-1a65-4ff6-8997-731d55678866 OT Other
e33acd23-43bd-467c-b3f2-8ae41ceb6a9b BB Bottomfish bait
f5216068-bfc0-4508-8b0c-ccb7f2c3e7a2 DR Downrigger trolling
c8e5dac6-1036-4412-8dbf-d00c9e0f68ad DV Diver trolling
f4310dd8-f28d-4f23-83f2-da48f3d890bd SF Spear fishing
c9cdb1a6-83fd-4392-84ca-f195543e16bf JG Jigging

encounter_depth_range_lut

Depth range categories for fish encounters
Column name Data type Nullable? Key Description
encounter_depth_range_id uuid No Primary Universally unique primary key
encounter_depth_range_code varchar (1) No Short code for depth range
encounter_depth_range varchar (20) No Depth ranges in feet
depth_range_description varchar (50) No Description of depth ranges
encounter_depth_range_id encounter_depth_range_code encounter_depth_range depth_range_description
e13d3449-87fd-4ea5-97b9-004e95da34f5 A 0-30 0-30 ft
52d043d4-241b-4045-8fb9-37e7f883514f F > 180 Greater than 180
aa3e11ec-7200-458b-8141-4793605af7b5 Y No data Marine fish, but no depth range recorded
cf60f146-0d1f-47d1-b6cf-58d5ab3161cc Z Not Applicable Not Applicable, not marine fish
cbe88181-b21a-4a5d-adf4-6cc34e4f3b57 E 121-180 121-180 ft
e07f01de-5efd-43f5-8fb3-77331d286637 C 61-90 61-90 ft
01e2326c-6bfc-4d13-8222-8d6aa171a6f2 D 91-120 91-120 ft
8a73f30b-39f7-4a57-8db8-b9a020756701 B 31-60 31-60 ft

cwt_detection_status_lut

Result of coded-wire tag detection efforts
Column name Data type Nullable? Key Description
cwt_detection_status_id uuid No Primary Universally unique primary key
detection_status_description varchar (75) No Description of detection status
cwt_detection_status_id detection_status_description
efe698a8-98dd-45df-ba5b-0d448c88121d Coded-wire tag undetermined, e.g., no head
6242055f-b2bc-44c1-b0d4-3ce24be44bbe Coded-wire tag detected
ba4209af-3839-46a7-bd5d-57bc8516f7af Coded-wire tag not detected
bd7c5765-2ca3-4ab4-80bc-ce1a61ad8115 Not applicable
ea27011e-09cd-46d3-a15d-e95a0cdf05c4 Did not check for coded-wire tag

tag_disposition_lut

Status, or disposition of fish tags
Column name Data type Nullable? Key Description
tag_disposition_id uuid No Primary Universally unique primary key
tag_disposition_description text No Description of disposition categories
tag_disposition_id tag_disposition_description
c3cccb4e-16bd-4b0a-b42c-0b3de28f4706 Not applicable
4ce609a7-5b4e-4b6d-ab77-107271a4d93a Not sampled
80d00b19-daa8-41cb-b63f-3dbcbb00b8b9 Tag removed from fish
966f13c6-7db6-4782-8c54-7230514a1e97 No tags found
c9f60367-9cdb-42ca-a26b-95e831d65897 Tag left on fish

catch_result_type_lut

End result of fish encounter (kept, released, etc.)
Column name Data type Nullable? Key Description
catch_result_type_id uuid No Primary Universally unique primary key
catch_result_type_code varchar (2) No Short code for catch result categories
catch_result_type_description varchar (75) No Description of catch result categories
catch_result_type_id catch_result_type_code catch_result_type_description
468a853c-668a-4978-9fa2-ba9dee873247 K Fish was kept
43a98b6b-e6e7-44a5-8710-c1860593e64e DO Fish dropped off
50642a06-18bf-47de-ab61-cabf925db4aa R Fish was released
3d18830c-8b33-40f7-b2a5-edb1359b3867 na Not applicable

release_health_type_lut

Condition of fish when released
Column name Data type Nullable? Key Description
release_health_type_id uuid No Primary Universally unique primary key
health_type_short_description varchar (25) No Short description of health type categories
health_type_description varchar (200) No Extended description of health type categories
release_health_type_id health_type_short_description health_type_description
894a7b19-f790-4c58-b1ce-31d431ed78ac Dead or near death Dead or near death. Includes cases such as where the fish was eaten by a seal upon release.
3517da9a-45e4-433a-8ec1-64a818ba48cb No data Unknown release condition. No data recorded.
80231ebb-44f4-485f-b0d2-ccf6a1638965 Unhealthy Unhealthy. Significant bleeding either from the mouth or gills, significant scale loss.
5856a58a-d2e7-4aa8-8f28-d39efb88048f Moderately healthy Fish is expected to survive upon release. May include some visible bleeding and marginal scale loss. Fish may becoming lethargic.
dad4cec2-57b0-4cd9-9fd5-fceee78eb459 Healthy Fish is expected to survive upon release. No visible bleeding, minimal scale loss, and vigorous movement.

descending_device_status_lut

Was a descending device used?
Column name Data type Nullable? Key Description
descending_device_status_id uuid No Primary Universally unique primary key
descending_device_status varchar (50) No Status categories (yes, no, not applicable)
descending_device_status_id descending_device_status
825e56cb-3ef6-4346-a272-448a507cb69f Not applicable
75a81df6-66b2-480a-a428-fae82d619f31 Used descending device
77db0b6c-8a85-4ed4-91bb-6ddaf1171966 Did not use descending device

Individual_fish

Data for individual fish
Column name Data type Nullable? Key Description
individual_fish_id uuid No Primary Universally unique primary key
fish_encounter_id uuid No Foreign Link to fish_encounter table
sex_id uuid No Foreign Fish sex categories
maturity_id uuid No Foreign Fish maturity categories
cwt_head_taken_status_id uuid No Foreign Was the fish head taken for coded_wire tag processing
fish_color_id uuid No Foreign Color of fish when captured
fish_processing_status_id uuid No Foreign Was the fish gutted, intact, etc?
sample_category_id uuid No Foreign Was the fish a member of the bio-sample
fish_sample_number varchar (40) Yes Sample number of individual fish
weight_measurement_kilogram numeric (6, 3) Yes Weight of fish in kilograms
scale_sample_card_number varchar (40) Yes Scale card number
scale_sample_position_number varchar (40) Yes Position number of scale on scale card
cwt_snout_sample_number varchar (40) Yes Bag label number for snouts taken for coded-wire tag analysis
genetic_sample_number varchar (40) Yes Genetic sample number
otolith_sample_number varchar (40) Yes Otolith sample number
total_age int4 Yes Total age of fish as determined by age analysis
fresh_water_age int4 Yes Number of years fish spent in fresh water as determined by age analysis
age_code varchar (15) Yes Coded notation for age
comment_text text Yes Additional comments on individual_fish

sex_lut

Sex of fish (male, female, unknown, etc)
Column name Data type Nullable? Key Description
sex_id uuid No Primary Universally unique primary key
sex_code varchar (2) No Short code for sex categories
sex_description varchar (25) No Description of sex categories
sex_id sex_code sex_description
1511973c-cbe1-4101-9481-458130041ee7 F Female
ccdde151-4828-4597-8117-4635d8d47a71 M Male
b97eaba9-4205-431b-ab09-a34636557666 na Not applicable
c0f86c86-dc49-406b-805d-c21a6756de91 uk Unknown

maturity_lut

Maturity status of fish (adult, subadult, etc.)
Column name Data type Nullable? Key Description
maturity_id uuid No Primary Universally unique primary key
maturity_code varchar (35) No Short code for maturity categories
maturity_description text No Description of maturity categories
maturity_id maturity_code maturity_description
00d97caa-ce0d-4283-b238-1b6e6caaba46 Kelt Salmon that have spawned, typically thin and in poor condition
68347504-ee22-4632-9856-a4f4366b2bd8 Adult For salmon, fish that have returned from the ocean after an ocean residence typical for the species.
0b0d12cf-ed27-48fb-ade2-b408067520e1 Subadult For salmon, fish of either sex that have returned from the ocean earlier than normal. Typically after one year.
060732b9-230c-48f3-8264-bcb3c078a6e7 Not applicable Does not apply, e.g., no fish encountered
66cc15ba-1307-4c5d-a7e5-854861d2791a Unknown Unknown, e.g., no data recorded

cwt_head_taken_status_lut

Was the fish head taken for coded_wire tag processing
Column name Data type Nullable? Key Description
cwt_head_taken_status_id uuid No Primary Universally unique primary key
cwt_head_taken_status_code varchar (2) No Short code for status categories
cwt_head_taken_status_description varchar (50) No Description of status categories
cwt_head_taken_status_id cwt_head_taken_status_code cwt_head_taken_status_description
4c5db133-3501-45e4-8f42-3991fcd1adc7 N No
9def4ec6-33ba-4f6a-8eee-8c8682bb92c7 P Pending
467c8815-b3eb-4fa9-b858-c7076098d1d6 Y Yes
8f956ae4-ef6b-4a38-9bca-d8255948622a na Not applicable

fish_color_lut

Fish color categories (bright, tule, etc.)
Column name Data type Nullable? Key Description
fish_color_id uuid No Primary Universally unique primary key
fish_color_description varchar (150) No Description of color categories
fish_color_id fish_color_description
d47d2717-1564-468f-b6a8-0ab02fb2711d Bright, or white
8783c550-e08d-43b2-8188-64f0fe567308 Tule
d2bea6f6-3e25-440c-91e3-8fe50823ec80 Black
c991d4d3-2586-45c3-880d-d04d54a2695e Not applicable
8a719b86-f3d1-4fe7-b6e2-d2d9e5c846bc Unknown
be14d2e4-069c-4ee1-920f-eb17f95c6184 SAB

fish_processing_status_lut

Was the fish gutted, intact, etc?
Column name Data type Nullable? Key Description
fish_processing_status_id uuid No Primary Universally unique primary key
processing_status_description varchar (150) No Description of processing status categories
fish_processing_status_id processing_status_description
f8b4bc67-9217-494a-9c14-02f97012ef77 Fish cleaned, or gutted
4e8310c5-247c-4613-86b4-6714610e3b1c Fish intact, not cleaned
60876b70-45a7-4887-a54d-6f2b939408e3 Not applicable
b6f02778-98c7-407d-8aa8-add6235c2216 Unknown

sample_category_lut

Was the fish a member of the bio-sample
Column name Data type Nullable? Key Description
sample_category_id uuid No Primary Universally unique primary key
sample_category_description varchar (150) No Description of bio-sample membership categories
sample_category_id sample_category_description
ef258c59-4871-4b2b-b4d7-333193b18244 In bio-sample
b66e2a61-8f0a-4c23-ae42-522f1ed9cd20 Unknown
f21773f3-9b24-4f76-9219-bd875b89e73d Not applicable
9dedde8e-b9af-4789-8a8f-cbf015058203 Out of bio-sample

Fish_length_measurement

Individual fish length measurements (can be multiple per fish)
Column name Data type Nullable? Key Description
fish_length_measurement_id uuid No Primary Universally unique primary key
individual_fish_id uuid No Foreign Link to individual_fish table
fish_length_measurement_type_id uuid Yes Foreign Type of length measurement (FL, POH, etc.)
length_measurement_centimeter numeric (6, 2) Yes Length in centimeter

fish_length_measurement_type_lut

Types of length measurements
Column name Data type Nullable? Key Description
fish_length_measurement_type_id uuid No Primary Universally unique primary key
length_type_code varchar (5) No Short code for length measurement type
length_type_description varchar (50) No Description of length measurement type
fish_length_measurement_type_id length_type_code length_type_description
6009bdb8-a704-4a60-a487-5f6fc3ad2edd MP Mid-eye to posterior most scale length
f68b6789-96de-4ac7-ae93-6eb7a4eef36b NS Not specified
740dbd1a-93fe-4355-8e78-722afba53b9f FL Fork length
63e183a7-0f29-4b59-97f5-9a88038dc71e TL Total length
5e355040-adf1-4dce-a965-b79db4e5ec03 POH Post-orbital to hypural plate length
acc10539-5c97-464e-8f57-f4b92e1e7b2d MH Mid-eye to hypural length
61abd481-c150-43d6-bbd3-51bde528e393 UE Undefined estimate
be7c369d-6eac-4840-b177-5ba2d3da27a4 na Not applicable
cc141458-fb71-47cb-aa2b-a1ed1a7c7477 SL Standard length

Fish_sample_inventory

Data to track biological samples
Column name Data type Nullable? Key Description
fish_sample_inventory_id uuid No Primary Universally unique primary key
individual_fish_id uuid No Foreign Link to individual_fish table
sample_type_id uuid No Foreign Type of bio-sample (otolith, scale, etc.)
sample_sent_destination_id uuid Yes Foreign Link to location table
sample_packaged_indicator int4 No Was the sample packaged
sample_held_indicator int4 No Is the sample been held locally
sample_inventoried_datetime timestamptz (6) Yes Date that the sample was inventoried
sample_sent_datetime timestamptz (6) Yes Date that the sample was shipped
comment_text text Yes Additional comments on sample inventory

sample_type_lut

Types of samples to track
Column name Data type Nullable? Key Description
sample_type_id uuid No Primary Universally unique primary key
sample_type_description varchar (150) No Description of sample_type type (otolith, scale, etc.)
sample_type_id sample_type_description
eadd2842-40e4-429e-abcf-0d0d2c088130 Scale sample
a0c3b322-c369-4cae-b9af-d1448e64d67e Otolith sample
6a84b4a5-5061-4e41-868d-da6500645948 CWT Snout sample
d4b37ae9-3267-4fdd-9626-e5da8a128d72 Genetic sample

Individual_fish_mark

Data for marks and tags observed on fish
Column name Data type Nullable? Key Description
individual_fish_mark_id uuid No Primary Universally unique primary key
individual_fish_id uuid No Foreign Link to individual_fish table
mark_type_id uuid No Foreign Type of mark or tag (fin clip, floy tag, etc)
mark_orientation_id uuid No Foreign Orientation of mark or tag (right, left, etc.)
mark_placement_id uuid No Foreign Placement of mark or tag (adipose fin, snout, etc.)
tag_number varchar (40) Yes Tag number
comment_text text Yes

mark_type_lut

Type of mark or tag (fin clip, floy tag, etc)
Column name Data type Nullable? Key Description
mark_type_id uuid No Primary Universally unique primary key
mark_type_category_id uuid No Foreign Mark or tag?
mark_type_code varchar (4) No Short code for mark or tag
mark_type_description varchar (75) No Description of mark or tag
mark_type_id mark_type_code mark_type_description
d5f00d16-b331-4242-8606-73926f04f06d Acou Acoustic Tag
a9b0ec64-861b-4e8a-af84-0c58327e36f4 Carc Carcass
415c80e4-b3f8-45c5-a70f-bc4057de979d Clip Clip
1eec75f2-a61b-4997-9b4f-e032f4d70a2e CWT Coded Wire Tag
195780de-4c85-4894-9852-0fa871de3820 Dart Dart
2aec69e7-40c4-4304-9340-d9019d415ea9 Dye Dye
36c090b2-c164-4ea4-a40e-3c0b5a1b7633 Floy Floy
17a65bb8-3612-4cae-8996-c911e0e90f01 Frze Freeze Brand
450bae49-1728-40b6-bffa-6fa38b694564 Jaw Jaw
e868b795-f692-4046-a6e9-3fa77ce10cfc Oto Marked otolith
78aa8d53-d856-493b-a3f9-e738e86a4f79 MJct MicroJect
89111757-5bb5-469d-9ea0-e9596064b5dc Oprc Opercle Tag
e4f9403b-cf22-4ccf-a9a7-1fbd64b2b48c PIT Passive Integrated Transponder
6e750d0c-661a-418f-98d5-35926637c975 Pnch Punch
607a9bfc-7042-4a72-b282-5cb3cb2b365f Radi Radio
fdd41f55-c703-4eb2-8bbc-695fce41195f Sonr Sonar
78d11f75-622a-44df-8e13-4fbb32daa9c7 Spag Spaghetti
3a91010d-842d-4eb1-96b6-4ee735b36949 VIE Visual Implant Elastomer

mark_type_category_lut

Was it a mark or a tag?
Column name Data type Nullable? Key Description
mark_type_category_id uuid No Primary Universally unique primary key
mark_type_category_name varchar (25) No Mark or tag?
mark_type_category_id mark_type_category_name
eefb6282-c462-4cd0-b996-8c65d53423b3 Mark
5840c8de-dcb7-4da7-bc09-2b47eca4240c Tag

mark_orientation_lut

Orientation of mark or tag (right, left, etc.)
Column name Data type Nullable? Key Description
mark_orientation_id uuid No Primary Universally unique primary key
mark_orientation_code varchar (4) No Short code for mark orientation categories
mark_orientation_description varchar (50) No Description of mark orientation categories
mark_orientation_id mark_orientation_code mark_orientation_description
fcc3fe36-cfa6-481a-8790-879e6616fb09 Not applicable
97045e1b-d659-4d46-97bf-f2c2910ba3bf Botm Bottom
bbffd2d0-ce31-4301-839d-e2719101a532 Left Left
09992db3-da10-4598-8a49-d188b84b9711 Rght Right
9fb05dac-7073-4c4d-8bb8-525c8efdd51f Uppr Upper

mark_placement_lut

Placement of mark or tag (adipose fin, snout, etc.)
Column name Data type Nullable? Key Description
mark_placement_id uuid No Primary Universally unique primary key
mark_placement_code varchar (10) No Short code for mark placement categories
mark_placement_description varchar (50) No Description of mark placement categories
mark_placement_id mark_placement_code mark_placement_description
f0a2cdcf-012a-4468-b223-c7cd0eb2ee58 Not applicable
d517f1e4-6f8e-4d97-b921-24a277f0b694 AdipFin Adipose fin
5213c60c-768c-4c47-8f0e-223f13e9c918 AnalFin Anal fin
98f89e8f-f3c6-4be6-86fa-1ad523268a83 CaudFin Caudal fin
cfd54be2-024d-4329-a42d-68f1d820e7ae DorsFin Dorsal fin
6a1546cb-07d4-4514-8a9a-183d9480dcd3 Maxi Maxillary
d35aacb1-20b3-41d7-b24e-00d4941b68a8 Oper Opercle
0a0e4adb-9a7d-4fb1-ba3b-5ce3bf0af646 PectFin Pectoral fin
3e4234a3-dde5-41be-b39d-dd8a96c15d62 Snout Snout
e5c5309a-f750-4391-9192-415004d09900 VentFin Ventral fin

Fishery

Data to track fisheries (mark_selective, halibut, etc)
Column name Data type Nullable? Key Description
fishery_id uuid No Primary Universally unique primary key
catch_area_id uuid No Foreign Link to catch area look-up table
season_id uuid No Foreign Type of fishing season (winter, summer, etc.)
fishery_code varchar (8) No Short code for fishery
comment_text text Yes Additional comments for fishery

season_lut

Categories of fishing season types (winter, summer, etc.)
Column name Data type Nullable? Key Description
season_id uuid No Primary Universally unique primary key
season_description varchar (25) No Description of season categories
season_id season_description
81e9a42a-4fd3-4578-9cdf-3880974163d7 Winter (Oct1-Apr30)
8e02fcaf-2c56-4b24-8767-567543317ffb Summer (May1-Sept30)

Fishery_model

Model output from FRAM (fishery regulation and assessment model)
Column name Data type Nullable? Key Description
fishery_model_id uuid No Primary Universally unique primary key
fishery_id uuid No Foreign Link to fishery table
model_estimate_type_id uuid No Foreign Model output categories (kept marked, etc.)
model_estimate_quantity int4 No Estimated totals for each modeled category

model_estimate_type_lut

FRAM model output categories (kept marked, etc.)
Column name Data type Nullable? Key Description
model_estimate_type_id uuid No Primary Universally unique primary key
estimate_type_code varchar (4) No Short code for model output categories
estimate_type_description varchar (100) No Description of model output categories
model_estimate_type_id estimate_type_code estimate_type_description
d2f195c3-8692-44c6-b6e1-2868d3ab5d78 esm Encountered sub-legal marked
e6d7cfea-6eb6-4150-a226-2a56623b55be km kept marked
91ba7093-6cd7-4221-a6cf-369135abd512 rmsm Release mortality sub-legal marked
cb07f043-a42a-46ef-8a4a-397e87c656f2 elu Encountered legal unmarked
2919c39a-db02-493c-b38c-5dbdb30fd683 elm Encountered legal marked
db68cd09-c88a-4ac1-beaf-7af6b13b1c87 rmlu Release mortality legal unmarked
505183c3-2d44-411e-9f45-84797799e59b rmsu Release mortality sub-legal unmarked
44f38168-00ba-4244-aaae-dad209b8a282 ku Kept unmarked
5b8ebb51-7f08-4790-b1dc-f677e23b556a esu Encountered sub-legal unmarked
014b2026-451a-4db8-995c-fb363e81f885 rmlm Release mortality legal marked

Fishery_date

Data to track start and end dates of fisheries
Column name Data type Nullable? Key Description
fishery_date_id uuid No Primary Universally unique primary key
fishery_id uuid No Foreign Link to fishery table
start_datetime timestamptz (6) No Start date of fishery
end_datetime timestamptz (6) No End date of fishery
fishery_period_number int4 No Identifier for discrete periods within fisheries

Fishery_date_survey_type

Associative table between fishery and survey_type tables
Column name Data type Nullable? Key Description
fishery_date_survey_type_id uuid No Primary Universally unique primary key
fishery_date_id uuid No Foreign Link to fishery_date table
survey_type_id uuid No Foreign Link to survey_type look-up table

Fishery_regulation

Data to track fishery regulation type (mark-selective, non-retention, etc)
Column name Data type Nullable? Key Description
fishery_regulation_id uuid No Primary Universally unique primary key
fishery_date_id uuid No Foreign Link to fishery_date table
species_id uuid No Foreign Link to species table
regulation_type_id uuid No Foreign Regulation type (mark-selective, non-retention, etc)
survey_design_type_id uuid No Foreign Survey design used to monitor fishery (murthy, baseline, etc.)

regulation_type_lut

Regulation type categories (mark-selective, non-retention, etc)
Column name Data type Nullable? Key Description
regulation_type_id uuid No Primary Universally unique primary key
regulation_type_code varchar (3) No Short code for regulation type categories
regulation_type_description varchar (100) No Description of regulation type categories
regulation_type_id regulation_type_code regulation_type_description
f37ece9d-f711-4b32-99df-122b4265ef46 NS Non Selective
c1834c4e-95c6-48eb-acb7-bfd134ff0531 NR Non Retention
0c5e703b-a4e0-4911-ad46-fbac224222c1 MSF Mark Selective