form builder db design

form_builder.sql

-- Organizations Table
CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    organization_name VARCHAR(80) NOT NULL
);

-- Creating an index on the organization_name as suggested by the ER diagram.
CREATE INDEX idx_organizations_name ON organizations(organization_name);

-- Create ENUM type for survey status
CREATE TYPE survey_status AS ENUM ('draft', 'published', 'archived');

-- Survey Headers Table
CREATE TABLE project_forms (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    survey_name VARCHAR(80) NOT NULL,
    instructions TEXT,
    other_header_info VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    status survey_status DEFAULT 'draft'
);

-- Input Types Table
CREATE TABLE form_input_types (
    id SERIAL PRIMARY KEY,
    input_type_name VARCHAR(80) NOT NULL
);

-- Creating an index on the input_type_name as suggested by the ER diagram.
CREATE INDEX idx_input_types_name ON form_input_types(input_type_name);

-- Option Groups Table
CREATE TABLE form_option_groups (
    id SERIAL PRIMARY KEY,
    option_group_name VARCHAR(45) NOT NULL
);

-- Survey Sections Table
CREATE TABLE form_sections (
    id SERIAL PRIMARY KEY,
    survey_header_id INTEGER NOT NULL REFERENCES project_forms(id),
    section_name VARCHAR(80) NOT NULL,
    section_title VARCHAR(45),
    section_subheading VARCHAR(45),
    section_required BOOLEAN NOT NULL,
    sort_order INTEGER,
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Questions Table
CREATE TABLE form_questions (
    id SERIAL PRIMARY KEY,
    survey_section_id INTEGER NOT NULL REFERENCES form_sections(id),
    input_type_id INTEGER NOT NULL REFERENCES form_input_types(id),
    question_subtext VARCHAR(500),
    question_text VARCHAR(500) NOT NULL,
    answer_required BOOLEAN NOT NULL,
    option_group_id INTEGER REFERENCES form_option_groups(id),
    allow_multiple_option_answers BOOLEAN,
    sort_order INTEGER,
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Option Choices Table
CREATE TABLE form_option_choices (
    id SERIAL PRIMARY KEY,
    option_group_id INTEGER NOT NULL REFERENCES form_option_groups(id),
    option_choice_name VARCHAR(45) NOT NULL,
    description TEXT,
    sort_order INTEGER,
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Question Options Table
CREATE TABLE form_question_options (
    id SERIAL PRIMARY KEY,
    question_id INTEGER NOT NULL REFERENCES form_questions(id),
    option_choice_id INTEGER NOT NULL REFERENCES form_option_choices(id)
);

-- Mt_numbers Table
CREATE TABLE mt_numbers (
    id SERIAL PRIMARY KEY,
    mt_number VARCHAR(80) NOT NULL
);

-- Creating an index on the mt_number as suggested by the ER diagram.
CREATE INDEX idx_mt_numbers_name ON mt_numbers(mt_number);

-- Answers Table
CREATE TABLE form_responses (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    mt_number INTEGER NOT NULL REFERENCES mt_numbers(id),
    question_option_id INTEGER REFERENCES form_question_options(id),
    answer_numeric INTEGER,
    answer_text VARCHAR(255),
    answer_boolean BOOLEAN,
    question_id INTEGER REFERENCES form_questions(id),
    answer_date DATE,
    answer_datetime TIMESTAMP,
    additional_text VARCHAR(255),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    answer_file VARCHAR(255)
);