ERModel v01

Diagram

Entities

User

This is the core entity that represents every person registered on PetSitter. It acts as a supertype for Admin/PetOwner/PetSitter so we can store the common attributes only in User.

Candidate keys:

• user_id - Primary key; numeric id, most efficient for indexing
• username - unique but not as efficient as user_id
• email - unique but not as efficient as user_id

Attributes:

• user_id - Numeric (Required, Unique, Auto-generated integer).

• username - Text (Required, Unique, 4-20 characters).

• first_name - Text (Required).

• last_name - Text (Required).

• password - Text (Required, Hashed format, Min. 8 characters).

• email - Text (Required, Unique, Standard email format).

---

Admin

Subtype/specialization of User entity. Inherits from User. Has administrator privileges.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

PetOwner

Subtype/specialization of User entity. Inherits from User. Is able to make bookings for their Pet and request a PetSitter.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

PetSitter

Subtype/specialization of User entity. Inherits from User. Provides services to PetOwner. Is able to confirm or deny bookings.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

Pet

The animal that will be taken care of. Track the identity, special needs, age of the pet in the booking. Linked to PetType to categorize animals more efficiently.

Candidate keys:

• pet_id - Primary key; Unique surrogate key.

Attributes:

• pet_id - Numeric (Required, Unique, Auto-generated integer).

• name - Text (Required).

• photo - Text/URL (Optional, URL format).

• age - Numeric (Required, non-negative integer).

• password - Text (Required, Hashed format, Min. 8 characters).

• special_needs - Text (Optional).

• description - Text (Optional).

---

PetType

Entity used to categorize pets (for example Cat, Dog, Rabbit, Bird). Used for storing attributes that would repeat a lot of times in one single Pet entity.

Candidate keys:

• pet_id - Primary key; Unique surrogate key.
• species - Unique but not as efficient as a numeric id key.

Attributes:

• pettype_id - Numeric (Required, Unique, Auto-generated integer).

• species - Text (Required, Unique).

• average_lifespan - Numeric (Optional).

• needs_outdoor_walk - Boolean (Required, 0 or 1).

---

Booking

The entity that connects PetOwner, PetSitter, Pet and the services requested for the Pet

Candidate keys:

• booking_id - Primary key; Unique surrogate key.

Attributes:

• booking_id - Numeric (Required, Unique, Auto-generated integer).

• status - Text (Required, for example 'Pending', 'Confirmed', 'Completed', 'Rejected').

• date_from - Date (Required).

• date_to - Date (Required).

• address - Text (Required, location of the service).

---

Review

The feedback that the PetOwner leaves for the PetSitter after a completed Booking. This is mandatory to maintain trust and establish quality on the platform.

Candidate keys:

• review_id - Primary key; Unique surrogate key.

Attributes:

• review_id - Numeric (Required, Unique, Auto-generated integer).

• rating - Numeric (Required, Integer from 1 to 5).

• comment - Text (Optional).

---

Service

Defines the type of care offered for the Pet, for example walking, overnight stay or short visit. If needed new services can be added without changing the database schema.

Candidate keys:

• service_id - Primary key; Unique surrogate key.

Attributes:

• service_id - Numeric (Required, Unique, Auto-generated integer).

• type - Text (Required).

• description - Text (Optional).

---

Payment

Defines the financial transaction related to a Booking.

Candidate keys:

• payment_id - Primary key; Unique surrogate key.

Attributes:

• payment_id - Numeric (Required, Unique, Auto-generated integer).

• amount - Numeric (Required, positive integer > 0).

• payment_type - Text (Required, for example Cash or Card).

Relations

manage - Admin to User

• M:N relation
• an Admin can manage multiple users, and a User can be managed by multiple admins.

---

leaves - PetOwner to Review

• 1:N relation
• one PetOwner can leave many reviews to sitters, but each Review has one unique owner.

---

receives - Review to PetSitter

• 1:N relation
• one PetSitter can receive many reviews from clients, but each Review is meant for one unique PetSitter.

---

is_a - Pet to PetType

• M:1 relation
• many Pets can have one PetType (for example Cat), but each Pet has exactly one type.

---

is_for - Pet to Booking

• N:N relation
• a Booking can have two pets from the same owner, while a Pet can be part of many bookings during its life.

---

books - PetOwner to Booking

• 1:N relation
• a PetOwner can schedule many bookings, but each Booking is created by and owned by one unique PetOwner.

---

is_booked - Booking to PetSitter

• 1:N relation
• a PetSitter can be booked for many bookings, but each Booking is assigned to only one PetSitter at a time.

---

provides - PetSitter to Service

• M:N relation
• a PetSitter can provide multiple services (Walking, Overnight stay) and a Service (Walking) can be provided by many different sitters.

---

requires - Booking to Service

• M:N relation
• a Booking can require multiple services (Walking, Overnight stay) and a specific Service type can be requested in many different bookings.

---

charged - Booking to Payment

• 1:1 relation
• each Booking has exactly one Payment record, and each Payment record is associated with exactly one Booking