== ERModel Current version
== Diagram ==
[[Image(ERModel_v02.jpg)]]
== Data requirements == 
=== ENTITIES ===
{{{User}}}
[[BR]]
User represents every account in the system (admins and clients). It is the supertype in the user-hierarchy, holding all shared identity and login data. Modeling it as a separate entity avoids duplicating attributes in Admin/ Client/ Owner and makes authorization logic easier. 
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{user_id}}} – surrogate numeric identifier (chosen primary key).
* {{{username}}} – natural key, must be unique, but can change in the future.
* {{{email}}} – also unique, but might change and can be reused if a user is deleted.
{{{user_id}}} is chosen as PK because it is stable, short, and independent of business rules.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{user_id}}} – numeric, required. Auto-generated positive integer; primary key.
* {{{username}}} – text, required. Unique; max length ~30; only letters, digits and some symbols.
* {{{fullname}}} – text, required. Free-text full name.
* {{{name}}} – text, required. First name; max length ~50.
* {{{surname}}} – text, required. Last name; max length ~50.
* {{{email}}} – text, required. Must be unique; email format validation.
* {{{created_at}}} – datetime, required. Automatically set when the account is created.
[[BR]]
----
{{{Admin}}}
[[BR]]
Admin is a specialization of User representing accounts with administrative privileges (approving clinics, moderating listings, etc.). 
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{user_id}}} - inherited from User, serves as primary key and foreign key to {{{User}}}
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* No additional attributes beyond those of User.
[[BR]]
----
{{{Client}}}
[[BR]]
Client is the specialization for “regular” platform users (people who interact with clinics and animals). It is modeled as a subtype to distinguish them from Admins, while still sharing the common User attributes.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{user_id}}} - inherited from User, serves as primary key and foreign key to {{{User}}}
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* No additional attributes beyond those of User.
[[BR]]
----
{{{Owner}}}
[[BR]]
Owner is a client who actually has animals registered in the system. It’s modeled as a subtype of Client to explicitly express that only some clients own pets, and to support constraints like “only owners may book appointments”.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{user_id}}} - inherited, primary key and FK to {{{Client}}} (and transitively User).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* No additional attributes.
[[BR]]
----
{{{Listing}}}
[[BR]]
Represents an advertisement where a user offers an animal for sale. It is needed to separate the offer from the animal itself (the same animal can appear in multiple listings over time, and a user can create many listings). It is modeled with its own status and price attributes because these can change independently of the animal data.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{listing_id}}} – surrogate numeric identifier (primary key).
No realistic natural key exists that is stable and unique, so we only use {{{listing_id}}}.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{listing_id}}} – numeric, required. Auto-generated primary key.
* {{{status}}} – text, required. Allowed values like {ACTIVE, RESERVED, SOLD}.
* {{{created_at}}} – datetime, required. Date and time when the listing was created. Automatically set.
* {{{price}}} – numeric, required. Must be > 0.
* {{{description}}} – text, optional. Free-text; length limit ~ 1000 characters.
[[BR]]
----
{{{Animal}}}
[[BR]]
Represents each individual pet in the system. Needed to track identity, species, breed, owner, and health data independently from listings. It is modeled as a separate entity because health records, appointments, and ownership relate to the animal itself, not just to an advertisement.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
 * {{{animal_id}}} – surrogate numeric identifier (primary key).
{{{animal_id}}} is chosen as PK for simplicity and stability.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{animal_id}}} – numeric, required. Auto-generated primary key.
* {{{owner_id}}} – numeric, required. Foreign key to User.user_id; must reference an existing user.
* {{{species}}} – text, required. Choose from predefined set.
* {{{type}}} – text, required.
* {{{breed}}} – text, required. Breed name; may be “mixed/unknown”.
* {{{name}}} – text, required. Pet name; max length ~50.
* {{{sex}}} – text, required. {MALE, FEMALE, UNKNOWN}.
* {{{date_of_birth}}} – date, required. Must not be in the future.
* {{{located}}} – text, required. City/area; free text.
* {{{photo_url}}} – text, required. URL to main photo; must be valid URL format.
[[BR]]
----
{{{VetClinic}}}
[[BR]]
Represents veterinary clinics that cooperate with the platform. It is needed so animals, appointments, and reviews can be associated with a specific clinic. It is modeled as a separate entity because clinics are independent organizational units with their own attributes and can be referenced by many animals, appointments, and reviews.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{clinic_id}}} – surrogate numeric identifier (primary key).
* {{{name + address}}} could be a natural key, but both may change over time, so they are not used as the primary key.
{{{clinic_id}}} is chosen as PK for stability, simplicity, and immutability.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{clinic_id}}} – numeric, required. Auto-generated primary key.
* {{{name}}} – text, required. Clinic name; max length ~100.
* {{{location}}} – text, required. Region or district name.
* {{{address}}} – text, optional. Street address; max length ~200.
* {{{city}}} – text, required.
* {{{email}}} – text, optional. Must be valid email format; must be unique.
* {{{phone}}} – text, optional. May require a specific phone-number format.
* {{{approved_by}}} - text, which admin approved to have that clinic as a part of the app
[[BR]]
----
{{{Appointment}}}
[[BR]]
Represents a scheduled veterinary visit for a specific animal. Required to organize veterinary care, track upcoming visits, and link visits to clinics and generated health records. It is modeled as a standalone entity because appointments are time-based events that may create one or more medical entries.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''

