= ERModel v01 == Diagram [[Image(ERModel_v01.jpg)]] == Entities {{{User}}} This is the core entity that represents every person registered on [https://develop.finki.ukim.mk/projects/petsitter/#PetSitter 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}}}