Scribd
Upload
74 views3 pages

Pet Adoption System SQL Tables

The document outlines the SQL table creation for a Pet Adoption System, including tables for users, pets, adoptions, shelters, pet types, breeds, appointments, medical records, messages, and reviews. Each table is defined with relevant fields and relationships through foreign keys. The structure supports functionalities such as user management, pet information, adoption processes, and communication between users.

Uploaded by

Pʀᴀᴠᴇᴇɴ Bᴀʟᴀᴊɪ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
74 views3 pages

Pet Adoption System SQL Tables

The document outlines the SQL table creation for a Pet Adoption System, including tables for users, pets, adoptions, shelters, pet types, breeds, appointments, medical records, messages, and reviews. Each table is defined with relevant fields and relationships through foreign keys. The structure supports functionalities such as user management, pet information, adoption processes, and communication between users.

Uploaded by

Pʀᴀᴠᴇᴇɴ Bᴀʟᴀᴊɪ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Pet Adoption System - Table Creation SQL

-- Table: users
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
password_hash VARCHAR(255),
phone_number VARCHAR(20),
address VARCHAR(255),
user_type ENUM('adopter', 'shelter_staff', 'admin')
);

-- Table: pets
CREATE TABLE pets (
pet_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
pet_type_id INT,
breed_id INT,
age INT,
gender ENUM('Male', 'Female'),
shelter_id INT,
FOREIGN KEY (pet_type_id) REFERENCES pet_types(pet_type_id),
FOREIGN KEY (breed_id) REFERENCES breeds(breed_id),
FOREIGN KEY (shelter_id) REFERENCES shelters(shelter_id)
);

-- Table: adoptions
CREATE TABLE adoptions (
adoption_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
pet_id INT,
adoption_date DATE,
status ENUM('pending', 'approved', 'rejected'),
remarks TEXT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (pet_id) REFERENCES pets(pet_id)
);

-- Table: shelters
CREATE TABLE shelters (
shelter_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
location VARCHAR(255),
phone_number VARCHAR(20),
email VARCHAR(100),
operating_hours VARCHAR(50)
);

-- Table: pet_types
CREATE TABLE pet_types (
pet_type_id INT AUTO_INCREMENT PRIMARY KEY,
type_name VARCHAR(50),
description TEXT
);

-- Table: breeds
CREATE TABLE breeds (
breed_id INT AUTO_INCREMENT PRIMARY KEY,
breed_name VARCHAR(100),
pet_type_id INT,
size_category ENUM('Small', 'Medium', 'Large'),
description TEXT,
FOREIGN KEY (pet_type_id) REFERENCES pet_types(pet_type_id)
);

-- Table: appointments
CREATE TABLE appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
pet_id INT,
appointment_date DATE,
status ENUM('scheduled', 'completed', 'cancelled'),
notes TEXT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (pet_id) REFERENCES pets(pet_id)
);

-- Table: medical_records
CREATE TABLE medical_records (
record_id INT AUTO_INCREMENT PRIMARY KEY,
pet_id INT,
exam_date DATE,
vaccination_status VARCHAR(100),
notes TEXT,
veterinarian_name VARCHAR(100),
FOREIGN KEY (pet_id) REFERENCES pets(pet_id)
);
-- Table: messages
CREATE TABLE messages (
message_id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT,
receiver_id INT,
message_content TEXT,
sent_at DATETIME,
read_status ENUM('read', 'unread'),
FOREIGN KEY (sender_id) REFERENCES users(user_id),
FOREIGN KEY (receiver_id) REFERENCES users(user_id)
);

-- Table: reviews
CREATE TABLE reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
shelter_id INT,
rating INT CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at DATETIME,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (shelter_id) REFERENCES shelters(shelter_id)
);

You might also like