Changes between Version 2 and Version 3 of RelationalDesign

Timestamp:

09/02/26 15:50:17 (4 days ago)

Author:

236024

Comment:

--

RelationalDesign

@@ -1 +1 @@
 = Relational Design =
 
-This page documents the relational design for BEST Skopje Hub, based on !ERModel_v09. The design uses partial transformation from the ER model into a PostgreSQL relational schema. Strong entities are mapped directly to relations, weak entities include the primary keys of their owner entities, specializations are mapped with one relation for the supertype and one relation for each subtype, and M:N relationships are mapped as separate relations.
-
-Some attribute names were adapted to SQL-friendly snake_case names. For example, the ER attribute `index` is represented as `student_index`, and the !EventEditions attributes `title`, `start`, and `end` are represented as `event_title`, `event_start_time`, and `event_end_time`.
+This page documents the relational design for BEST Skopje Hub, based on !ERModel_v10. The design uses partial transformation from the ER model into a PostgreSQL relational schema. Strong entities are mapped directly to relations, weak entities include the primary keys of their owner entities, specializations are mapped with one relation for the supertype and one relation for each subtype, and M:N relationships are mapped as separate relations.
 
 == Descriptive representation of the relational schema ==
@@ -46 +44 @@
 
 membership_stages(
-    ms_name,
-    PK(ms_name)
+    ms_id, ms_name,
+    PK(ms_id),
+    UNIQUE(ms_name)
 )
 
 has_stage(
-    student_index, ms_name, valid_from, valid_to,
-    PK(student_index, ms_name, valid_from),
-    FK(student_index) -> members(student_index),
-    FK(ms_name) -> membership_stages(ms_name)
+    student_index, ms_id, valid_from, valid_to,
+    PK(student_index, ms_id, valid_from),
+    FK(student_index) -> members(student_index),
+    FK(ms_id) -> membership_stages(ms_id)
 )
 
 organizational_functions(
-    of_title,
-    PK(of_title)
+    of_id, of_title,
+    PK(of_id),
+    UNIQUE(of_title)
 )
 
 board(
-    of_title,
-    PK(of_title),
-    FK(of_title) -> organizational_functions(of_title)
+    of_id,
+    PK(of_id),
+    FK(of_id) -> organizational_functions(of_id)
 )
 
 non_board(
-    of_title,
-    PK(of_title),
-    FK(of_title) -> organizational_functions(of_title)
-)
-
-is_organizational_function(
-    student_index, of_title, mandate,
-    PK(student_index, of_title, mandate),
+    of_id,
+    PK(of_id),
+    FK(of_id) -> organizational_functions(of_id)
+)
+
+function_mandates(
+    student_index, of_id, mandate_year,
+    PK(student_index, of_id, mandate_year),
     FK(student_index) -> full_members(student_index),
-    FK(of_title) -> organizational_functions(of_title)
+    FK(of_id) -> organizational_functions(of_id)
 )
 
 activity_types(
-    at_name, at_description,
-    PK(at_name)
+    at_id, at_name, at_description,
+    PK(at_id),
+    UNIQUE(at_name)
+)
+
+locations(
+    l_id, l_name,
+    PK(l_id),
+    UNIQUE(l_name)
 )
 
 activity_sessions(
-    at_name, as_start_time, as_location,
-    PK(at_name, as_start_time, as_location),
-    FK(at_name) -> activity_types(at_name)
+    as_id, at_id, l_id, as_start_time,
+    PK(as_id),
+    FK(at_id) -> activity_types(at_id),
+    FK(l_id) -> locations(l_id)
 )
 
 related_to(
-    at_name, as_start_time, as_location, et_name, event_title, event_start_time,
-    PK(at_name, as_start_time, as_location),
-    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
+    as_id, ee_id,
+    PK(as_id),
+    FK(as_id) -> activity_sessions(as_id),
+    FK(ee_id) -> event_editions(ee_id)
 )
 
 event_types(
-    et_name, et_description,
-    PK(et_name)
+    et_id, et_name, et_description,
+    PK(et_id),
+    UNIQUE(et_name)
 )
 
 event_editions(
-    et_name, event_title, event_start_time, event_end_time,
-    PK(et_name, event_title, event_start_time),
-    FK(et_name) -> event_types(et_name)
+    ee_id, et_id, ee_title, ee_start_time, ee_end_time,
+    PK(ee_id),
+    FK(et_id) -> event_types(et_id)
 )
 
 event_edition_locations(
-    et_name, event_title, event_start_time, location,
-    PK(et_name, event_title, event_start_time, location),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
+    ee_id, l_id,
+    PK(ee_id, l_id),
+    FK(ee_id) -> event_editions(ee_id),
+    FK(l_id) -> locations(l_id)
 )
 
 event_session_types(
-    est_name, est_description, needed_no_volunteers,
-    PK(est_name)
+    est_id, est_name, est_description,
+    PK(est_id),
+    UNIQUE(est_name)
 )
 
 event_sessions(
-    et_name, event_title, event_start_time, est_name, es_start_time, location, es_title,
-    PK(et_name, event_title, event_start_time, est_name, es_start_time, location),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time),
-    FK(est_name) -> event_session_types(est_name)
-)
-
-core_team_functions(
-    et_name, event_title, event_start_time, function_name,
-    PK(et_name, event_title, event_start_time, function_name),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
+    ee_id, es_no, est_id, l_id, es_start_time, es_title, needed_no_volunteers,
+    PK(ee_id, es_no),
+    FK(ee_id) -> event_editions(ee_id),
+    FK(est_id) -> event_session_types(est_id),
+    FK(l_id) -> locations(l_id)
+)
+
+event_functions(
+    ee_id, ef_no, ef_name,
+    PK(ee_id, ef_no),
+    FK(ee_id) -> event_editions(ee_id)
 )
 
 applications(
-    application_id, student_index, et_name, event_title, event_start_time,
-    function_name, motivational_letter, application_type, application_status,
+    application_id, student_index, ee_id, ef_no,
+    motivational_letter, application_type, application_status,
     PK(application_id),
     FK(student_index) -> students(student_index),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time),
