Lost and Found Information System Database Design

Lost and Found Information System Database Design

The capstone project entitled “Lost and Found Information System” is an online platform that allows everyone to post or report a lost item or if they have found an item somewhere. With this online information system, it would be easy to report if you have accidentally lost an item or if you are the lucky one who has found something that is very important to someone.

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 system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

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

tbl_lost_found_item – this table stores information of the items reported by the members, it is either a lost item or a user can also post the item they found. The  lost and found table 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 – this is a foreign key that connects to the post category table.
• title – the user defined title of the post.
• description – a description of the item or of the post in general.
• date – date the member reports the lost or found item.
• time – time the member reports the lost or found item.
• location_lost_found – the location where the item lost or found.
• post_type (lost, found) – the member can report a lost or found item.
• member_id – this is a foreign key that links to the membership information table.
• status (claimed, unclaimed) – the moderator or member concerned can update the status of the post.
• remarks – additional information about the post or report.
• user_id – the user who manage, update the post information. It is a foreign key that connects to the user table.

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 – the members can upload multiple images about the report they posted as evidences. 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 – this is a foreign key that links to the lost and found item table.
• 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 – list of post categories allowed in the platform will be stored and managed in the post category table and it 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 (people, pet, cellphone, laptop, etc) – name of the category.
• description – description of the post category.
• user_id – the user who encodes, manage and update the category information. It is a foreign key that connects to the user table.

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_member – the member will need to register the following information for them to use the application. The member table 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.
• address – complete valid address of the member, it must reflect in their credentials such as their identification card.
• contact – contact information such as landline and mobile phone number of the member.
• email – personal email address of the member.
• username – desired username of the member.
• password – desired password of the member.
• user_id – the user who encodes, manage and update the member information. It is a foreign key that connects to the user table.

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_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 member needs to submit a valid credential to verify their identity, the submitted credentials will be evaluated by the moderators and administrators of the platform. The member credential table 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 links to the member profile information.
• credential_name – name of the credential (company id, driver’s license, etc)
• description – description of the credential submitted.
• user_id – the user who manage and update the credential information. It is a foreign key that connects to the user table.

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_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.
• 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;
COMMIT;

Free Download .sql file

Our team can modify the project based on your specific business requirements.

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.