Lost and Found Information System Database Design 2021

Lost and Found Information System Database Design

The capstone project entitled “Lost and Found Information System” is an online platform that allows the users to post and browse for a lost or found items and to communicate with the other registered members.

This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of Lost and Found Information System. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

This database design has 7 tables with their respective fields and columns as well as their relationships among each other.

Lost and Found Information System Database Design - List of Database Tables — Lost and Found Information System Database Design – List of Database Tables

tbl_member – member table will store the information of the user or member. The member table of the Lost and Found Information System has 8 columns.

member_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
complete_name – complete name of the member/user.
address – complete address of the member/user.
contact – contact information, preferably mobile number of the member/user.
email – personal email address of the member/user.
username – desired username of the member/user.
password – desired password of the member/user. Maximum of 8 characters and combination of letter, numbers and special characters.
user_id – this is the foreign key that represents the moderator who will approve/disapprove the member application.

Create SQL Statement – the statement below is used to create the tbl_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_member` (
`member_id` int(11) NOT NULL,
`complete_name` varchar(30) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_member_credential – the members of the platform needs to present a credential such as ID or any documents that will validate your identity. The member credential table of the Lost and Found Information System has 5 columns:

credential_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
member_id – this is a foreign key that points out to the member information, the member can submit multiple credentials.
credential_name – credential could be in a form of valid ID, passport, birth certificate or any other legal documents that will validate the member information.
description – description of the credential.
user_id – this is the foreign key that represents the moderator who will review/approve/disapprove the member credentials.

Create SQL Statement – the statement below is used to create the tbl_member_credential, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_member_credential` (
`credential_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`credential_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_lost_found_item – this is the table where the members can upload information for the lost or found items. The information on this table will be validated, monitored and moderated by the moderators and system administrators. The tbl_lost_found_item table of the Lost and Found Information System has 12 columns.

post_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
post_category_id – foreign key that represents to the category of the item.
title – represents to the title of the post.
description – this is where the body of the content or the detailed information about the lost or found item.
date – the date the item was lost or found.
time – the estimated time the item was lost or found.
location_lost_found – the location where the item was lost or found.
post_type – 0 means pending, 1 means published
member_id – this is a foreign key that represents to the member who posted the article.
status – the status of the post, 0 = lost, 1 = found, 2 = solved.
remarks – other notes or message about the post
user_id – this is the foreign key that represents the moderator who will review/approve/disapprove the post.

Create SQL Statement – the statement below is used to create the tbl_lost_found_item, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_lost_found_item` (
`post_id` int(11) NOT NULL,
`post_category_id` int(11) NOT NULL,
`title` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`location_lost_found` varchar(100) NOT NULL,
`post_type` int(1) NOT NULL,
`member_id` int(11) NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_post_images – this table will store the images of the items posted by the members. The post images table has 3 columns:

image_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
post_id – refers to the post.
description – description of the image.

Create SQL Statement – the statement below is used to create the tbl_post_images, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_post_images` (
`image_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_post_category – this table can only be accessed by the administrator and moderators of the system. They need to setup the list of items that will be included or covered by the service. Post category of the Lost and Found Information System has 4 columns:

category_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
category_name – name of the categories included in the service (electronic devices, pets, wallets, ID’s, etc).
description – refers to the detailed information about the category.
user_id – this is the foreign key that represents the moderator who have encoded/updated the category information.

Create SQL Statement – the statement below is used to create the tbl_post_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_post_category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_user – this table will store the information of personnel who can access the records of the system.

user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
username – username of the personnel used to login together with the password.
password – password of the personnel used to login together with the username.
avatar – this will hold the profile image of the user.
fullname – the complete name of the personnel or user.
contact – contact number of the personnel (mobile/cellphone number).
email – email address of the personnel/user.
user_category_id – the user group or category of the user.
status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`user_category_id` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.

user_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
group_name – name of the category or user group (cashier, encoder, administrator).
description – information on what the user group is all about.
allow_add – this column is to allow or prevent user from adding a record.
allow_edit – this column is to allow or prevent user from editing or updating a record.
allow_delete – this column is to allow or prevent user from removing or deleting a record.
allow_print – this column is to allow or prevent user from printing a report.
allow_import – this column is to allow or prevent user from importing records to the system.
allow_export – this column is to allow or prevent user from exporting records from the system.

Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user_group` (
`user_group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`allow_add` int(1) NOT NULL,
`allow_edit` int(1) NOT NULL,
`allow_delete` int(1) NOT NULL,
`allow_print` int(1) NOT NULL,
`allow_import` int(1) NOT NULL,
`allow_export` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes for dumped tables

-- Indexes for table `tbl_lost_found_item`
--
ALTER TABLE `tbl_lost_found_item`
ADD PRIMARY KEY (`post_id`),
ADD KEY `post_category_id` (`post_category_id`),
ADD KEY `member_id` (`member_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_member`
--
ALTER TABLE `tbl_member`
ADD PRIMARY KEY (`member_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_member_credential`
--
ALTER TABLE `tbl_member_credential`
ADD PRIMARY KEY (`credential_id`),
ADD KEY `member_id` (`member_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_post_category`
--
ALTER TABLE `tbl_post_category`
ADD PRIMARY KEY (`category_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_post_images`
--
ALTER TABLE `tbl_post_images`
ADD PRIMARY KEY (`image_id`),
ADD KEY `post_id` (`post_id`);

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD PRIMARY KEY (`user_id`),
ADD KEY `user_category_id` (`user_category_id`);

--
-- Indexes for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
ADD PRIMARY KEY (`user_group_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_lost_found_item`
--
ALTER TABLE `tbl_lost_found_item`
MODIFY `post_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_member`
--
ALTER TABLE `tbl_member`
MODIFY `member_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_member_credential`
--
ALTER TABLE `tbl_member_credential`
MODIFY `credential_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_post_category`
--
ALTER TABLE `tbl_post_category`
MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_post_images`
--
ALTER TABLE `tbl_post_images`
MODIFY `image_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT;

Constraints for dumped tables

--
-- Constraints for table `tbl_lost_found_item`
--
ALTER TABLE `tbl_lost_found_item`
ADD CONSTRAINT `tbl_lost_found_item_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_lost_found_item_ibfk_2` FOREIGN KEY (`post_category_id`) REFERENCES `tbl_post_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_lost_found_item_ibfk_3` FOREIGN KEY (`member_id`) REFERENCES `tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_member`
--
ALTER TABLE `tbl_member`
ADD CONSTRAINT `tbl_member_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_member_credential`
--
ALTER TABLE `tbl_member_credential`
ADD CONSTRAINT `tbl_member_credential_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_member_credential_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_post_category`
--
ALTER TABLE `tbl_post_category`
ADD CONSTRAINT `tbl_post_category_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_post_images`
--
ALTER TABLE `tbl_post_images`
ADD CONSTRAINT `tbl_post_images_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `tbl_lost_found_item` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD CONSTRAINT `tbl_user_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tbl_user_group` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;