School Asset Inventory System Database Design

School Asset Inventory System Database Design

Introduction

A well-structured database is essential for effectively managing school assets. The School Asset Inventory System (SAIS) provides a comprehensive solution for tracking and managing various assets within educational institutions. By implementing a robust database design, schools can ensure efficient asset management, accountability, and compliance with regulations.

This tutorial will guide you through the database design process for the SAIS, covering key tables, relationships, and best practices. Understanding the database structure will empower you to effectively utilize the system and customize it to meet your specific needs.

Database Design Overview

The database design is the backbone of the School Asset Inventory System. It ensures that all asset data, assignments, locations, and accountable persons are structured in a way that allows for efficient management, tracking, and reporting. Below is a detailed discussion on the database structure and its components.

The database structure consists of several key tables, each designed to handle different aspects of school asset management. This includes assets themselves, the categories they belong to, the locations where they are stored, the people responsible for them, and any transfers or maintenance activities associated with the assets.

Each table serves a specific purpose, and relationships between these tables ensure that the data remains organized and consistent across the entire system.

Database Structure

Let’s discuss the key tables of the School Asset Inventory System and their importance in managing school assets efficiently:

tbl_category

• Purpose: This table categorizes assets to make it easier to group and manage them. Categories may include items like “Electronics”, “Furniture”, or “Vehicles”.
• Fields:
  • category_id: Unique identifier for each category.
  • category_name: The name of the asset category (e.g., “Computers”).
  • description: Additional details or notes about the category.
• Example: A laptop might be under the category “Electronics”.

tbl_office

• Purpose: This table stores information about the different offices or departments that might be assigned assets.
• Fields:
  • office_id: Unique ID for each office or department.
  • office_name: Name of the office (e.g., “IT Department”).
  • description: Description of the office or department.
• Example: An office responsible for managing technological assets.

tbl_location

• Purpose: Tracks where each asset is physically stored, such as buildings, rooms, or storage facilities.
• Fields:
  • location_id: Unique ID for each location.
  • location_name: Name of the location (e.g., “Room 101”).
  • location_address: Physical address or other details about the location.
• Example: A computer lab or storage room.

tbl_accountable_person

• Purpose: Holds data about the people responsible for managing or using assets. These can be faculty, staff, or other accountable persons.
• Fields:
  • accountable_person_id: Unique identifier for each person.
  • complete_name: The full name of the person.
  • email_address: Their contact email.
  • contact_number: Phone number for communication.
  • profile_image: Image for visual identification.
  • username & password: For login purposes.
• Example: John Doe, head of the IT department, accountable for computer equipment.

tbl_asset

• Purpose: Central table for recording all assets, including their category, barcode, and status.
• Fields:
  • asset_id: Unique identifier for each asset.
  • category_id: Links each asset to a category from tbl_category.
  • barcode: Unique barcode for quick asset scanning and tracking.
  • asset_name: The name of the asset (e.g., “HP Laptop”).
  • description: Details or additional notes about the asset.
  • status: The asset’s condition or state (e.g., “active”, “maintenance”).
• Example: A barcode-scanned laptop belonging to the “Electronics” category.

tbl_assignment

• Purpose: Logs when assets are assigned to an accountable person, office, and location. This allows for accurate tracking of who is using which asset and where.
• Fields:
  • assignment_id: Unique ID for each assignment.
  • accountable_person_id: Links to the person responsible for the asset.
  • asset_id: Links to the assigned asset.
  • office_id: The office or department where the asset is assigned.
  • location_id: The physical location of the asset.
  • assignment_date: Date when the asset was assigned.
  • status: Tracks whether the asset is “active”, “returned”, “disposed”, or under “maintenance”.
  • remarks: Additional comments or notes about the assignment.
  • upload_mr: Option to upload an official document for the assignment.
• Example: A laptop assigned to John Doe in the IT department with the status “active”.

tbl_transfer

• Purpose: Logs the transfer of assets between accountable persons or locations, ensuring accurate tracking of asset movements.
• Fields:
  • transfer_id: Unique ID for each transfer.
  • assignment_id: Links the transfer to the asset’s assignment.
  • accountable_person_id_from: The person transferring the asset.
  • accountable_person_id_to: The person receiving the asset.
  • transfer_date: Date of the transfer.
  • remarks: Additional notes about the transfer.
• Example: A laptop transferred from John Doe to Jane Smith.

Relationships

The School Asset Inventory System (SAIS) utilizes foreign key relationships to connect the various tables and establish meaningful relationships between entities. This allows for efficient data retrieval and analysis.

Here’s a breakdown of the primary relationships:

1. tbl_asset and tbl_category:
   • Each asset is associated with a specific category.
   • The category_id in the tbl_asset table references the category_id in the tbl_category table.
2. tbl_assignment and tbl_asset:
   • Each assignment is linked to a specific asset.
   • The asset_id in the tbl_assignment table references the asset_id in the tbl_asset table.
3. tbl_assignment and tbl_accountable_person:
   • Each assignment is assigned to a specific accountable person.
   • The accountable_person_id in the tbl_assignment table references the accountable_person_id in the tbl_accountable_person table.