-    FK(et_name, event_title, event_start_time, function_name) -> core_team_functions(et_name, event_title, event_start_time, function_name)
+    FK(ee_id) -> event_editions(ee_id),
+    FK(ee_id, ef_no) -> event_functions(ee_id, ef_no)
 )
 
 participation(
-    student_index, at_name, as_start_time, as_location,
-    rsvp_status, rsvp_at, attendance_status,
-    PK(student_index, at_name, as_start_time, as_location),
+    student_index, as_id, rsvp_status, rsvp_at, attendance_status,
+    PK(student_index, as_id),
     FK(student_index) -> students(student_index),
-    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
+    FK(as_id) -> activity_sessions(as_id)
 )
 
 announces_absence(
-    student_index, at_name, as_start_time, as_location, reason, announced_at,
-    PK(student_index, at_name, as_start_time, as_location),
-    FK(student_index) -> members(student_index),
-    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
+    student_index, as_id, reason, announced_at,
+    PK(student_index, as_id),
+    FK(student_index) -> members(student_index),
+    FK(as_id) -> activity_sessions(as_id)
 )
 
 responsible_for(
-    student_index, at_name, as_start_time, as_location,
-    PK(student_index, at_name, as_start_time, as_location),
-    FK(student_index) -> members(student_index),
-    FK(at_name, as_start_time, as_location) -> activity_sessions(at_name, as_start_time, as_location)
+    student_index, as_id,
+    PK(student_index, as_id),
+    FK(student_index) -> members(student_index),
+    FK(as_id) -> activity_sessions(as_id)
 )
 
 volunteers(
-    student_index, et_name, event_title, event_start_time,
-    est_name, es_start_time, location, volunteer_role,
-    PK(student_index, et_name, event_title, event_start_time, est_name, es_start_time, location),
-    FK(student_index) -> members(student_index),
-    FK(et_name, event_title, event_start_time, est_name, es_start_time, location) -> event_sessions(et_name, event_title, event_start_time, est_name, es_start_time, location)
+    student_index, ee_id, es_no, volunteer_role,
+    PK(student_index, ee_id, es_no),
+    FK(student_index) -> members(student_index),
+    FK(ee_id, es_no) -> event_sessions(ee_id, es_no)
 )
 
