== ERModel Current version
== Diagram ==
[[Image(ERModel_v02.jpg)]]
== Data requirements == 
=== ENTITIES ===
{{{User}}}
[[BR]]
Represents every person using the platform: buyers, sellers, and administrators. It is needed to authenticate users, assign roles, and link their actions (listings, reviews, notifications, ownership of animals). It is modeled as a single entity with a role attribute instead of separate tables for buyers/sellers/admins, because one person can play multiple roles over time.
[[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.
* {{{role}}} – text, required. Limited set of values: {ADMIN, USER, GUEST}; implemented as enum.
* {{{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]]
----
{{{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.
[[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. It is modeled with two optional foreign keys — {{{target_user_id}}} and {{{target_clinic_id}}} — so that a single structure supports reviewing people and organizations. A design constraint ensures that at least one of them must be non-null.
[[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.
* {{{target_user_id}}} – numeric, optional. FK to User.user_id. Must be non-null when review is about a user.
* {{{target_clinic_id}}} – numeric, optional. FK to VetClinic.clinic_id. Must be non-null when review is about a clinic.
* {{{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]]
----
{{{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 (User – 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 (User – 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]]
{{{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 – Review)}}}
[[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 – Review)}}}
[[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}}}.
== ERModel History