Changes between Version 1 and Version 2 of RelationalDesign


Ignore:
Timestamp:
08/22/26 15:35:21 (11 hours ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v1 v2  
    55* UQ = unique constraint
    66== Relation schema
    7 {{{USERS + SUBTYPES}}}
     7{{{ USERS + SUBTYPES }}}
    88* Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
    99* Admin(user_id [PK, FK→Users(user_id)])
    1010* Client(user_id [PK, FK→Users(user_id)])
    11 * Owner(user_id [PK, FK→Client(user_id)]) (Owner is a subtype of Client, as in the diagram)
    12 * Vet_clinic(user_id [PK, FK→vet_clinic(user_id)])
    13 
    14 {{{NOTIFICATIONS}}}
    15 * Notification(notification_id [PK], user_id [FK→Users(user_id)], type, message, is_read, created_at)
    16 
    17 {{{VET CLINIC APPLICATIONS}}}
    18 * vet_clinic_applications(application_id[PK],name,email,phone,city,address,submitted_at,status,reviewd_at,reviewd_by[FK→Admin(user_id)],denial_reason)
    19  
    20 {{{VET CLINICS}}}
    21 * !VetClinic(clinic_id [PK], name, email, phone, city, address, user_if(FK→vet_clinic(user_id)),application_id[FK→vet_clinic_applications(application_id)])
    22 
    23 {{{ANIMALS}}}
    24 * Animal(animal_id [PK], owner_id [FK→Owner(user_id)], name, sex, date_of_birth, photo_url, type, species, breed, located_name)
    25 
    26 {{{LISTINGS}}}
    27 * Listing(listing_id [PK], owner_id [FK→Owner(user_id)], animal_id [FK→Animal(animal_id)], status, price, description, created_at)
    28 {{{FAVORITE LISTINGS}}}
    29 * favorite_listings(client_id[FK→User(user_id)],listing_id[FK→Listing(listing_id)])→The whole thing is the PK
    30 
    31 {{{APPOINTMENTS}}}
    32 * 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)
    33 
    34 {{{REVIEWS (SUPERTYPE + SUBTYPES)}}}
    35 * Review(review_id [PK], reviewer_id [FK→Client(user_id)], rating, comment, created_at)
    36 * !UserReview(review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)])
    37 * !ClinicReview(review_id [PK, FK→Review(review_id)], target_clinic_id [FK→!VetClinic(clinic_id)])
    38 
    39 {{{HEALTH RECORDS}}}
    40 * !HealthRecord(healthrecord_id [PK], animal_id [FK→Animal(animal_id)], appointment_id [FK→Appointment(appointment_id)], type, description, date)
    41 
    42 {{{CLINIC UNAVAILABLE SLOTS}}}
    43 * clinic_unavailable_slots(slot_id[PK],clinic_id[FK→vet_clinics(clinic_id)],date_time,reason,created_at)
     11* Owner(user_id [PK, FK→Client(user_id)])
     12* VetClinicUser(user_id [PK, FK→Users(user_id)])
    4413----
    45 Business rules enforced in DDL:
    46 [[BR]]
    47 Review rating range (1–5)
    48 [[BR]]
    49 Disjoint review specialization (a Review can be either !UserReview or !ClinicReview, not both) via trigger
    50 [[BR]]
    51 Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
    52 [[BR]]
    53 !HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)
     14{{{ NOTIFICATIONS }}}
     15* Notification(
     16  notification_id [PK],
     17  user_id [FK→Users(user_id)],
     18  type,
     19  message,
     20  is_read,
     21  created_at)
     22----
     23{{{ VET CLINIC APPLICATIONS }}}
     24* VetClinicApplication(
     25  application_id [PK],
     26  clinic_id [FK→VetClinic(clinic_id)],
     27  name,
     28  email,
     29  phone,
     30  city,
     31  address,
     32  submitted_at,
     33  status,
     34  reviewed_at,
     35  reviewed_by [FK→Admin(user_id)],
     36  denial_reason)
     37----
     38{{{ VET CLINICS }}}
     39* VetClinic(
     40  clinic_id [PK],
     41  user_id [FK→VetClinicUser(user_id), UQ],
     42  application_id [FK→VetClinicApplication(application_id), UQ],
     43  name,
     44  email,
     45  phone,
     46  city,
     47  address,
     48  location,
     49  work_days,
     50  start_time,
     51  end_time)
     52----
     53{{{ ANIMALS }}}
     54* Animal(
     55  animal_id [PK],
     56  owner_id [FK→Owner(user_id)],
     57  name,
     58  sex,
     59  date_of_birth,
     60  photo_url,
     61  type,
     62  species,
     63  breed,
     64  located_name)
     65----
     66{{{ LISTINGS }}}
     67* Listing(
     68  listing_id [PK],
     69  owner_id [FK→Owner(user_id)],
     70  animal_id [FK→Animal(animal_id)],
     71  status,
     72  price,
     73  description,
     74  created_at)
     75* FavoriteListing(
     76  user_id [FK→Client(user_id)],
     77  listing_id [FK→Listing(listing_id)],
     78  [PK](user_id, listing_id))
     79----
     80{{{ APPOINTMENTS }}}
     81* Appointment(
     82  appointment_id [PK],
     83  clinic_id [FK→VetClinic(clinic_id)],
     84  animal_id [FK→Animal(animal_id)],
     85  responsible_owner_id [FK→Owner(user_id)],
     86  status,
     87  date_time,
     88  notes)
     89----
     90{{{ REVIEWS: SUPERTYPE + SUBTYPES }}}
     91* Review(
     92  review_id [PK],
     93  reviewer_id [FK→Client(user_id)],
     94  rating,
     95  comment,
     96  created_at,
     97  updated_at,
     98  is_deleted)
     99* UserReview(
     100  review_id [PK, FK→Review(review_id)],
     101  target_user_id [FK→Users(user_id)],
     102  interaction_type)
     103* ClinicReview(
     104  review_id [PK, FK→Review(review_id)],
     105  target_clinic_id [FK→VetClinic(clinic_id)])
     106----
     107{{{ HEALTH RECORDS }}}
     108* HealthRecord(
     109  healthrecord_id [PK],
     110  animal_id [FK→Animal(animal_id)],
     111  appointment_id [FK→Appointment(appointment_id)],
     112  type,
     113  description,
     114  date)
     115----
     116{{{ CLINIC UNAVAILABLE SLOTS }}}
     117* clinic_unavailable_slots(
     118  slot_id [PK],
     119  clinic_id [FK→VetClinic(clinic_id)],
     120  date_time,
     121  reason,
     122  created_at)
     123----
     124== Business Rules Enforced in DDL / Triggers
     125[[BR]] Review.rating must be in range 1–5.
     126[[BR]] Review specialization is disjoint:one Review can be either UserReview or ClinicReview, not both.
     127[[BR]] A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
     128[[BR]] HealthRecord consistency:HealthRecord.animal_id must match Appointment.animal_id.
     129[[BR]] FavoriteListing has composite primary key:(user_id, listing_id).
    54130== DDL
    55131 * [attachment:schema_creation.sql]