Changes between Version 6 and Version 7 of RelationalDesign
- Timestamp:
- 09/11/26 00:16:19 (12 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
RelationalDesign
v6 v7 15 15 surname, 16 16 password_hash, 17 created_at 18 ) 17 created_at) 19 18 20 19 - Admin( 21 user_id [PK, FK→Users(user_id)] 22 ) 20 user_id [PK, FK→Users(user_id)]) 23 21 24 22 - Client( … … 27 25 blocked_at, 28 26 blocked_reason, 29 blocked_by [FK→Admin(user_id)] 30 ) 27 blocked_by [FK→Admin(user_id)]) 31 28 32 29 - Owner( 33 user_id [PK, FK→Client(user_id)] 34 ) 30 user_id [PK, FK→Client(user_id)]) 35 31 36 32 - VetClinic( 37 33 clinic_id [PK], 38 user_id [FK→Users(user_id), UQ] 39 ) 34 user_id [FK→Users(user_id), UQ]) 40 35 41 36 VetClinic is also a specialization of Users. It keeps its own clinic_id as the primary key, while user_id connects it to the Users supertype. 42 37 43 --- 38 ---- 44 39 45 40 {{{ CLIENT INTERACTIONS }}} … … 52 47 This represents the recursive Client–Client relationship `interaction_with`. 53 48 54 --- 49 ---- 55 50 56 51 {{{ NOTIFICATIONS }}} … … 62 57 message, 63 58 is_read, 64 created_at 65 ) 66 67 --- 59 created_at) 60 61 ---- 68 62 69 63 {{{ VET CLINIC APPLICATIONS }}} … … 81 75 reviewed_at, 82 76 reviewed_by [FK→Admin(user_id)], 83 denial_reason 84 ) 85 86 --- 77 denial_reason) 78 79 ---- 87 80 88 81 {{{ VET CLINICS }}} … … 100 93 work_days, 101 94 start_time, 102 end_time 103 ) 104 105 --- 95 end_time) 96 97 ---- 106 98 107 99 {{{ ANIMALS }}} … … 117 109 species, 118 110 breed, 119 located_name 120 ) 121 122 --- 111 located_name) 112 113 ---- 123 114 124 115 {{{ LISTINGS }}} … … 131 122 price, 132 123 description, 133 created_at 134 ) 124 created_at) 135 125 136 126 - FavoriteListing( 137 127 fav_listing_id [PK], 138 128 user_id [FK→Users(user_id)], 139 listing_id [FK→Listing(listing_id)] 140 ) 141 142 --- 129 listing_id [FK→Listing(listing_id)]) 130 131 ---- 143 132 144 133 {{{ APPOINTMENTS }}} … … 151 140 status, 152 141 date_time, 153 notes 154 ) 155 156 --- 142 notes) 143 144 ---- 157 145 158 146 {{{ REVIEWS: SUPERTYPE + SUBTYPES }}} … … 165 153 created_at, 166 154 updated_at, 167 is_deleted 168 ) 155 is_deleted) 169 156 170 157 - UserReview( 171 158 review_id [PK, FK→Review(review_id)], 172 159 target_user_id [FK→Users(user_id)], 173 interaction_type 174 ) 160 interaction_type) 175 161 176 162 - ClinicReview( 177 163 review_id [PK, FK→Review(review_id)], 178 164 target_clinic_id [FK→VetClinic(clinic_id)], 179 appointment_id [FK→Appointment(appointment_id)] 180 ) 181 182 --- 165 appointment_id [FK→Appointment(appointment_id)]) 166 167 ---- 183 168 184 169 {{{ HEALTH RECORDS }}} … … 190 175 type, 191 176 description, 192 date 193 ) 194 195 --- 177 date) 178 179 ---- 196 180 197 181 {{{ CLINIC UNAVAILABLE SLOTS }}} … … 202 186 date_time, 203 187 reason, 204 created_at 205 ) 206 207 --- 188 created_at) 189 190 ---- 208 191 209 192 == Business Rules Enforced in DDL / Triggers == … … 211 194 [[BR]] 212 195 Review.rating must be in range 1–5. 213 214 196 [[BR]] 215 197 Review specialization is disjoint: one Review can be either UserReview or ClinicReview, not both. 216 217 198 [[BR]] 218 199 A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'. 219 220 200 [[BR]] 221 201 A Client can leave a UserReview only for another Client/User with whom they have an existing ClientInteraction. 222 223 202 [[BR]] 224 203 ClientInteraction is recursive: both source_client_id and target_client_id reference Client(user_id). 225 226 204 [[BR]] 227 205 A Client cannot have an interaction with themselves. 228 229 206 [[BR]] 230 207 HealthRecord consistency: HealthRecord.animal_id must match Appointment.animal_id. 231 232 208 [[BR]] 233 209 UserReview.interaction_type must be one of the allowed interaction types defined in the DDL.
