Project: Pet Adoption | Part 2

 

Part 6: Database Schema Design

1. Tables

Here’s a basic schema for the Pet Adoption Platform:

Table Name	Columns
users	id (PK), username, password, email, role (USER/SHELTER/ADMIN), created_at, updated_at
pets	id (PK), name, type (dog/cat/other), breed, age, gender, description, image_url, shelter_id (FK), availability_status, created_at, updated_at
adoption_requests	id (PK), pet_id (FK), user_id (FK), status (pending/approved/rejected), message, submitted_at
shelters	id (PK), name, location, contact_email, user_id (FK), created_at, updated_at

2. Relationships

• users table links to shelters and adoption_requests via user_id.
• pets table links to shelters via shelter_id.
• adoption_requests links to users and pets.

3. Example Database

For simplicity, use MySQL. Example SQL script for table creation:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role ENUM('USER', 'SHELTER', 'ADMIN') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE pets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    breed VARCHAR(100),
    age INT,
    gender ENUM('Male', 'Female'),
    description TEXT,
    image_url VARCHAR(255),
    shelter_id INT NOT NULL,
    availability_status ENUM('Available', 'Adopted') DEFAULT 'Available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (shelter_id) REFERENCES users (id)
);

CREATE TABLE adoption_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pet_id INT NOT NULL,
    user_id INT NOT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    message TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pet_id) REFERENCES pets (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE shelters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    contact_email VARCHAR(100) UNIQUE,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

---

Part 7: API Design

1. Authentication APIs

Endpoint	Method	Description	Authorization
/auth/register	POST	Register a new user	Public
/auth/login	POST	User login with JWT token	Public

2. Pet APIs

Endpoint	Method	Description	Authorization
/pets	GET	Fetch all pets	Public
/pets/{id}	GET	Fetch pet details by ID	Public
/pets	POST	Add a new pet (for shelters)	Shelter/Admin
/pets/{id}	PUT	Update pet details	Shelter/Admin
/pets/{id}	DELETE	Delete a pet	Shelter/Admin

3. Adoption Request APIs

Endpoint	Method	Description	Authorization
/adoptions	POST	Submit a new adoption request	User
/adoptions/{id}	PUT	Update adoption request status	Shelter/Admin
/adoptions/{id}	GET	Fetch adoption request details	Shelter/Admin/User

---

Part 8: Testing Plan

1. Unit Tests

• Frontend: Test React components using Jest and React Testing Library.
• Backend: Use JUnit and Mockito to test services, controllers, and repositories.

2. Integration Tests

• Frontend: Test API integrations using tools like Postman or Swagger.
• Backend: Validate API responses and database interactions.

3. End-to-End Tests

• Use tools like Cypress or Selenium to simulate user workflows (e.g., login, browsing pets, submitting adoption requests).

---

Part 9: Deployment

1. Frontend

• Use Netlify or Vercel for deployment. Build the project using npm run build and upload the static files.

2. Backend

• Deploy the Spring Boot application to AWS EC2 or Heroku.
• Use Docker to containerize the application for portability.

3. Database

• Use AWS RDS for hosting the MySQL database.

---

Part 10: Future Enhancements

• AI Integration: Recommend pets based on user preferences.
• Chat Feature: Real-time chat between adopters and shelters.
• Mobile App: Extend functionality to mobile devices.
• Payment Gateway: Collect donations for shelters.
• Analytics Dashboard: Insights for shelters about adoption rates.