Database Schema

Draft Database Schema

CREATE TABLE ${dbprefix}chapter (
    id INT PRIMARY KEY AUTOINCREMENT,
    unitid INT NOT NULL,
    name TINYTEXT NOT NULL DEFAULT ''
);

CREATE TABLE ${dbprefix}troop (
    id INT PRIMARY KEY AUTOINCREMENT,
    chapter_id INT NOT NULL FOREIGN KEY ${dbprefix}chapter(id),
    troopnum INT NOT NULL,
    leader_name VARCHAR(120),
    leader_phone VARCHAR(20),
    leader_email VARCHAR(250),
    meeting_location TEXT,
    UNIQUE(chapter_id,troopnum)
);

CREATE TABLE ${dbprefix}election (
    id INT PRIMARY KEY AUTOINCREMENT,
    troop_id INT NOT NULL FOREIGN KEY ${dbprefix}troop(id),
    election_date DATE,
    submission_source CHAR(2) NOT NULL DEFAULT 'UE', /* or SM */
    reg_active INT,
    youth_present INT,
    num_eligible INT,
    ballots_returned INT,
    ballots_required INT,
    num_elected INT,
    additional_info TEXT,
    submitter_name VARCHAR(120),
    submitter_email VARCHAR(250),
    submitter_phone VARCHAR(20),
    UNIQUE(troop_id,submission_source)
);

CREATE TABLE ${dbprefix}electedscout (
    id INT PRIMARY KEY AUTOINCREMENT,
    election_id INT NOT NULL FOREIGN KEY ${dbprefix}election(id),
    firstname VARCHAR(120) NOT NULL,
    middlename VARCHAR(120) NOT NULL DEFAULT '',
    lastname VARCHAR(120) NOT NULL,
    bsa_member_id BIGINT NOT NULL,
    scout_email VARCHAR(250),
    parent_email VARCHAR(250),
    mailing_address1 VARCHAR(120),
    mailing_address2 VARCHAR(120),
    city VARCHAR(60) NOT NULL,
    state CHAR(2) NOT NULL,
    zip VARCHAR(10),
    birthdate DATE,
    phone VARCHAR(20),
    UNIQUE(election_id,bsa_member_id)
);