Changes between Initial Version and Version 1 of ConceptualModel_v01

Timestamp:

05/05/26 20:08:17 (2 days ago)

Author:

181201

Comment:

--

ConceptualModel_v01

@@ +1 @@
+= 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}}}