NRRSS Database Schema

Schema (for MySql Database)

TABLE nrrss_node_table

node_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

node_abbrev CHAR(2) NOT NULL

node_name VARCHAR(80)

TABLE nrrss_user_table

user_number INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL

logon VARCHAR(120) NOT NULL

last_name VARCHAR(30) NOT NULL

first_name VARCHAR(20)

middle_init CHAR(1)

user_manager_ind CHAR(1)

node_number INTEGER UNSIGNED REFERENCES nrrss_node_table

user_status CHAR(1)

TABLE p_table

user_number INTEGER UNSIGNED REFERENCES nrrss_user_table

password VARCHAR(8) NOT NULL

TABLE proj_intent_table

proj_intent_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

proj_intent_category VARCHAR(40) NOT NULL

proj_intent_text TEXT

TABLE state_territory_table

state_terr_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

state_terr_abbrev CHAR(2) NOT NULL

state_terr_name VARCHAR(30)

TABLE country_table

country_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

country_abbrev CHAR(2) NOT NULL

country_name VARCHAR(80)

TABLE flow_table

flow_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

flow_category VARCHAR(50) NOT NULL

TABLE miscellaneous_activities_table

misc_activities_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

misc_activities_category VARCHAR(40) NOT NULL

TABLE prev_rem_inputs_table

prev_rem_inputs_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

prev_rem_inputs_category VARCHAR(60) NOT NULL

TABLE shape_table

shape_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

shape_category VARCHAR(35) NOT NULL

TABLE structure_table

structure_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

structure_category VARCHAR(40) NOT NULL

TABLE vegetation_table

vegetation_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

vegetation_category VARCHAR(45) NOT NULL

TABLE proj_year_cat_table

proj_year_cat_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

proj_range_of_years VARCHAR(22)

TABLE geo_indicator_table

geo_indicator CHAR(1) PRIMARY KEY NOT NULL

geo_system_name VARCHAR(80) NOT NULL

TABLE nrrss_record_table

nrrss_number INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL

nrrss_archived CHAR(1)

node_number INTEGER UNSIGNED REFERENCES nrrss_node_table

reg_vs_nat CHAR(1)

source_data_type_number INTEGER UNSIGNED REFERENCES source_data_type_table

geo_indicator CHAR(1) REFERENCES geo_indicator_table

created_by_user_num INTEGER UNSIGNED

nrrss_created_at DATETIME

nrrss_manually_updated CHAR(1)

proj_intent_other_text VARCHAR(100)

waterway_other_text VARCHAR(30)

source_data_type_text VARCHAR(100)

numeration_within_host_doc VARCHAR(100)

location_with_host_doc VARCHAR(255)

TABLE state_terr_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

state_terr_number INTEGER UNSIGNED REFERENCES state_territory_table

TABLE activities_other_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

activities_other_text1 TEXT

activities_other_text2 TEXT

activities_other_text3 TEXT

activities_other_text4 TEXT

activities_other_text5 TEXT

activities_other_text6 TEXT

activities_other_text7 TEXT

activities_other_text8 TEXT

activities_other_text9 TEXT

activities_other_text10 TEXT

TABLE source_data_type_table

source_data_type_number INTEGER UNSIGNED PRIMARY KEY

source_material VARCHAR(100)

TABLE record_last_updated_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

updated_by_user_num INTEGER UNSIGNED

nrrss_last_modified_at DATETIME

TABLE proj_intent_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

proj_intent_number INTEGER UNSIGNED REFERENCES proj_intent_table

TABLE misc_act_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

misc_activities_number INTEGER UNSIGNED REFERENCES miscellaneous_activities_table

TABLE flow_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

flow_number INTEGER UNSIGNED REFERENCES flow_table

TABLE prev_rem_inp_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

prev_rem_inputs_number INTEGER UNSIGNED REFERENCES prev_rem_inputs_table

TABLE shape_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

shape_number INTEGER UNSIGNED REFERENCES shape_table

TABLE structure_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

structure_number INTEGER UNSIGNED REFERENCES structure_table

TABLE vegetation_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

vegetation_number INTEGER UNSIGNED REFERENCES vegetation_table

TABLE geographic_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

lat_deg CHAR(2)

lat_min CHAR(2)

lat_sec CHAR(2)

lat_direction CHAR(1)

lon_deg CHAR(3)

lon_min CHAR(2)

lon_sec CHAR(2)

lon_direction CHAR(1)

TABLE utm_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

northings CHAR(10)

eastings CHAR(10)

zone CHAR(2)

TABLE proj_ident_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

proj_name VARCHAR(120)

stream_name VARCHAR(100)

tributary_of VARCHAR(100)

county VARCHAR(80)

nearest_town VARCHAR(80)

country_number INTEGER UNSIGNED REFERENCES country_table

basin_mentioned VARCHAR(100)

proj_year_completed YEAR

proj_year_implemented YEAR

proj_year_permitted YEAR

proj_year_cat_number INTEGER UNSIGNED REFERENCES proj_year_cat_table

proj_website VARCHAR(255)

proj_ongoing CHAR(1)

