If you’re just starting your journey with databases and want a concrete example, here’s a step-by-step walkthrough of a real pet adoption system built in MySQL.
We’ll explain each table, the meaning of every field, and how all tables connect—with easy diagrams and a practical, beginner-focused approach.
Why Use a Database for Pet Adoption?
- Stay organized: Manage pets, adoptions, shelters, health records, and more.
- Minimize errors: Validate data with unique IDs and constraints.
- Answer questions: Who adopted this pet? Which pets are available? All at a glance.
All Tables in This Database (Explained)
1. adopter
This table stores data about everyone who adopts or wants to adopt a pet.
| Field | Meaning |
|---|---|
| adopter_id | Unique number for each adopter |
| name | Full name of adopter |
| aadhar_number | Unique Indian ID (acts as unique constraint) |
| Email address | |
| phone | 10-digit phone number |
| address | Full residential address |
| proof_of_residence | Document showing address proof |
No two adopters can have the same aadhar_number.
2. pet
This holds info for every animal in the system.
| Field | Meaning |
|---|---|
| pet_id | Unique ID for the pet |
| breed_id | Which breed it is (references breed) |
| shelter_id | Which shelter it lives in (references shelter) |
| age | Age in years |
| gender | Male / Female |
| adoption_status | 'available', 'adopted', etc. |
| registration_date | Date added to system |
3. breed
All the different breeds in one table.
| Field | Meaning |
|---|---|
| breed_id | Unique ID for the breed (used by pet) |
| breed_name | e.g. "Labrador", "Persian Cat" |
| breed_lifespan | Average lifespan (years) |
4. shelter
Details for every animal shelter.
| Field | Meaning |
|---|---|
| shelter_id | Unique shelter number (used by pet) |
| shelter_name | Name of shelter |
| shelter_address | Full address |
| registration_number | Unique registration code for shelter |
5. adoption
Tracks each time a pet is adopted by someone.
| Field | Meaning |
|---|---|
| pet_id | Which pet (from pet table) |
| adopter_id | Who adopted (from adopter table) |
| date_of_adoption | When the adoption happened |
Each row links a pet to an adopter (a pet can't be adopted by the same person twice).
6. diagnosis
Medical records for each pet, if they get sick.
| Field | Meaning |
|---|---|
| diagnosis_id | Unique record for the health event |
| pet_id | Which pet (from pet table) it is for |
| date_of_diagnosis | Date this issue was found |
| description | What problem the pet had |
| treatment | How it was treated/resolved |
How the Tables Connect (ER Diagram)
[Adopter] ← [Adoption] → [Pet] ← [Diagnosis]
[Pet] → [Breed]
[Pet] → [Shelter]
- adoption: links each pet to an adopter and a date
- pet: links to a breed and a shelter
- diagnosis: links each health event to a pet
Beginner Example: Alice Adopts Max
-
The shelter adds a new dog named Max:
- Max’s info goes into
pet - Breed and shelter already exist for reference
- Max’s info goes into
-
Alice visits and her details are added to
adopter:- Her name, aadhar, contact info.
-
Alice adopts Max:
- Add record to
adoptionwith both IDs and the date.
- Add record to
-
Max gets a checkup and is treated for fleas:
- Health event is recorded in
diagnosis.
- Health event is recorded in
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 Is Awesome for Beginners
- Each table stores ONE type of data, so things don't get mixed up.
- Even as more pets, adopters, shelters, or health events are added, everything stays organized.
- IDs and foreign keys prevent mistakes and enable easy searching.
- This concept can be used for many real-life apps: libraries, students, inventory, and more!
Practice & Next Steps
- Try drawing the table connections on paper.
- Invent more pets and adopters, and see how records flow across tables.
- Move to MySQL and actually create these tables using
CREATE TABLEandINSERT INTOqueries!
Got stuck or want to see more code? Comment below or check out the full SQL schema!
Tags: Pet Adoption System, MySQL Database Design, Beginner Database Tutorial, SQL Schema Explained, Entity Relationship Diagram, Shelter Management, step by step
