Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Setting database queries for Document Tracking System
CREATE TABLE receiving(
receiving_date DATE NOT NULL,
diary_no INT NOT NULL,
no_of_letter VARCHAR(40) NOT NULL,
creation_date DATE NOT NULL,
file_no VARCHAR(40),
-- original_address VARCHAR(40),
from_dpt VARCHAR(40) NOT NULL,
to_dpt VARCHAR(40) NOT NULL,
subject VARCHAR(255) NOT NULL,
letter_type VARCHAR(40) NOT NULL,
action_taken VARCHAR(40),
-- final_status VARCHAR(40),
remarks VARCHAR(255),
PRIMARY KEY ( diary_no, to_dpt ),
FOREIGN KEY ( from_dpt ) REFERENCES department( dpt_id ) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY ( to_dpt ) REFERENCES department( dpt_id ) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE department(
dpt_id INT NOT NULL,
dpt_name VARCHAR(40) NOT NULL,
dpt_tag VARCHAR(10) NOT NULL,
dpt_address VARCHAR(40),
dpt_type VARCHAR(40), -- Individual/Deptt
PRIMARY KEY ( dpt_id )
) ENGINE=InnoDB;
CREATE TABLE doc_status(
status_id INT NOT NULL AUTO_INCREMENT,
status_name VARCHAR(40) NOT NULL,
remarks VARCHAR(255)
) ENGINE=InnoDB;
CREATE TABLE file_status(
file_id INT NOT NULL,
to_dpt VARCHAR(40) NOT NULL,
status_id INT NOT NULL,
status_date TIMESTAMP NOT NULL,
PRIMARY KEY ( file_id, to_dpt, status_id ),
FOREIGN KEY ( file_id, to_dpt ) REFERENCES receiving( diary_no, to_dpt ) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY ( status_id ) REFERENCES doc_status( status_id ) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE sending(
dispatch_no INT NOT NULL AUTO_INCREMENT,
file_no INT,
creation_date DATE,
sent_to VARCHAR(40),
through VARCHAR(40),
copy_to VARCHAR(40),
subject VARCHAR(255),
remarks VARCHAR(255),
PRIMARY KEY ( dispatch_no )
) ENGINE=InnoDB;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.