Changes between Version 5 and Version 6 of RelationalDesign


Ignore:
Timestamp:
09/11/26 00:14:21 (12 hours ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v5 v6  
    11= Relation Design
    2 == Notation
    3 * PK marked with [PK]
    4 * FK marked with [FK→Table(column)]
    5 * UQ = unique constraint
    6 == Relation schema
     2== Notation ==
     3- PK marked with [PK]
     4- FK marked with [FK→Table(column)]
     5- UQ = unique constraint
     6== Relation Schema ==
     7
    78{{{ USERS + SUBTYPES }}}
    8 * Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
    9 * Admin(user_id [PK, FK→Users(user_id)])
    10 * Client(user_id [PK, FK→Users(user_id)])
    11 * Owner(user_id [PK, FK→Client(user_id)])
    12 * VetClinic(clinic_id[PK],user_id [FK→Users(user_id)])
    13 ----
     9
     10- Users(
     11  user_id [PK],
     12  username [UQ],
     13  email [UQ],
     14  name,
     15  surname,
     16  password_hash,
     17  created_at
     18)
     19
     20- Admin(
     21  user_id [PK, FK→Users(user_id)]
     22)
     23
     24- Client(
     25  user_id [PK, FK→Users(user_id)],
     26  is_blocked,
     27  blocked_at,
     28  blocked_reason,
     29  blocked_by [FK→Admin(user_id)]
     30)
     31
     32- Owner(
     33  user_id [PK, FK→Client(user_id)]
     34)
     35
     36- VetClinic(
     37  clinic_id [PK],
     38  user_id [FK→Users(user_id), UQ]
     39)
     40
     41VetClinic 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
     43---
     44
     45{{{ CLIENT INTERACTIONS }}}
     46
     47- ClientInteraction(
     48  source_client_id [PK, FK→Client(user_id)],
     49  target_client_id [PK, FK→Client(user_id)],
     50)
     51
     52This represents the recursive Client–Client relationship `interaction_with`.
     53
     54---
     55
    1456{{{ NOTIFICATIONS }}}
    15 * Notification(
     57
     58- Notification(
    1659  notification_id [PK],
    1760  user_id [FK→Users(user_id)],
    … …  
    1962  message,
    2063  is_read,
    21   created_at)
    22 ----
     64  created_at
     65)
     66
     67---
     68
    2369{{{ VET CLINIC APPLICATIONS }}}
    24 * VetClinicApplication(
     70
     71- VetClinicApplication(
    2572  application_id [PK],
    26   clinic_id [FK→VetClinic(clinic_id)],
     73  clinic_id [FK→VetClinic(clinic_id), UQ],
    2774  name,
    2875  email,
    … …  
    3481  reviewed_at,
    3582  reviewed_by [FK→Admin(user_id)],
    36   denial_reason)
    37 ----
     83  denial_reason
     84)
     85
     86---
     87
    3888{{{ VET CLINICS }}}
    39 * VetClinic(
     89
     90- VetClinic(
    4091  clinic_id [PK],
    41   user_id [FK→User(user_id), UQ],
     92  user_id [FK→Users(user_id), UQ],
    4293  application_id [FK→VetClinicApplication(application_id), UQ],
    4394  name,
    … …  
    49100  work_days,
    50101  start_time,
    51   end_time)
    52 ----
     102  end_time
     103)
     104
     105---
     106
    53107{{{ ANIMALS }}}
    54 * Animal(
     108
     109- Animal(
    55110  animal_id [PK],
    56111  owner_id [FK→Owner(user_id)],
    … …  
    62117  species,
    63118  breed,
    64   located_name)
    65 ----
     119  located_name
     120)
     121
     122---
     123
    66124{{{ LISTINGS }}}
    67 * Listing(
     125
     126- Listing(
    68127  listing_id [PK],
    69128  owner_id [FK→Owner(user_id)],
    … …  
    72131  price,
    73132  description,
    74   created_at)
    75 * FavoriteListing(
     133  created_at
     134)
     135
     136- FavoriteListing(
     137  fav_listing_id [PK],
    76138  user_id [FK→Users(user_id)],
    77   listing_id [FK→Listing(listing_id)],
    78   [PK](user_id, listing_id))
    79 ----
     139  listing_id [FK→Listing(listing_id)]
     140)
     141
     142---
     143
    80144{{{ APPOINTMENTS }}}
    81 * Appointment(
     145
     146- Appointment(
    82147  appointment_id [PK],
    83148  clinic_id [FK→VetClinic(clinic_id)],
    … …  
    86151  status,
    87152  date_time,
    88   notes)
    89 ----
     153  notes
     154)
     155
     156---
     157
    90158{{{ REVIEWS: SUPERTYPE + SUBTYPES }}}
    91 * Review(
     159
     160- Review(
    92161  review_id [PK],
    93162  reviewer_id [FK→Client(user_id)],
    … …  
    96165  created_at,
    97166  updated_at,
    98   is_deleted)
    99 * UserReview(
     167  is_deleted
     168)
     169
     170- UserReview(
    100171  review_id [PK, FK→Review(review_id)],
    101172  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   appointment_id [FK -> Appointment(appointment_id)])
    107 ----
     173  interaction_type
     174)
     175
     176- ClinicReview(
     177  review_id [PK, FK→Review(review_id)],
     178  target_clinic_id [FK→VetClinic(clinic_id)],
     179  appointment_id [FK→Appointment(appointment_id)]
     180)
     181
     182---
     183
    108184{{{ HEALTH RECORDS }}}
    109 * HealthRecord(
     185
     186- HealthRecord(
    110187  healthrecord_id [PK],
    111188  animal_id [FK→Animal(animal_id)],
    … …  
    113190  type,
    114191  description,
    115   date)
    116 ----
     192  date
     193)
     194
     195---
     196
    117197{{{ CLINIC UNAVAILABLE SLOTS }}}
    118 * clinic_unavailable_slots(
     198
     199- ClinicUnavailableSlot(
    119200  slot_id [PK],
    120201  clinic_id [FK→VetClinic(clinic_id)],
    121202  date_time,
    122203  reason,
    123   created_at)
    124 ----
    125 == Business Rules Enforced in DDL / Triggers
    126 [[BR]] Review.rating must be in range 1–5.
    127 [[BR]] Review specialization is disjoint:one Review can be either UserReview or ClinicReview, not both.
    128 [[BR]] A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
    129 [[BR]] HealthRecord consistency:HealthRecord.animal_id must match Appointment.animal_id.
    130 [[BR]] FavoriteListing has composite primary key:(user_id, listing_id).
     204  created_at
     205)
     206
     207---
     208
     209== Business Rules Enforced in DDL / Triggers ==
     210
     211[[BR]]
     212Review.rating must be in range 1–5.
     213
     214[[BR]]
     215Review specialization is disjoint: one Review can be either UserReview or ClinicReview, not both.
     216
     217[[BR]]
     218A Client can review a clinic only if they have at least one Appointment at that clinic with status = 'DONE'.
     219
     220[[BR]]
     221A Client can leave a UserReview only for another Client/User with whom they have an existing ClientInteraction.
     222
     223[[BR]]
     224ClientInteraction is recursive: both source_client_id and target_client_id reference Client(user_id).
     225
     226[[BR]]
     227A Client cannot have an interaction with themselves.
     228
     229[[BR]]
     230HealthRecord consistency: HealthRecord.animal_id must match Appointment.animal_id.
     231
     232[[BR]]
     233UserReview.interaction_type must be one of the allowed interaction types defined in the DDL.
    131234== DDL
    132235 * [attachment:schema_creation.sql]