Crime Reporting Management System Database Model

Crime Reporting Management System Database Model

The crime reporting system is a web and mobile based project which is a database driven information system that will allow the residents to report an incident with the use of internet, smartphone and a mobile application.

This article will provide you an idea of what are the database tables needed to design and develop a crime reporting management system.

Development Tools: PHP, MariaDB, Bootstrap, JQuery Mobile, Apache Cordova

Database Design of the Crime Reporting Management System

tblcrimeinfo (crime_id, crime_description, date_reported, time_reported,  location, resident_id ,image_capture, crime_category_id, status)

Description: residents are allowed to report a crime online and with the use of their smartphone through the mobile application of the project. Database table tblcrimeinfo will store the records reported by the residents, information includes the description of the incident, location of the incident and if possible the residents could send an image as a proof about the report incident.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcrimeinfo` (
`crime_id` int(11) NOT NULL AUTO_INCREMENT,
`crime_description` varchar(100) NOT NULL,
`date_reported` date NOT NULL,
`time_reported` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`location` varchar(100) NOT NULL,
`resident_id` int(11) NOT NULL,
`image_capture` blob NOT NULL,
`crime_category_id` int(11) NOT NULL,
`status` varchar(15) NOT NULL,
PRIMARY KEY (`crime_id`),
KEY `resident_id` (`resident_id`,`crime_category_id`),
KEY `crime_category_id` (`crime_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcrimecategory (crime_category_id, category_description)

Description: tblcrimecategory is the table that stores the different categories of the crime/incident.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcrimecategory` (
`crime_category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_description` varchar(50) NOT NULL,
PRIMARY KEY (`crime_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblresident (resident_id, resident_name, resident, contact, resident_address, image, username, password, encodedby)

Description: residents will first register their personal information such as their name, contact, address, image, username and password. Once the account is validated, the resident can now access the system through online and mobile application.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblresident` (
`resident_id` int(11) NOT NULL AUTO_INCREMENT,
`resident_name` varchar(100) NOT NULL,
`resident_contact` varchar(11) NOT NULL,
`resident_address` varchar(100) NOT NULL,
`image` blob NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`encodedby` int(11) NOT NULL,
PRIMARY KEY (`resident_id`),
KEY `encodedby` (`encodedby`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblresidentcredential (credential_id, resident_id, credential_info)

Description: A resident needs to pass/upload their credentials such as a valid id or any valid documents as a proof of their identity. The database table that will store such information is the tblresidentcredential.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblresidentcredential` (
`credential_id` int(11) NOT NULL AUTO_INCREMENT,
`resident_id` int(11) NOT NULL,
`credential_info` int(50) NOT NULL,
PRIMARY KEY (`credential_id`),
KEY `resident_id` (`resident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblresponseinfo (response_info_id, crime_id, response_date, response_time, police_officer_id, status)

Description: the purpose of the tblresponseinfo is to store records of the response team for a certain incident reported by the residents. It includes the crime information, the date and time of response, the police officer(s) who responded to the report and the status of the report, whether it was solved or on the process of investigation.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblresponseinfo` (
`response_info_id` int(11) NOT NULL AUTO_INCREMENT,
`crime_id` int(11) NOT NULL,
`response_date` date NOT NULL,
`response_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`police_officer_id` int(11) NOT NULL,
`status` varchar(15) NOT NULL,
PRIMARY KEY (`response_info_id`),
KEY `crime_id` (`crime_id`,`police_officer_id`),
KEY `police_officer_id` (`police_officer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpoliceinfo (police_id, police_name, police_mobile_no, police_address, police_email, username, password, encodedby)

Description: information of the police officers will also be encoded and stored in the system. Name of the police, mobile number, address, email, username and password will be stored in the tblpoliceinfo database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpoliceinfo` (
`police_id` int(11) NOT NULL AUTO_INCREMENT,
`police_name` varchar(100) NOT NULL,
`police_mobile_no` varchar(11) NOT NULL,
`police_address` varchar(100) NOT NULL,
`police_email` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`encodedby` int(11) NOT NULL,
PRIMARY KEY (`police_id`),
KEY `encodedby` (`encodedby`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, fullname, address, contact, username, password)

Description: information of the administrators is stored in the tbluser table. The admins are the ones who will encode the information of police officers and residents.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`fullname` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Constraints for dumped tables

— Constraints for table `tblcrimeinfo`

ALTER TABLE `tblcrimeinfo`
ADD CONSTRAINT `tblcrimeinfo_ibfk_2` FOREIGN KEY (`resident_id`) REFERENCES `tblresident` (`resident_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblcrimeinfo_ibfk_1` FOREIGN KEY (`crime_category_id`) REFERENCES `tblcrimecategory` (`crime_category_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblpoliceinfo`

ALTER TABLE `tblpoliceinfo`
ADD CONSTRAINT `tblpoliceinfo_ibfk_1` FOREIGN KEY (`encodedby`) REFERENCES `tbluser` (`user_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblresident`

ALTER TABLE `tblresident`
ADD CONSTRAINT `tblresident_ibfk_1` FOREIGN KEY (`encodedby`) REFERENCES `tbluser` (`user_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblresidentcredential`

ALTER TABLE `tblresidentcredential`
ADD CONSTRAINT `tblresidentcredential_ibfk_1` FOREIGN KEY (`resident_id`) REFERENCES `tblresident` (`resident_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblresponseinfo`

ALTER TABLE `tblresponseinfo`
ADD CONSTRAINT `tblresponseinfo_ibfk_2` FOREIGN KEY (`police_officer_id`) REFERENCES `tblpoliceinfo` (`police_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblresponseinfo_ibfk_1` FOREIGN KEY (`crime_id`) REFERENCES `tblcrimeinfo` (`crime_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.