Changes between Version 5 and Version 6 of Normalization

Timestamp:

04/21/26 12:12:53 (11 days ago)

Author:

231035

Comment:

--

Normalization

@@ -234 +234 @@
 * review_id → target_user_id / target_clinic_id
 * FavoriteListings has key (saved_user_id, saved_listing_id) only
+
+=== Explanation of grouping by determinants
+
+The grouping is performed based on the principle that each set of attributes is determined by a single identifier.
+This ensures that each relation will have a primary key on which all other attributes fully depend,
+thus satisfying 2NF.
+
+* user_id → username, email, name, surname, full_name, password_hash, user_created_at  
+These attributes describe a user and depend only on user_id, so they are grouped into the Users relation.
+
+* client_id → user_id, is_blocked, blocked_at, blocked_reason  
+These attributes describe the client role of a user, so they are grouped into Clients, separated from Users.
+
+* admin_id → user_id  
+This represents the admin role, so it is separated into its own relation to reflect specialization.
+
+* owner_id → client_id  
+Owner is a specialization of Client, so it is grouped separately to preserve this hierarchy.
+
+* notification_id → notification_type, notification_message, notification_created_at, is_read, user_id  
+These attributes describe a notification and depend only on notification_id, so they form the Notifications relation.
+
+* clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city  
+These attributes describe a clinic and depend only on clinic_id, so they are grouped into VetClinics.
+
+* application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id  
+These attributes describe a clinic application and depend only on application_id, so they form VetClinicApplications.
+
+* animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id  
+These attributes describe an animal and depend only on animal_id, so they form the Animals relation.
+
+* listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id  
+These attributes describe a listing and depend only on listing_id, so they form the Listings relation.
+
+* appointment_id → date_time, appointment_status, notes, animal_id, clinic_id  
+These attributes describe an appointment and depend only on appointment_id, so they form the Appointments relation.
+
+* healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id  
+These attributes describe a health record and depend only on healthrecord_id, so they form the HealthRecords relation.
+
+* review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted  
+These attributes describe a review and depend only on review_id, so they form the Reviews relation.
+
+* review_id → target_user_id / target_clinic_id  
+These attributes represent specialization (UserReview and ClinicReview), so they will be separated later.
+
+* FavoriteListings has key (saved_user_id, saved_listing_id) only  
+This represents a many-to-many relationship between users and listings. Since it has no non-key attributes, it is already correctly grouped and does not require further decomposition.
 
 === 2NF relations (first decomposition)