Changes between Initial Version and Version 1 of RelationalDesign

Timestamp:

08/20/26 18:47:36 (32 hours ago)

Author:

231035

Comment:

--

RelationalDesign

@@ +1 @@
+= Relation Design
+== Notation
+* PK marked with [PK]
+* FK marked with [FK→Table(column)]
+* UQ = unique constraint
+== Relation schema
+{{{USERS + SUBTYPES}}}
+* Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
+* Admin(user_id [PK, FK→Users(user_id)])
+* Client(user_id [PK, FK→Users(user_id)])
+* Owner(user_id [PK, FK→Client(user_id)]) (Owner is a subtype of Client, as in the diagram)
+* Vet_clinic(user_id [PK, FK→vet_clinic(user_id)])
+
+{{{NOTIFICATIONS}}}
+* Notification(notification_id [PK], user_id [FK→Users(user_id)], type, message, is_read, created_at)
+
+{{{VET CLINIC APPLICATIONS}}}
+* vet_clinic_applications(application_id[PK],name,email,phone,city,address,submitted_at,status,reviewd_at,reviewd_by[FK→Admin(user_id)],denial_reason)
+ 
+{{{VET CLINICS}}}
+* !VetClinic(clinic_id [PK], name, email, phone, city, address, user_if(FK→vet_clinic(user_id)),application_id[FK→vet_clinic_applications(application_id)])
+
+{{{ANIMALS}}}
+* Animal(animal_id [PK], owner_id [FK→Owner(user_id)], name, sex, date_of_birth, photo_url, type, species, breed, located_name)
+
+{{{LISTINGS}}}
+* Listing(listing_id [PK], owner_id [FK→Owner(user_id)], animal_id [FK→Animal(animal_id)], status, price, description, created_at)
+{{{FAVORITE LISTINGS}}}
+* favorite_listings(client_id[FK→User(user_id)],listing_id[FK→Listing(listing_id)])→The whole thing is the PK
+
+{{{APPOINTMENTS}}}
+* Appointment(appointment_id [PK], clinic_id [FK→!VetClinic(clinic_id)], animal_id [FK→Animal(animal_id)], responsible_owner_id [FK→Owner(user_id)], status, date_time, notes)
+
+{{{REVIEWS (SUPERTYPE + SUBTYPES)}}}
+* Review(review_id [PK], reviewer_id [FK→Client(user_id)], rating, comment, created_at)
+* !UserReview(review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)])
+* !ClinicReview(review_id [PK, FK→Review(review_id)], target_clinic_id [FK→!VetClinic(clinic_id)])
+
+{{{HEALTH RECORDS}}}
+* !HealthRecord(healthrecord_id [PK], animal_id [FK→Animal(animal_id)], appointment_id [FK→Appointment(appointment_id)], type, description, date)
+
+{{{CLINIC UNAVAILABLE SLOTS}}}
+* clinic_unavailable_slots(slot_id[PK],clinic_id[FK→vet_clinics(clinic_id)],date_time,reason,created_at)
+----
+Business rules enforced in DDL:
+[[BR]]
+Review rating range (1–5)
+[[BR]]
+Disjoint review specialization (a Review can be either !UserReview or !ClinicReview, not both) via trigger
+[[BR]]
+Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
+[[BR]]
+!HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)
+== DDL
+ * [attachment:schema_creation.sql]
+== DML
+* [attachment:data_load.sql]
+== Relational diagram
+[[Image(relational_schema.jpg)]]