= ERModel v02 == Diagram [[Image(ERModel_v02.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). * {{{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:''' * {{{pettype_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 optional but it helps 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. ---- {{{owns - PetOwner to Pet}}} * 1:N relation * one {{{PetOwner}}} can register multiple pets, but each {{{Pet}}} belongs to one owner. ---- {{{produces - Booking to Review}}} * 1:1 relation * one {{{Booking}}} produces max. one {{{Review}}} from a client. ---- {{{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}}} == Version history [https://develop.finki.ukim.mk/projects/petsitter/wiki/ConceptualModel_v01 v01] * Initial version [https://develop.finki.ukim.mk/projects/petsitter/wiki/ConceptualModel v02] * Current version * Added '''owns''' relation between {{{PetOwner}}} and {{{Pet}}}; so each pet can be assigned to an owner * Added '''produces''' relation between {{{Booking}}} and {{{Review}}}; so each review can be associated directly with only a booking * Removed redundant '''leaves''' relation between {{{PetOwner}}} and {{{Review}}} * Removed redundant '''receives''' relation between {{{PetSitter}}} and {{{Review}}}