Pet Adoption Database Design

Pet Adoption Database Design

Introduction

The Pet Adoption System is designed to simplify the process of adopting pets by connecting pet owners, adopters, and administrators in a single platform. The system helps manage the adoption process efficiently, from pet registration to adoption requests and final approvals.

Purpose of the System

The primary goal of this system is to streamline the adoption process by creating detailed profiles for each pet, ensuring adopters can easily browse and request pets for adoption. Pet owners can register their pets, upload health and vaccination records, and track adoption statuses. Administrators oversee the entire process, from managing pet profiles to approving or rejecting adoption requests.

Key Users of the System

• Pet Owners: Register their pets, upload health details, and monitor the adoption status.
• Adopters: Create accounts, browse available pets, submit adoption requests, and track the progress.
• Administrators: Manage all users, review adoption requests, approve or reject requests, and finalize adoptions.

Managing Pet Adoptions

This system ensures a seamless process for managing pets, handling adoption requests, and keeping records of health status and vaccinations. It also supports uploading media like pet photos and vaccination documents.

Importance of Database Design Planning

A well-structured database is essential for this system to function smoothly. It helps organize pet details, owner and adopter information, and adoption records. Thoughtful planning of the database design ensures data consistency, security, and ease of access, making the pet adoption process more efficient for all users involved.

Database Structure

Here is a detailed description of each table in your Pet Adoption Database Structure:

1. tbl_pet_type

• Description: Stores information about the different types of pets available for adoption.
• Columns:
  • pet_type_id: Unique identifier for each pet type (e.g., dog, cat).
  • pet_type_name: The name of the pet type (e.g., “Dog”, “Cat”).

2. tbl_pet

• Description: Contains detailed information about each individual pet in the system.
• Columns:
  • pet_id: Unique identifier for each pet.
  • pet_owner_id: References the owner of the pet from the tbl_pet_owner.
  • pet_name: The name of the pet.
  • pet_type_id: References the type of pet from tbl_pet_type.
  • description: A short description of the pet, including temperament, breed, etc.
  • age: The age of the pet.
  • gender: The gender of the pet (e.g., male, female).
  • health_status: The health status of the pet (e.g., Healthy, Needs Treatment).
  • upload_health_history: File or document detailing the pet’s health history.
  • vaccination_status: Indicates if the pet is vaccinated (Vaccinated, Not Vaccinated).
  • proof_of_vaccination: File or document showing proof of vaccination.
  • adoption_status: Current status of the pet (Available, Pending, Adopted).
  • date_registered: The date the pet was registered in the system.

3. tbl_pet_media

• Description: Stores media related to the pets, such as photos and videos.
• Columns:
  • pet_media_id: Unique identifier for each media entry.
  • pet_id: References the associated pet from the tbl_pet.
  • pet_media_name: Name or description of the media (e.g., “Front view”).
  • pet_media_url: URL or location where the media file is stored.

4. tbl_pet_owner

• Description: Contains information about the owners who register pets for adoption.
• Columns:
  • pet_owner_id: Unique identifier for each pet owner.
  • pet_owner_name: Name of the pet owner.
  • pet_owner_contact: Contact number of the pet owner.
  • pet_owner_email: Email address of the pet owner.
  • pet_owner_address: Home address of the pet owner.
  • pet_owner_profile: Profile information or image of the pet owner.
  • pet_owner_username: Username for pet owner login.
  • pet_owner_password: Encrypted password for pet owner login.

5. tbl_adopter

• Description: Contains information about potential adopters interested in adopting pets.
• Columns:
  • adopter_id: Unique identifier for each adopter.
  • adopter_name: Name of the adopter.
  • adopter_contact: Contact number of the adopter.
  • adopter_email: Email address of the adopter.
  • adopter_address: Home address of the adopter.
  • adopter_profile: Profile information or image of the adopter.
  • adopter_username: Username for adopter login.
  • adopter_password: Encrypted password for adopter login.

6. tbl_adoption_request

• Description: Tracks adoption requests made by adopters for specific pets.
• Columns:
  • adoption_request_id: Unique identifier for each adoption request.
  • pet_id: References the pet being requested for adoption from the tbl_pet.
  • adopter_id: References the adopter making the request from tbl_adopter.
  • request_date: The date the adoption request was made.
  • status: The current status of the request (Pending, Approved, Rejected).
  • approval_date: The date the request was approved (if applicable).
  • remarks: Additional notes or comments about the request.
  • user_id: References the admin who handled the request.

7. tbl_adoption

• Description: Records finalized adoptions, including details of the adoption process.
• Columns:
  • adoption_id: Unique identifier for each adoption.
  • pet_id: References the pet being adopted from tbl_pet.
  • adopter_id: References the adopter from tbl_adopter.
  • adoption_date: The date the adoption was finalized.
  • upload_adoption_document: File or document showing proof of adoption.
  • remarks: Additional comments or details about the adoption.
  • user_id: References the admin overseeing the adoption process.

This detailed description outlines the purpose of each table and how the columns support the pet adoption process. This structure provides a comprehensive system for managing pets, owners, adopters, adoption requests, and finalized adoptions.

Relationship

The relationships among the tables in the Pet Adoption Database are essential for connecting the various entities involved in the pet adoption process. Here’s a breakdown of the relationships:

1. tbl_pet_type and tbl_pet:

• One-to-Many Relationship: A pet type can have many pets, but a pet belongs to only one type.
• Foreign Key: The pet_type_id in the tbl_pet table is a foreign key referencing the pet_type_id in the tbl_pet_type table.

2. tbl_pet_owner and tbl_pet:

• One-to-Many Relationship: A pet owner can have many pets, but a pet belongs to only one owner.
• Foreign Key: The pet_owner_id in the tbl_pet table is a foreign key referencing the pet_owner_id in the tbl_pet_owner table.

