Changeset c42181c for sql

Timestamp:

09/16/26 14:48:55 (7 days ago)

Author:

Stefan-Saveski <stefansaveski19@…>

Branches:

master

Children:

8ee90bd

Parents:

ea40556

Message:

Refactor payment model to remove redundant user_id, addressing BCNF violation in Phase 5 normalization

Location:

sql

Files:

• data_load.sql (modified) (1 diff)
• iknow.dbml (modified) (2 diffs)
• schema.md (modified) (3 diffs)
• schema_creation.sql (modified) (1 diff)

sql/data_load.sql

@@ -115 +115 @@
 (3, 1, 'tok_old789_stefan', '2026-01-01 00:00:00', FALSE);
 
-INSERT INTO payment (id, user_id, enrollment_id, amount) VALUES
-(1, 1, 1, 200),
-(2, 1, 2, 200),
-(3, 2, 3, 400),
-(4, 3, 4, 200);
+INSERT INTO payment (id, enrollment_id, amount) VALUES
+(1, 1, 200),
+(2, 2, 200),
+(3, 3, 400),
+(4, 4, 200);
 
 INSERT INTO major_subjects (major_id, subject_id, mandatory_semester) VALUES

sql/iknow.dbml

@@ -150 +150 @@
 Table project.payment {
   id integer [pk, increment]
-  user_id integer [not null]
   enrollment_id integer [not null]
   amount integer [not null]
@@ -188 +187 @@
 Ref: project.contact.user_id - project.users.id                      // has_contact (1:1)
 Ref: project.token.user_id > project.users.id                        // has_token
-Ref: project.payment.user_id > project.users.id                      // pays
 Ref: project.payment.enrollment_id > project.enrolled_semesters.id   // for_enrollment
 Ref: project.enrolled_semesters.user_id > project.users.id           // submits

sql/schema.md

@@ -111 +111 @@
     payment {
         integer id PK
-        integer user_id FK
         integer enrollment_id FK
         integer amount
@@ -136 +135 @@
     users               ||--o| contact            : has_contact
     users               ||--o{ token              : has_token
-    users               ||--o{ payment            : pays
     users               ||--o{ enrolled_semesters : submits
     users               ||--o{ user_documents     : owns
@@ -184 +182 @@
   they differ.
 - `professour_subjects` keeps the spelling used by the table in `schema_creation.sql`.
+- `payment` has no `user_id`: the student is reached through `enrollment_id`.
+  Storing it twice violated BCNF (`enrolled_id -> user_id`) and allowed a
+  payment to contradict the enrolment it refers to. Removed in Phase 5.

sql/schema_creation.sql

@@ -115 +115 @@
 CREATE TABLE payment (
     id             INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-    user_id        INTEGER NOT NULL REFERENCES users (id),
     enrollment_id  INTEGER NOT NULL REFERENCES enrolled_semesters (id),
     amount         INTEGER NOT NULL