| | 25 | [[BR]] |
| | 26 | {{{Listing}}} |
| | 27 | [[BR]] |
| | 28 | 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. |
| | 29 | [[BR]] |
| | 30 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 31 | * {{{listing_id}}} – surrogate numeric identifier (primary key). |
| | 32 | No realistic natural key exists that is stable and unique, so we only use {{{listing_id}}}. |
| | 33 | [[BR]] |
| | 34 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 35 | * {{{listing_id}}} – numeric, required. Auto-generated primary key. |
| | 36 | * {{{status}}} – text, required. Allowed values like {ACTIVE, RESERVED, SOLD}. |
| | 37 | * {{{created_at}}} – datetime, required. Date and time when the listing was created. Automatically set. |
| | 38 | * {{{price}}} – numeric, required. Must be > 0. |
| | 39 | * {{{description}}} – text, optional. Free-text; length limit ~ 1000 characters. |
| | 40 | [[BR]] |
| | 41 | {{{Animal}}} |
| | 42 | [[BR]] |
| | 43 | 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. |
| | 44 | [[BR]] |
| | 45 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 46 | * {{{animal_id}}} – surrogate numeric identifier (primary key). |
| | 47 | {{{animal_id}}} is chosen as PK for simplicity and stability. |
| | 48 | [[BR]] |
| | 49 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 50 | * {{{animal_id}}} – numeric, required. Auto-generated primary key. |
| | 51 | * {{{owner_id}}} – numeric, required. Foreign key to User.user_id; must reference an existing user. |
| | 52 | * {{{species}}} – text, required. Choose from predefined set. |
| | 53 | * {{{type}}} – text, required. |
| | 54 | * {{{breed}}} – text, required. Breed name; may be “mixed/unknown”. |
| | 55 | * {{{status}}} – text, required. Choosed from a predefined set{AVAILABLE,IN_TREATMENT,???}. |
| | 56 | * {{{name}}} – text, required. Pet name; max length ~50. |
| | 57 | * {{{sex}}} – text, required. {MALE, FEMALE, UNKNOWN}. |
| | 58 | * {{{date_of_birth}}} – date, required. Must not be in the future. |
| | 59 | * {{{located}}} – text, required. City/area; free text. |
| | 60 | * {{{photo_url}}} – text, required. URL to main photo; must be valid URL format. |
| | 61 | [[BR]] |
| | 62 | {{{VetClinic}}} |
| | 63 | [[BR]] |
| | 64 | 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. |
| | 65 | [[BR]] |
| | 66 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 67 | * {{{clinic_id}}} – surrogate numeric identifier (primary key). |
| | 68 | * {{{name + address}}} could be a natural key, but both may change over time, so they are not used as the primary key. |
| | 69 | {{{clinic_id}}} is chosen as PK for stability, simplicity, and immutability. |
| | 70 | [[BR]] |
| | 71 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 72 | * {{{clinic_id}}} – numeric, required. Auto-generated primary key. |
| | 73 | * {{{name}}} – text, required. Clinic name; max length ~100. |
| | 74 | * {{{location}}} – text, required. Region or district name. |
| | 75 | * {{{address}}} – text, optional. Street address; max length ~200. |
| | 76 | * {{{city}}} – text, required. |
| | 77 | * {{{email}}} – text, optional. Must be valid email format; must be unique. |
| | 78 | * {{{phone}}} – text, optional. May require a specific phone-number format. |
| | 79 | [[BR]] |
| | 80 | {{{Appointment}}} |
| | 81 | [[BR]] |
| | 82 | 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. |
| | 83 | [[BR]] |
| | 84 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 85 | |
| | 86 | * {{{appointment_id}}} – surrogate numeric identifier (primary key). |
| | 87 | * A composite natural key like ({{{animal_id}}}, {{{date_time}}}, {{{clinic_id}}}) could be considered, but it is complex and not guaranteed unique. |
| | 88 | {{{appointment_id}}} is chosen as the PK because it is simple, stable, and unambiguous. |
| | 89 | [[BR]] |
| | 90 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 91 | * {{{appointment_id}}} – numeric, required. Auto-generated primary key. |
| | 92 | * {{{date_time}}} – datetime, required. Must represent a valid future or past appointment date. |
| | 93 | * {{{status}}} – text, required. Allowed values: {SCHEDULED, COMPLETED, CANCELLED, NO_SHOW}. |
| | 94 | * {{{notes}}} – text, optional. Additional remarks from the owner or vet. |
| | 95 | [[BR]] |
| | 96 | {{{HealthRecord}}} |
| | 97 | [[BR]] |
| | 98 | 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. |
| | 99 | [[BR]] |
| | 100 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 101 | * {{{healthrecord_id}}} – surrogate numeric identifier (primary key). |
| | 102 | * A possible natural key ({{{animal_id}}}, {{{date}}}, {{{type}}}) is not reliable because multiple actions of the same type may occur on the same day. |
| | 103 | {{{healthrecord_id}}} is chosen as PK for simplicity and uniqueness. |
| | 104 | [[BR]] |
| | 105 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 106 | * {{{healthrecord_id}}} – numeric, required. Auto-generated primary key. |
| | 107 | * {{{animal_id}}} – numeric, required. Foreign key to Animal.animal_id. |
| | 108 | * {{{type}}} – text, required. Allowed values include {VACCINATION, SURGERY, DEWORMING, CHECKUP}. |
| | 109 | * {{{description}}} – text, optional. Details of the treatment or observation. |
| | 110 | * {{{date}}} – date, required. Must be a valid date; must not be in the future for completed actions. |
| | 111 | [[BR]] |
| | 112 | {{{Review}}} |
| | 113 | [[BR]] |
| | 114 | 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. |
| | 115 | [[BR]] |
| | 116 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 117 | * {{{review_id}}} – surrogate numeric identifier (primary key). |
| | 118 | * A compound natural key such as ({{{reviewer_id}}}, {{{target_user_id}}}, {{{created_at}}}) is possible but not guaranteed unique. |
| | 119 | {{{review_id}}} is chosen as PK for simplicity and consistency. |
| | 120 | [[BR]] |
| | 121 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 122 | * {{{review_id}}} – numeric, required. Auto-generated primary key. |
| | 123 | * {{{comment}}} – text, optional. Free-text comment; up to ~1000 characters. |
| | 124 | * {{{reviewer_id}}} – numeric, required. Foreign key to User.user_id. |
| | 125 | * {{{target_user_id}}} – numeric, optional. FK to User.user_id. Must be non-null when review is about a user. |
| | 126 | * {{{target_clinic_id}}} – numeric, optional. FK to VetClinic.clinic_id. Must be non-null when review is about a clinic. |
| | 127 | * {{{created_at}}} – datetime, required. Time of submission. |
| | 128 | * {{{rating}}} – numeric, required. Integer 1–5; enforce interval [1, 5]. |
| | 129 | '''[[span(style=color: #E30B5C, Special constraint: )]]''' |
| | 130 | At least one of {{{target_user_id}}} or {{{target_clinic_id}}} must be NOT NULL (exactly one in normal operation). |
| | 131 | [[BR]] |
| | 132 | {{{Notification}}} |
| | 133 | [[BR]] |
| | 134 | 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. |
| | 135 | [[BR]] |
| | 136 | '''[[span(style=color: #E30B5C, Candidate keys: )]]''' |
| | 137 | * {{{notification_id}}} – surrogate numeric identifier (primary key). |
| | 138 | No natural candidate key exists because notifications are numerous and non-unique. |
| | 139 | {{{notification_id}}} is chosen as a primary key. |
| | 140 | [[BR]] |
| | 141 | '''[[span(style=color: #E30B5C, Attributes: )]]''' |
| | 142 | * {{{notification_id}}} – numeric, required. Auto-generated primary key. |
| | 143 | * {{{type}}} – text, required. Allowed values include {APPOINTMENT_REMINDER, NEW_REVIEW, LISTING_STATUS}. |
| | 144 | * {{{created_at}}} – datetime, required. When the notification was generated. |
| | 145 | * {{{message}}} – text, required. Human-readable notification body. |
| | 146 | * {{{user_id}}} – numeric, required. Foreign key to User.user_id (recipient). |
| | 147 | * {{{is_read}}} – boolean, required. Defaults to FALSE; becomes TRUE when viewed. |
