Changes between Initial Version and Version 1 of ERModel

Timestamp:

07/08/26 01:49:18 (11 hours ago)

Author:

181201

Comment:

--

ERModel

@@ +1 @@
+= ERModel
+
+== Diagram
+[[Image(ERModel_v02.png)]]
+
+== 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.
+* Partial participation on Admin, Partial participation on User
+
+----
+
+{{{owns - PetOwner to Pet}}}
+* 1:N relation 
+* one {{{PetOwner}}} can register multiple pets, but each {{{Pet}}} belongs to one owner.
+* Partial participation on PetOwner, Total participation on Pet
+
+----
+
+{{{produces - Booking to Review}}}
+* 1:1 relation
+* one {{{Booking}}} produces max. one {{{Review}}} from a client.
+* Partial participation on Booking, Total participation on Review
+
+
+----
+
+{{{is_a - Pet to PetType}}}
+* M:1 relation
+* many Pets can have one {{{PetType}}} (for example Cat), but each {{{Pet}}} has exactly one type.
+* Total participation on Pet, Partial participation on PetType
+
+----
+
+{{{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.
+* Partial participation on Pet, Total participation on Booking
+
+----
+
+{{{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}}}.
+* Partial participation on PetOwner, Total participation on Booking
+
+----
+
+{{{is_booked - PetSitter to Booking}}}
+* 1:N relation
+* a {{{PetSitter}}} can be booked for many bookings, but each {{{Booking}}} is assigned to only one {{{PetSitter}}} at a time.
+* Partial participation on PetSitter, Total participation on Booking
+
+----
+
+{{{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.
+* Total participation on PetSitter, Partial participation on Service
+
+----
+
+{{{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.
+* Total participation on Booking, Partial participation on Service
+
+----
+
+{{{charged - Booking to Payment}}}
+* 1:1 relation
+* each {{{Booking}}} has exactly one {{{Payment}}} record, and each {{{Payment}}} record is associated with exactly one {{{Booking}}}
+* Partal participation on Booking, Total participation on Payment
+
+== 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}}}