3. tbl_pet and tbl_pet_media:

• One-to-Many Relationship: A pet can have many media files (e.g., images, videos), but a media file belongs to only one pet.
• Foreign Key: The pet_id in the tbl_pet_media table is a foreign key referencing the pet_id in the tbl_pet table.

4. tbl_pet and tbl_adoption_request:

• One-to-Many Relationship: A pet can have many adoption requests, but an adoption request is for only one pet.
• Foreign Key: The pet_id in the tbl_adoption_request table is a foreign key referencing the pet_id in the tbl_pet table.

5. tbl_adopter and tbl_adoption_request:

• One-to-Many Relationship: An adopter can make many adoption requests, but an adoption request is from only one adopter.
• Foreign Key: The adopter_id in the tbl_adoption_request table is a foreign key referencing the adopter_id in the tbl_adopter table.

6. tbl_pet and tbl_adoption:

• One-to-Many Relationship: A pet can have multiple adoptions (e.g., temporary foster placements), but an adoption is for only one pet.
• Foreign Key: The pet_id in the tbl_adoption table is a foreign key referencing the pet_id in the tbl_pet table.

7. tbl_adopter and tbl_adoption:

• One-to-Many Relationship: An adopter can have many adoptions, but an adoption is for only one adopter.
• Foreign Key: The adopter_id in the tbl_adoption table is a foreign key referencing the adopter_id in the tbl_adopter table.

SQL Statement

-- Create tbl_pet_type
CREATE TABLE tbl_pet_type (
pet_type_id INT AUTO_INCREMENT PRIMARY KEY,
pet_type_name VARCHAR(100) NOT NULL
);

-- Create tbl_pet_owner
CREATE TABLE tbl_pet_owner (
pet_owner_id INT AUTO_INCREMENT PRIMARY KEY,
pet_owner_name VARCHAR(100) NOT NULL,
pet_owner_contact VARCHAR(15),
pet_owner_email VARCHAR(100),
pet_owner_address VARCHAR(255),
pet_owner_profile TEXT,
pet_owner_username VARCHAR(50) UNIQUE NOT NULL,
pet_owner_password VARCHAR(255) NOT NULL
);

-- Create tbl_adopter
CREATE TABLE tbl_adopter (
adopter_id INT AUTO_INCREMENT PRIMARY KEY,
adopter_name VARCHAR(100) NOT NULL,
adopter_contact VARCHAR(15),
adopter_email VARCHAR(100),
adopter_address VARCHAR(255),
adopter_profile TEXT,
adopter_username VARCHAR(50) UNIQUE NOT NULL,
adopter_password VARCHAR(255) NOT NULL
);

-- Create tbl_pet
CREATE TABLE tbl_pet (
pet_id INT AUTO_INCREMENT PRIMARY KEY,
pet_owner_id INT,
pet_name VARCHAR(100) NOT NULL,
pet_type_id INT,
description TEXT,
age INT,
gender ENUM('Male', 'Female'),
health_status ENUM('Healthy', 'Needs Treatment') NOT NULL,
upload_health_history TEXT,
vaccination_status ENUM('Vaccinated', 'Not Vaccinated') NOT NULL,
proof_of_vaccination TEXT,
adoption_status ENUM('Available', 'Pending', 'Adopted') NOT NULL,
date_registered DATE,
FOREIGN KEY (pet_owner_id) REFERENCES tbl_pet_owner(pet_owner_id),
FOREIGN KEY (pet_type_id) REFERENCES tbl_pet_type(pet_type_id)
);

-- Create tbl_pet_media
CREATE TABLE tbl_pet_media (
pet_media_id INT AUTO_INCREMENT PRIMARY KEY,
pet_id INT,
pet_media_name VARCHAR(255),
pet_media_url VARCHAR(255),
FOREIGN KEY (pet_id) REFERENCES tbl_pet(pet_id)
);

-- Create tbl_adoption_request
CREATE TABLE tbl_adoption_request (
adoption_request_id INT AUTO_INCREMENT PRIMARY KEY,
pet_id INT,
adopter_id INT,
request_date DATE,
status ENUM('Pending', 'Approved', 'Rejected') NOT NULL,
approval_date DATE,
remarks TEXT,
user_id INT,
FOREIGN KEY (pet_id) REFERENCES tbl_pet(pet_id),
FOREIGN KEY (adopter_id) REFERENCES tbl_adopter(adopter_id)
);

-- Create tbl_adoption
CREATE TABLE tbl_adoption (
adoption_id INT AUTO_INCREMENT PRIMARY KEY,
pet_id INT,
adopter_id INT,
adoption_date DATE,
upload_adoption_document TEXT,
remarks TEXT,
user_id INT,
FOREIGN KEY (pet_id) REFERENCES tbl_pet(pet_id),
FOREIGN KEY (adopter_id) REFERENCES tbl_adopter(adopter_id)
);

Note:

• Adjust the data types and lengths of the fields as needed based on your specific requirements.
• For password storage, consider using a strong hashing algorithm like bcrypt or Argon2.
• If you’re using a user management system, you might want to reference a separate users table for the user_id in tbl_adoption_request and tbl_adoption.

These SQL statements will create the necessary tables with their corresponding columns and relationships for your pet adoption database.

FREE DOWNLOAD SQL

Summary

This tutorial provides a comprehensive guide to designing and implementing a pet adoption database. It covers essential components such as database structure, relationships between tables, and SQL statements for creating the tables. The database design includes tables for pet types, pets, pet owners, adopters, adoption requests, and adoptions. By following the steps outlined in this tutorial, you can create a robust and efficient database to manage the pet adoption process effectively.

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.