4. tbl_assignment and tbl_location:
   • Each assignment is associated with a specific location.
   • The location_id in the tbl_assignment table references the location_id in the tbl_location table.
5. tbl_transfer and tbl_assignment:
   • Each asset transfer is linked to a specific assignment.
   • The assignment_id in the tbl_transfer table references the assignment_id in the tbl_assignment table.

These relationships enable the SAIS to track the complete lifecycle of assets, from initial assignment to transfer or disposal. By understanding these relationships, you can effectively query and analyze the data stored in the database.

SQL Statement

-- Table: tbl_category
CREATE TABLE tbl_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL,
description TEXT
);

-- Table: tbl_office
CREATE TABLE tbl_office (
office_id INT AUTO_INCREMENT PRIMARY KEY,
office_name VARCHAR(100) NOT NULL,
description TEXT
);

-- Table: tbl_location
CREATE TABLE tbl_location (
location_id INT AUTO_INCREMENT PRIMARY KEY,
location_name VARCHAR(100) NOT NULL,
location_address TEXT
);

-- Table: tbl_accountable_person
CREATE TABLE tbl_accountable_person (
accountable_person_id INT AUTO_INCREMENT PRIMARY KEY,
complete_name VARCHAR(150) NOT NULL,
email_address VARCHAR(100) NOT NULL,
contact_number VARCHAR(15),
profile_image VARCHAR(255),
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL
);

-- Table: tbl_asset
CREATE TABLE tbl_asset (
asset_id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
barcode VARCHAR(100) UNIQUE,
asset_name VARCHAR(150) NOT NULL,
description TEXT,
status ENUM('active', 'returned', 'disposed', 'maintenance') NOT NULL,
FOREIGN KEY (category_id) REFERENCES tbl_category(category_id)
);

-- Table: tbl_assignment
CREATE TABLE tbl_assignment (
assignment_id INT AUTO_INCREMENT PRIMARY KEY,
accountable_person_id INT NOT NULL,
asset_id INT NOT NULL,
office_id INT NOT NULL,
location_id INT NOT NULL,
assignment_date DATE NOT NULL,
status ENUM('active', 'returned', 'disposed', 'maintenance') NOT NULL,
remarks TEXT,
upload_mr VARCHAR(255),
FOREIGN KEY (accountable_person_id) REFERENCES tbl_accountable_person(accountable_person_id),
FOREIGN KEY (asset_id) REFERENCES tbl_asset(asset_id),
FOREIGN KEY (office_id) REFERENCES tbl_office(office_id),
FOREIGN KEY (location_id) REFERENCES tbl_location(location_id)
);

-- Table: tbl_transfer
CREATE TABLE tbl_transfer (
transfer_id INT AUTO_INCREMENT PRIMARY KEY,
assignment_id INT NOT NULL,
accountable_person_id_from INT NOT NULL,
accountable_person_id_to INT NOT NULL,
transfer_date DATE NOT NULL,
remarks TEXT,
FOREIGN KEY (assignment_id) REFERENCES tbl_assignment(assignment_id),
FOREIGN KEY (accountable_person_id_from) REFERENCES tbl_accountable_person(accountable_person_id),
FOREIGN KEY (accountable_person_id_to) REFERENCES tbl_accountable_person(accountable_person_id)
);

-- Table: tbl_assignment_history
CREATE TABLE tbl_assignment_history (
assignment_id INT AUTO_INCREMENT PRIMARY KEY,
accountable_person_id INT NOT NULL,
asset_id INT NOT NULL,
office_id INT NOT NULL,
location_id INT NOT NULL,
assignment_date DATE NOT NULL,
status ENUM('active', 'returned', 'disposed', 'maintenance') NOT NULL,
remarks TEXT,
FOREIGN KEY (accountable_person_id) REFERENCES tbl_accountable_person(accountable_person_id),
FOREIGN KEY (asset_id) REFERENCES tbl_asset(asset_id),
FOREIGN KEY (office_id) REFERENCES tbl_office(office_id),
FOREIGN KEY (location_id) REFERENCES tbl_location(location_id)
);

FREE DOWNLOAD DATABASE

Conclusion

The School Asset Inventory System database is structured to manage assets effectively by categorizing them, tracking their status, and assigning them to specific users and locations. The design ensures data integrity through well-defined relationships between tables like tbl_asset, tbl_assignment, tbl_accountable_person, and others. This clear, structured approach simplifies asset management, enabling efficient tracking of assets, transfers, maintenance, and disposals.

A well-designed database is critical for tracking assets, preventing mismanagement, and ensuring accountability within the school environment. By maintaining data consistency, the system ensures that all asset-related activities, such as assignment, maintenance, and disposal, are recorded and easily accessible.

Future Enhancements

To further improve the system, future updates could include:

• Barcode Integration: Adding barcode scanning functionality for easier asset tracking and inventory updates.
• Detailed Maintenance Tracking: Recording more granular maintenance information, such as specific repair parts or technician details.
• Reports Generation: Automating the generation of detailed reports for asset status, maintenance history, and usage trends, possibly including export options for CSV, PDF, or Excel formats.

These enhancements would make the system even more powerful, offering additional tools to streamline asset management and reporting for schools.

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.