== ERModel Current version
== Diagram ==
[[Image(ERModel_v01.png)]]
== 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”.
* {{{status}}} – text, required. Choosed from a predefined set{AVAILABLE,IN_TREATMENT,???}.
* {{{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.
[[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.
== ERModel History