TABLE monitoring_focus_table

monitoring_focus_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

monitoring_focus_type VARCHAR(40)

TABLE monitor_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

monitoring_focus_number INTEGER UNSIGNED REFERENCES monitoring_focus_table

TABLE dollar_table

dollar_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

dollar_abbrev CHAR(2) NOT NULL

dollar_name VARCHAR(25)

TABLE area_unit_table

area_unit_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

area_unit_abbrev CHAR(2) NOT NULL

area_unit_name VARCHAR(20)

TABLE linear_unit_table

linear_unit_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

linear_unit_abbrev CHAR(2) NOT NULL

linear_unit_name VARCHAR(20)

TABLE proj_activities_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

proj_size DOUBLE(30,4) UNSIGNED

area_unit_number INTEGER UNSIGNED REFERENCES area_unit_table

proj_cost DECIMAL(24,2) UNSIGNED

dollar_number INTEGER UNSIGNED REFERENCES dollar_table

proj_cost_ind CHAR(1)

monitoring_ind CHAR(1)

monitoring_focus_text VARCHAR(40)

funder_indicator CHAR(1)

funder_info VARCHAR(255)

implement_indicator CHAR(1)

implement_info VARCHAR(255)

mitigation_indicator CHAR(1)

proj_length DOUBLE(30,4) UNSIGNED

linear_unit_number INTEGER UNSIGNED REFERENCES linear_unit_table

proj_size_ind CHAR(1)

proj_length_ind CHAR(1)

TABLE waterway_table

water_number INTEGER UNSIGNED PRIMARY KEY NOT NULL

waterway_type VARCHAR(30) NOT NULL

TABLE proj_contact_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

proj_cont_last_name VARCHAR(30) NOT NULL

proj_cont_affiliation VARCHAR(255)

proj_cont_first_name VARCHAR(20)

proj_cont_phone_number VARCHAR(20)

proj_cont_email VARCHAR(100)

TABLE source_data_cit_table

source_data_cit_number INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL

primary_responsibility VARCHAR(255)

title_host_document VARCHAR(255)

type_of_medium VARCHAR(80)

subordinate_responsibility VARCHAR(255)

edition VARCHAR(255)

place_of_publication VARCHAR(255)

publisher VARCHAR(255)

date_of_publication DATE

date_of_update_revision DATE

date_of_citation DATE

chapter VARCHAR(255)

title_of_part VARCHAR(255)

notes VARCHAR(255)

availability VARCHAR(80)

sdc_created_at DATETIME

sdc_last_modified_at DATETIME

sdc_created_by_user_num INTEGER UNSIGNED

sdc_updated_by_user_num INTEGER UNSIGNED

sdc_manually_updated CHAR(1)

TABLE water_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

water_number INTEGER UNSIGNED REFERENCES waterway_table

TABLE source_data_cit_num_nrrss_num_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

source_data_cit_number INTEGER UNSIGNED REFERENCES source_data_cit_table

TABLE user_manager_table

user_number INTEGER UNSIGNED REFERENCES nrrss_user_table

node_number INTEGER UNSIGNED REFERENCES nrrss_node_table

user_indicator CHAR(1)

TABLE new_user_table

new_user_number INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL

new_logon VARCHAR(120) NOT NULL

new_last_name VARCHAR(30) NOT NULL

new_first_name VARCHAR(20)

new_middle_init CHAR(1)

new_user_manager_ind CHAR(1)

node_number INTEGER UNSIGNED REFERENCES nrrss_node_table

new_user_datestamp DATETIME

TABLE phone_conversations_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

phone_conv_last_name VARCHAR(30) NOT NULL

phone_conv_first_name VARCHAR(20)

phone_conv_job_desc VARCHAR(120)

phone_conv_affiliation VARCHAR(120)

phone_conv_phone_number VARCHAR(20)

phone_conv_date_of_call DATE

phone_conv_email VARCHAR(100)

TABLE species_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

s1_scientific_name VARCHAR(80)

s1_common_name VARCHAR(80)

s1_protect CHAR(1)

s1_introduced CHAR(1)

s1_eradicate CHAR(1)

s2_scientific_name VARCHAR(80)

s2_common_name VARCHAR(80)

s2_protect CHAR(1)

s2_introduced CHAR(1)

s2_eradicate CHAR(1)

s3_scientific_name VARCHAR(80)

s3_common_name VARCHAR(80)

s3_protect CHAR(1)

s3_introduced CHAR(1)

s3_eradicate CHAR(1)

s4_scientific_name VARCHAR(80)

s4_common_name VARCHAR(80)

s4_protect CHAR(1)

s4_introduced CHAR(1)

s4_eradicate CHAR(1)

s5_scientific_name VARCHAR(80)

s5_common_name VARCHAR(80)

s5_protect CHAR(1)

s5_introduced CHAR(1)

s5_eradicate CHAR(1)

TABLE memo_table

nrrss_number INTEGER UNSIGNED REFERENCES nrrss_record_table

memo TEXT

NRRSS comments, questions: Emily S. Bernhardt     Web page and database contact: Bruce Powell