Database Schema

Draft Database Schema

CREATE TABLE {prefix}district (
    id INT PRIMARY KEY AUTOINCREMENT,
    unitid INT NOT NULL,
    name TINYTEXT NOT NULL DEFAULT ""
)

CREATE TABLE {prefix}election (
    id INT PRIMARY KEY AUTOINCREMENT,
    district INT NOT NULL FOREIGN KEY {prefix}district(id),
    troopnum INT NOT NULL,
    election_date DATETIME,
    meeting_location TEXT,
    reg_active INT,
    youth_present INT,
    num_eligible INT,
    ballots_returned INT,
    ballots_required INT,
    num_elected INT,
    leader_name TEXT,
    leader_phone TEXT,
    leader_email TEXT,
    additional_info TEXT,
    submitter_name TEXT,
    submitter_email TEXT,
    submitter_phone TEXT,
    UNIQUE(district,troop)
);

CREATE TABLE {prefix}electedscout (
    id INT PRIMARY KEY AUTOINCREMENT,
    election_id INT NOT NULL FOREIGN KEY {prefix}election(id),
    firstname TEXT NOT NULL,
    middlename TEXT NOT NULL DEFAULT "",
    lastname TEXT NOT NULL,
    bsa_member_id BIGINT NOT NULL,
    scout_email TEXT,
    parent_email TEXT,
    mailing_address1 TEXT,
    mailing_address2 TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    birthdate DATETIME,
    phone TEXT
);