ERModel Current version

Diagram

Data requirements

ENTITIES

User
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.
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.
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.

Listing
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.
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.
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.

Animal
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.
Candidate keys:

• animal_id – surrogate numeric identifier (primary key).

animal_id is chosen as PK for simplicity and stability.
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.

VetClinic
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.
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.
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.

Appointment
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.
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.
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.

HealthRecord
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.
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.
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.

Review
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.
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.
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].

Special constraint: At least one of target_user_id or target_clinic_id must be NOT NULL (exactly one in normal operation).
Notification
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.
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.
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