Schema for nlrb

CREATE TABLE allegation(case_number VARCHAR, allegation VARCHAR, created_at TIMESTAMP, FOREIGN KEY (case_number) REFERENCES filing(case_number));
CREATE TABLE docket(case_number VARCHAR, date DATE, "document" VARCHAR, actor VARCHAR, url VARCHAR, created_at TIMESTAMP, FOREIGN KEY (case_number) REFERENCES filing(case_number));
CREATE TABLE "document"(case_number VARCHAR, "document" VARCHAR, url VARCHAR, created_at TIMESTAMP, FOREIGN KEY (case_number) REFERENCES filing(case_number));
CREATE TABLE election(election_id BIGINT, case_number VARCHAR, voting_unit_id BIGINT, date DATE, tally_type VARCHAR, ballot_type VARCHAR, unit_size BIGINT, created_at TIMESTAMP, PRIMARY KEY(election_id));
CREATE TABLE election_mode(election_id BIGINT, case_number VARCHAR, "name" VARCHAR, city VARCHAR, state VARCHAR, status VARCHAR, date_filed DATE, date_closed DATE, reason_closed VARCHAR, election_mode VARCHAR, date_ballot_mailed DATE, date_ballot_counted DATE, date_election_scheduled DATE, date_tally_scheduled DATE, date_tallied DATE, tally_type VARCHAR, ballot_type VARCHAR, unit_id VARCHAR, ballots_impounded VARCHAR, number_of_eligible_voters BIGINT, number_of_void_ballots BIGINT, labor_organization_1_name VARCHAR, votes_for_labor_organization_1 BIGINT, labor_organization_2_name VARCHAR, votes_for_labor_organization_2 BIGINT, labor_organization_3_name VARCHAR, votes_for_labor_organization_3 BIGINT, votes_cast_against_labor_org BIGINT, number_of_valid_votes_counted BIGINT, number_of_challenged_ballots BIGINT, challenges_are_determinative VARCHAR, runoff_required VARCHAR, union_to_certify VARCHAR, unit_involved_in_petition VARCHAR, bargaining_unit_determined VARCHAR, FOREIGN KEY (election_id) REFERENCES election(election_id));
CREATE TABLE election_result(election_id BIGINT, total_ballots_counted BIGINT, void_ballots BIGINT, challenged_ballots BIGINT, challenges_are_determinative VARCHAR, runoff_required VARCHAR, union_to_certify VARCHAR, created_at TIMESTAMP, PRIMARY KEY(election_id), FOREIGN KEY (election_id) REFERENCES election(election_id));
CREATE TABLE filing(case_number VARCHAR, "name" VARCHAR, case_type VARCHAR, url VARCHAR, city VARCHAR, state VARCHAR, date_filed DATE, region_assigned VARCHAR, status VARCHAR, date_closed DATE, reason_closed VARCHAR, number_of_eligible_voters BIGINT, number_of_voters_on_petition_or_charge BIGINT, certified_representative VARCHAR, created_at TIMESTAMP, updated_at TIMESTAMP, last_checked_at TIMESTAMP, PRIMARY KEY(case_number));
CREATE TABLE filing_group(root_case_number VARCHAR, case_number VARCHAR, created_at TIMESTAMP, updated_at TIMESTAMP, PRIMARY KEY(case_number));
CREATE TABLE participant(case_number VARCHAR, participant VARCHAR, "type" VARCHAR, subtype VARCHAR, address VARCHAR, address_1 VARCHAR, address_2 VARCHAR, city VARCHAR, state VARCHAR, zip VARCHAR, phone_number VARCHAR, created_at TIMESTAMP, FOREIGN KEY (case_number) REFERENCES filing(case_number));
CREATE TABLE sought_unit(case_number VARCHAR, unit_sought VARCHAR, created_at TIMESTAMP);
CREATE TABLE tally(election_id BIGINT, "option" VARCHAR, votes BIGINT, created_at TIMESTAMP, FOREIGN KEY (election_id) REFERENCES election(election_id));
CREATE TABLE voting_unit(voting_unit_id BIGINT, case_number VARCHAR, unit_id VARCHAR, description VARCHAR, created_at TIMESTAMP, PRIMARY KEY(voting_unit_id));