DB_Setup

Resume Website in PHP - Database Schema Diagram

Wiki Home

Below is the initial Database schema creation script - the latest version, of course, is in the repository.

:::sql
DROP TABLE IF EXISTS resume_website.social_me;
DROP TABLE IF EXISTS resume_website.skills;
DROP TABLE IF EXISTS resume_website.education;
DROP TABLE IF EXISTS resume_website.chunks;
DROP TABLE IF EXISTS resume_website.links;
DROP TABLE IF EXISTS resume_website.subsections;
DROP TABLE IF EXISTS resume_website.resume_sections;
DROP TABLE IF EXISTS resume_website.resumes;
DROP TABLE IF EXISTS resume_website.primary_data;
DROP TABLE IF EXISTS resume_website.resume_section_types;
DROP TABLE IF EXISTS resume_website.social_networks;

CREATE TABLE resume_website.social_networks (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , sn_name VARCHAR(255)
     , home_url VARCHAR(255)
     , COLUMN_4 CHAR(10)
     , COLUMN_5 CHAR(10)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
);

CREATE TABLE resume_website.resume_section_types (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , description TEXT NOT NULL
     , css_id VARCHAR(40)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
);

CREATE TABLE resume_website.primary_data (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , Name VARCHAR(255) NOT NULL
     , address TEXT NOT NULL
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
);

CREATE TABLE resume_website.resumes (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , pd_fk INT(10) UNSIGNED NOT NULL
     , description TEXT
     , header TEXT
     , header_css_id VARCHAR(40)
     , footer TEXT
     , footer_css_id VARCHAR(40)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (pd_fk)
     , CONSTRAINT FK_resumes_1 FOREIGN KEY (pd_fk)
                  REFERENCES resume_website.primary_data (id)
);

CREATE TABLE resume_website.resume_sections (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , section_name VARCHAR(255) NOT NULL
     , type_fk INT(10) UNSIGNED NOT NULL
     , resume_fk INT(10) UNSIGNED NOT NULL
     , order INT(10)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (type_fk)
     , CONSTRAINT FK_resume_sections_1 FOREIGN KEY (type_fk)
                  REFERENCES resume_website.resume_section_types (id)
     , INDEX (resume_fk)
     , CONSTRAINT FK_resume_sections_2 FOREIGN KEY (resume_fk)
                  REFERENCES resume_website.resumes (id)
);

CREATE TABLE resume_website.subsections (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , section_fk INT(10) UNSIGNED NOT NULL
     , output TEXT
     , order INT(10) NOT NULL
     , css_id VARCHAR(40)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (section_fk)
     , CONSTRAINT FK_subsections_1 FOREIGN KEY (section_fk)
                  REFERENCES resume_website.resume_sections (id)
);

CREATE TABLE resume_website.links (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , link_text VARCHAR(255) NOT NULL
     , url VARCHAR(255) NOT NULL
     , pd_fk INT(10) UNSIGNED NOT NULL
     , open_in_new_window TINYINT(4)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (pd_fk)
     , CONSTRAINT FK_links_1 FOREIGN KEY (pd_fk)
                  REFERENCES resume_website.primary_data (id)
);

CREATE TABLE resume_website.chunks (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , section_fk INT(10) UNSIGNED NOT NULL
     , subsection_fk INT(10) UNSIGNED NOT NULL
     , output TEXT
     , order INT(10) NOT NULL
     , css_id VARCHAR(30)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (subsection_fk)
     , CONSTRAINT FK_chunks_1 FOREIGN KEY (subsection_fk)
                  REFERENCES resume_website.subsections (id)
     , INDEX (section_fk)
     , CONSTRAINT FK_chunks_2 FOREIGN KEY (section_fk)
                  REFERENCES resume_website.resume_sections (id)
);

CREATE TABLE resume_website.education (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , pk_fk INT(10) UNSIGNED NOT NULL
     , education_level TEXT NOT NULL
     , facility_name TEXT NOT NULL
     , address TEXT
     , startdate DATE NOT NULL
     , completed_on_date DATE
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
     , INDEX (pk_fk)
     , CONSTRAINT FK_education_1 FOREIGN KEY (pk_fk)
                  REFERENCES resume_website.primary_data (id)
);

CREATE TABLE resume_website.skills (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , pd_fk INT(10) UNSIGNED NOT NULL
     , COLUMN_4 CHAR(10)
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , id INT(10) UNSIGNED NOT NULL
     , PRIMARY KEY (skills)
     , INDEX (pd_fk)
     , CONSTRAINT FK_skills_1 FOREIGN KEY (pd_fk)
                  REFERENCES resume_website.primary_data (id)
);

CREATE TABLE resume_website.social_me (
       id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
     , account_name VARCHAR(255) NOT NULL
     , sn_fk INT(10) UNSIGNED NOT NULL
     , pd_fk INT(10) UNSIGNED NOT NULL
     , description TEXT
     , order INT(10) NOT NULL
     , created_on DATETIME NOT NULL
     , updated_on TIMESTAMP DEFAULT 'NULL' ON UPDATE CURRENT_TIMESTAMP
     , text TEXT
     , PRIMARY KEY (id)
     , INDEX (pd_fk)
     , CONSTRAINT FK_SocialMe_2 FOREIGN KEY (pd_fk)
                  REFERENCES resume_website.primary_data (id)
     , INDEX (sn_fk)
     , CONSTRAINT FK_SocialMe_1 FOREIGN KEY (sn_fk)
                  REFERENCES resume_website.SocialNetworks (id)
);