* {{{appointment_id}}} – surrogate numeric identifier (primary key).
* A composite natural key like ({{{animal_id}}}, {{{date_time}}}, {{{clinic_id}}}) could be considered, but it is complex and not guaranteed unique.
{{{appointment_id}}} is chosen as the PK because it is simple, stable, and unambiguous.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{appointment_id}}} – numeric, required. Auto-generated primary key.
* {{{date_time}}} – datetime, required. Must represent a valid future or past appointment date.
* {{{status}}} – text, required. Allowed values: {SCHEDULED, COMPLETED, CANCELLED, NO_SHOW}.
* {{{notes}}} – text, optional. Additional remarks from the owner or vet.
* {{{animal_id}}} - numeric, required. Foreign key to Animal.animal_id; the animal this appointment is for.
[[BR]]
----
{{{HealthRecord}}}
[[BR]]
Represents a single medical action or observation (vaccination, treatment, surgery, deworming, check-up) recorded for a specific animal. It is required to maintain a complete medical history. Modeled as its own entity because multiple medical actions may occur within one appointment, and each animal can accumulate records over its lifetime.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{healthrecord_id}}} – surrogate numeric identifier (primary key).
* A possible natural key ({{{animal_id}}}, {{{date}}}, {{{type}}}) is not reliable because multiple actions of the same type may occur on the same day.
{{{healthrecord_id}}} is chosen as PK for simplicity and uniqueness.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{healthrecord_id}}} – numeric, required. Auto-generated primary key.
* {{{animal_id}}} – numeric, required. Foreign key to Animal.animal_id.
* {{{type}}} – text, required. Allowed values include {VACCINATION, SURGERY, DEWORMING, CHECKUP}.
* {{{description}}} – text, optional. Details of the treatment or observation.
* {{{date}}} – date, required. Must be a valid date; must not be in the future for completed actions.
[[BR]]
----
{{{Review}}}
[[BR]]
Represents feedback written by a user about either another user (e.g., a seller) or a veterinary clinic. It is essential for trust, transparency, and reputation. 
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{review_id}}} – surrogate numeric identifier (primary key).
* A compound natural key such as ({{{reviewer_id}}}, {{{target_user_id}}}, {{{created_at}}}) is possible but not guaranteed unique.
{{{review_id}}} is chosen as PK for simplicity and consistency.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{review_id}}} – numeric, required. Auto-generated primary key.
* {{{comment}}} – text, optional. Free-text comment; up to ~1000 characters.
* {{{reviewer_id}}} – numeric, required. Foreign key to User.user_id.
* {{{created_at}}} – datetime, required. Time of submission.
* {{{rating}}} – numeric, required. Integer 1–5; enforce interval [1, 5].
'''[[span(style=color: #E30B5C, Special constraint: )]]'''
At least one of {{{target_user_id}}} or {{{target_clinic_id}}} must be NOT NULL (exactly one in normal operation).
[[BR]]
----
{{{UserReview}}}
[[BR]]
UserReview is the specialization of Review used when a review is directed at a user (for example another owner, sitter, or vet user). It is modeled as a subtype of Review so that all generic review attributes (text, rating, author, time) come from the parent, while UserReview adds only the information specific to “reviews about users”, namely who the target user is.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{review_id}}} – inherited from Review and used as both primary key and foreign key to {{{Review.review_id}}}.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{target_user_id}}} – required; Foreign key to User.user_id; identifies the user being reviewed. Must reference an existing user account.
[[BR]]
----
{{{ClinicReview}}}
[[BR]]
ClinicReview is the specialization of Review for feedback that targets a veterinary clinic. It is a subtype of Review for the same reasons as UserReview: all generic review data is stored once in Review, while ClinicReview adds only the clinic being reviewed. This allows you to attach the special business rule “a user may review a clinic only if they have at least one DONE appointment at that clinic” specifically to ClinicReview without complicating user reviews.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{review_id}}} – inherited from Review; primary key of ClinicReview and foreign key to {{{Review.review_id}}}
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{target_clinic_id}}} – required; Foreign key to User.user_id; identifies the clinic being reviewed. Must reference an existing clinic.
[[BR]]
----
{{{Notification}}}
[[BR]]
Represents a system-generated message delivered to a user (e.g., appointment reminders, listing updates, new reviews). It is necessary for timely communication and platform usability. It is modeled as an entity linked to a single user because each notification is specific to one recipient.
[[BR]]
'''[[span(style=color: #E30B5C, Candidate keys: )]]'''
* {{{notification_id}}} – surrogate numeric identifier (primary key).
No natural candidate key exists because notifications are numerous and non-unique.
{{{notification_id}}} is chosen as a primary key.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
* {{{notification_id}}} – numeric, required. Auto-generated primary key.
* {{{type}}} – text, required. Allowed values include {APPOINTMENT_REMINDER, NEW_REVIEW, LISTING_STATUS}.
* {{{created_at}}} – datetime, required. When the notification was generated.
* {{{message}}} – text, required. Human-readable notification body.
* {{{user_id}}} – numeric, required. Foreign key to User.user_id (recipient).
* {{{is_read}}} – boolean, required. Defaults to FALSE; becomes TRUE when viewed.
=== RELATIONS ===
[[BR]]
{{{receives (User – Notification)}}}
[[BR]]
Represents that a specific user receives specific notifications. It is needed so each notification is clearly assigned to one user. The relationship is modeled as 1–N: one user can receive many notifications; each notification belongs to exactly one user.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{notification_id}}} (PK of Notification).
* On the relationship level, the pair ({{{user_id}}}, {{{notification_id}}}) is unique.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
No extra attributes apart from the foreign keys already listed: {{{user_id}}} in Notification.
----
[[BR]]
{{{made_by (Owner – Listing)}}}
[[BR]]
Links each listing to the user who created it. It is needed to track authorship, rights to modify the listing, and contact information. Modeled as 1–N because one user can create many listings, but each listing is created by exactly one user.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{listing_id}}}.
* The pair ({{{user_id}}}, {{{listing_id}}}) is unique.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Relationship implemented via foreign key {{{user_id}}} inside Listing.
[[BR]]
----
{{{made_for (Listing – Animal)}}}
[[BR]]
Shows that a listing is created for a specific animal. It is needed to attach each advertisement to the animal being sold or adopted. Modeled as 1–N from Animal to Listing: one animal may appear in multiple listings, but each listing refers to exactly one animal.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{listing_id}}}.
* The combination ({{{listing_id}}}, {{{animal_id}}}) is unique.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Relationship implemented through the foreign key {{{animal_id}}} in Listing.
[[BR]]
----
{{{owns (Owner – Animal)}}}
[[BR]]
Represents ownership of animals. It ensures that only the rightful owner can manage listings or initiate health-related actions. Modeled as 1–N: one user can own many animals, while each animal has exactly one current owner.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{animal_id}}}.
* Unique pair ({{{owner_id}}}, {{{animal_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{owner_id}}} is stored in Animal.
----
[[BR]]
{{{approves (Admin – Listing)}}}
[[BR]]
Represents that a admin approves a vetclinic to be a part of the system.
----
[[BR]]
{{{person_responsible (Owner - Appointment)}}}
[[BR]]
Represents that an Appoinment needs to have by whom it was created by(who is the owner of the animal in that appointment).
----
[[BR]]
{{{writes (User – Review)}}}
[[BR]]
Represents that a user writes a review. Critical for establishing authorship and preventing anonymous reviews. Modeled as 1–N: one user can write many reviews, while each review has exactly one author.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{review_id}}}.
* Unique pair ({{{reviewer_id}}}, {{{review_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{reviewer_id}}} appears in Review.
----
[[BR]]
{{{user_reviewed_in (User – UserReview)}}}
[[BR]]
Represents that a user is the target of a review (e.g., a seller rated by a buyer). Needed for calculating user reputation. Modeled as 1–N: one user can receive many reviews, while each review may refer to one target user.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{review_id}}}.
* The pair ({{{target_user_id}}}, {{{review_id}}}) is unique.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{target_user_id}}} in Review (may be NULL when reviewing a clinic).
----
[[BR]]
{{{clinic_reviewed_in (VetClinic – ClinicReview)}}}
[[BR]]
Represents that a clinic is the target of a review. This supports public ratings of veterinary clinics. Modeled as 1–N: one clinic can receive many reviews; each review may refer to a single clinic.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{review_id}}}.
* Unique pair ({{{target_clinic_id}}}, {{{review_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{target_clinic_id}}} stored in Review (NULL if review is about a user).
----
[[BR]]
{{{belongs_to (Animal – HealthRecord)}}}

[[BR]]
Connects each medical record entry to the animal it describes. It is essential for maintaining a full medical history. Modeled as 1–N: one animal has many health records, while each health record refers to one animal.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{healthrecord_id}}}.
* Unique pair ({{{animal_id}}}, {{{healthrecord_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{animal_id}}} is stored in HealthRecord.
----
[[BR]]
{{{at_appointment (Appointment – HealthRecord)}}}

[[BR]]
Represents that a health record entry was created during a specific appointment. Needed to link treatments and checkups to their corresponding visits. Modeled as 1–N: one appointment may generate many records; each record belongs to at most one appointment.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{healthrecord_id}}}.
* Unique pair ({{{appointment_id}}}, {{{healthrecord_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{appointment_id}}} included in HealthRecord (may be NULL if manually added).
----
[[BR]]
{{{at_what_clinic (VetClinic – Appointment)}}}
[[BR]]
Represents at which veterinary clinic a given appointment takes place. This allows the system to know the location of each visit, show a clinic’s schedule, and coordinate resources. Modeled as 1–N: one clinic can host many appointments, while each appointment is held at exactly one clinic.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{appointment_id}}}.
* Unique pair ({{{clinic_id}}}, {{{appointment_id}}}).
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
Foreign key {{{clinic_id}}} stored in {{{Appointment}}}.
----
[[BR]]
{{{for_animal (Animal – Appointment)}}}
[[BR]]
Represents which animal a given appointment is scheduled for. It is needed so every appointment is clearly tied to a specific pet, enabling the system to show that pet’s upcoming visits and history. The relationship is modeled as 1–N from Animal to Appointment: one animal can have many appointments over time, while each appointment refers to exactly one animal.
[[BR]]
'''[[span(style=color: #E30B5C, Keys: )]]'''
* Identified by {{{appointment_id}}} (PK of Appointment).
* On the relationship level, the pair ({{{animal_id}}}, {{{appointment_id}}}) is unique.
[[BR]]
'''[[span(style=color: #E30B5C, Attributes: )]]'''
No extra attributes apart from the foreign key {{{animal_id}}} stored in {{{Appointment}}}.
----
[[BR]]
{{{based_on (ClinicReview – Appointment)}}}
[[BR]]
Represents that a user can't leave a review for a clinic if he hasn't had a appointment there.

== ERModel History
VersionOne - Added specialization of the User entity(User became a supertype and two subtypes were added),added specialization of the Review entity, added a relationship between Owner and Appointment, added a business rule (constraint) for clinic reviews