Database Schema

Draft Database Schema

CREATE TABLE ${dbprefix}district (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sortkey INT NOT NULL DEFAULT 0,
    name VARCHAR(120) NOT NULL DEFAULT '',
    UNIQUE(name)
);

CREATE TABLE ${dbprefix}chapter (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sortkey INT NOT NULL DEFAULT 0,
    name VARCHAR(120) NOT NULL DEFAULT '',
    district_id INT NOT NULL,
    UNIQUE(name),
    FOREIGN KEY (district_id) REFERENCES ${dbprefix}district(id)
);

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

CREATE TABLE ${dbprefix}election (
    id INT PRIMARY KEY AUTO_INCREMENT,
    troop_id INT NOT NULL,
    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),
    FOREIGN KEY (troop_id) REFERENCES ${dbprefix}troop(id)
);

CREATE TABLE ${dbprefix}electedscout (
    id INT PRIMARY KEY AUTO_INCREMENT,
    election_id INT NOT NULL,
    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),
    FOREIGN KEY (election_id) REFERENCES ${dbprefix}election(id)
);