Why Use a Database for Pet Adoption?

  • Stay organized: Manage pets, adoptions, shelters, health records, and system data from a centralized location.
  • Minimize errors: Validate data integrity using primary keys, foreign keys, and unique constraints.
  • Answer business queries: Instantly determine which pets are available or trace the full adoption history of any animal.
Pet Adoption System Database Banner

All Tables in This Database (Explained)

1. adopter

This table stores data about everyone who adopts or registers to adopt a pet.

Field Meaning
adopter_id Unique number for each adopter (Primary Key).
name Full name of adopter.
aadhar_number Unique national ID number (enforced with a UNIQUE constraint).
email Email address.
phone 10-digit phone number.
address Full residential address.
proof_of_residence Document identifier showing address proof.

Note: No two adopters can share the same aadhar_number due to the unique constraint on the column.

2. pet

This holds information for every animal in the system.

Field Meaning
pet_id Unique ID for the pet (Primary Key).
breed_id Which breed it is (references breed table).
shelter_id Which shelter it lives in (references shelter table).
age Age in years.
gender Male or Female.
adoption_status Current status such as available or adopted.
registration_date Date added to the system.

3. breed

All the different breeds stored in a single lookup table.

Field Meaning
breed_id Unique ID for the breed (used as Foreign Key in pet).
breed_name Breed name (for example, "Labrador" or "Persian Cat").
breed_lifespan Average lifespan in years.

4. shelter

Details for every animal shelter facility in the network.

Field Meaning
shelter_id Unique shelter number (used as Foreign Key in pet).
shelter_name Name of the shelter.
shelter_address Full physical address.
registration_number Unique state or government registration code for the shelter.

5. adoption

Tracks each transaction when a pet is adopted by a registered individual.

Field Meaning
pet_id Which pet (Foreign Key referencing pet table).
adopter_id Who adopted (Foreign Key referencing adopter table).
date_of_adoption Timestamp of when the adoption was finalized.

Note: Each row links a pet to an adopter so that a pet cannot be adopted twice concurrently.

6. diagnosis

Medical history records for each pet when healthcare events occur.

Field Meaning
diagnosis_id Unique record for the health event (Primary Key).
pet_id Which pet (Foreign Key referencing pet table).
date_of_diagnosis Date the condition was identified.
description Clinical symptoms or issue diagnosed.
treatment Prescribed medical treatment or resolution.

How the Tables Connect (ER Diagram)

Entity Relationship Diagram for Pet Adoption Database

Data mapping flows across the schema as follows:

  • adoption: Connects each pet to an adopter along with the adoption date.
  • pet: Links directly to a specific breed and a hosting shelter.
  • diagnosis: Links every health record event back to a specific pet.

Step-by-Step Example: Alice Adopts Max

  1. Pet Registration: The shelter registers a dog named Max. Max's record is created in pet, referencing existing entries in breed and shelter.
  2. Adopter Registration: Alice registers her details, which are added into adopter alongside her contact information and identification.
  3. Adoption Execution: Alice adopts Max. A record is added to adoption combining both IDs and the current date.
  4. Medical Checkup: Max undergoes a routine wellness visit, and his treatment record is logged into diagnosis.

Sample Table Data

adopter

adopter_id name
1 Alice

pet

pet_id breed_id shelter_id status
5 2 3 adopted

adoption

pet_id adopter_id date_of_adoption
5 1 2025-07-18

diagnosis

diagnosis_id pet_id date_of_diagnosis description treatment
1 5 2025-07-20 Fleas Medication

Why This Design Works

  • Normalization: Each table stores one specific type of data to eliminate unnecessary duplication.
  • Scalability: As thousands of pets, adopters, and shelters are added, database organization remains clean.
  • Referential Integrity: Primary and Foreign key relationships prevent orphan records and streamline relational SQL queries.