@@ -179 +189 @@
 
 cooperation(
-    c_id, et_name, event_title, event_start_time, cooperation_type,
-    PK(c_id, et_name, event_title, event_start_time),
+    c_id, ee_id, cooperation_type,
+    PK(c_id, ee_id),
     FK(c_id) -> companies(c_id),
-    FK(et_name, event_title, event_start_time) -> event_editions(et_name, event_title, event_start_time)
+    FK(ee_id) -> event_editions(ee_id)
 )
 }}}
@@ -192 +202 @@
 The specialization of students into members and then into observers, young members, full members, and alumni is represented through subtype tables whose primary keys are also foreign keys. `young_members` also contains `mentor_index`, which represents the mentorship relationship directly as a foreign key to `full_members`.
 
-The weak entities from the ER model are represented using composite primary keys. `activity_sessions` includes the key of `activity_types`. `event_editions` includes the key of `event_types`. `event_sessions` includes the composite key of `event_editions`, the key of `event_session_types`, and its partial key attributes `es_start_time` and `location`. `core_team_functions` includes the composite key of `event_editions` and its partial key `function_name`.
-
-The multivalued `locations` attribute of !EventEditions is transformed into the separate relation `event_edition_locations`.
-
-The relationships `has_stage`, `participation`, `announces_absence`, `responsible_for`, `volunteers`, `cooperation`, `related_to`, and `is` are represented as relations because they either have M:N cardinality, their own attributes, or are clearer without nullable foreign-key columns. The relationship `related_to` is represented separately because only some activity sessions are connected to event editions. The relationship `applies_with` is represented with the foreign key `student_index` in `applications`, because each application belongs to one student. The N:1 relationships `for_function`, `for_participation`, and `mentorship` are represented directly with foreign keys on the N-side relation.
+The weak entities from !ERModel_v10 are represented with composite primary keys where they still depend on an owner entity. `event_functions` uses `(ee_id, ef_no)`, and `event_sessions` uses `(ee_id, es_no)`. Other concrete entities such as `activity_sessions` and `event_editions` use their own numeric identifiers.
+
+The Locations entity is represented with `locations`. The relationship between !EventEditions and Locations is represented with `event_edition_locations`.
+
+The relationships `has_stage`, `participation`, `announces_absence`, `responsible_for`, `volunteers`, `cooperation`, `related_to`, `holds_function`, and the event-edition location relationship are represented as relations because they either have M:N cardinality, their own attributes, or are clearer without nullable foreign-key columns. The relationship `related_to` is represented separately because only some activity sessions are connected to event editions. The relationship `applies_with` is represented with the foreign key `student_index` in `applications`, because each application belongs to one student. The N:1 relationships `for_function`, `for_participation`, `takes_place_at`, `held_at`, and `mentorship` are represented directly with foreign keys on the N-side relation.
 
 == DDL script for creating the database schema and objects ==
@@ -218 +228 @@
 == AI Use ==
 
-AI was used during this phase as a consultation, drafting, and checking tool while preparing the relational design from the already completed !ERModel_v09. The AI-assisted parts were reviewed against the existing ER model and project requirements before being included.
+AI was used during this phase as a consultation, drafting, and checking tool while preparing the relational design from the already completed !ERModel_v10. The AI-assisted parts were reviewed against the existing ER model and project requirements before being included.
 
 Full AI usage documentation: [wiki:RelationalDesignAIUsage RelationalDesignAIUsage].