Entity-Relationship Model v.08

Diagram

Data requirements

Entities

Students

Students represents UKIM students known to the system, including external participants and students who later become BEST Skopje members. It is modeled as the root person entity because every member is also a student, but not every student is necessarily a member. The entity is needed so that applications, activity participation, and membership can all refer to the same student record.

• Candidate keys: index is chosen as the primary key because the project is scoped to UKIM students and the student index uniquely identifies a student in this context.
• Attributes:
  • index: numeric/text identifier, required, unique.
  • first_name: text, required.
  • last_name: text, required.
  • faculty: text, required.
  • field_of_studies: text, required.
  • year_of_studies: numeric, required, non-negative value; 0 will be used for alumni who have finished their studies.
  • birthday: date, required, valid past date.
  • email: text, required, valid email format.
  • phone_number: text, required, valid phone-number format.

Members

Members represents students who are part of BEST Skopje. It is modeled as a specialization of Students because member records inherit the student data, while membership-specific information is represented through membership stages, mentorship, responsibilities, and organizational roles. This avoids duplicating personal data while allowing the system to distinguish ordinary event participants from members.

• Candidate keys: the primary key is inherited from Students, so index is used.
• Attributes: no direct attributes in the ER model.

Observers

Observers represents members in the observer/associate stage. It is modeled as a subtype of Members because observers are members with a specific membership status and possible future progression to young member.

• Candidate keys: inherited index from Members.
• Attributes: no direct attributes in the ER model.

YoungMembers

YoungMembers represents members in the young member stage. It is modeled as a subtype of Members because young members are members with additional membership-development meaning, especially mentorship by a full member and later possible progression to full member.

• Candidate keys: inherited index from Members.
• Attributes: no direct attributes in the ER model.

FullMembers

FullMembers represents members with full membership rights and responsibilities. It is modeled as a subtype of Members because full members can mentor young members and hold organizational functions, including Board and non-Board roles.

• Candidate keys: inherited index from Members.
• Attributes: no direct attributes in the ER model.

Alumni

Alumni represents former full members with alumni status. It is modeled as a subtype of Members because alumni are still relevant to BEST Skopje activities and history, but they are distinct from active observer, young, and full members. Their year of studies would be 0 if they've finished their studies.

• Candidate keys: inherited index from Members.
• Attributes: no direct attributes in the ER model.

MembershipStages

MembershipStages stores the possible membership stages used in membership history, such as observer, young member, full member, and alumni. It is modeled separately even though the member subtypes exist because the has_stage relationship needs to store historical periods with valid_from and valid_to.

• Candidate keys: ms_name is chosen as the primary key because membership stage names are stable controlled values.
• Attributes:
  • ms_name: text, required, unique.

OrganizationalFunctions

OrganizationalFunctions represents reusable organizational role types, such as President, Treasurer, Secretary, HR coordinator, FR coordinator, PR coordinator, IT coordinator, Design coordinator, Grants coordinator or Vivaldi coordinator. It is modeled separately from members because a function can be held by different full members in different mandates, and one full member can hold different functions over time.

• Candidate keys: of_title is chosen as the primary key because function titles are stable role names within the organization.
• Attributes:
  • of_title: text, required, unique.

Board

Board represents organizational functions that are Board roles. It is modeled as a subtype of OrganizationalFunctions because Board membership is treated as a role/function, not as a separate membership stadium.

• Candidate keys: inherited of_title from OrganizationalFunctions.
• Attributes: no direct attributes in the ER model.

NonBoard

NonBoard represents organizational functions that are not Board roles, such as non-Board coordinators. It is modeled as a subtype of OrganizationalFunctions so the model can distinguish Board and non-Board responsibilities while keeping both under the same function structure.

• Candidate keys: inherited of_title from OrganizationalFunctions.
• Attributes: no direct attributes in the ER model.

ActivityTypes

ActivityTypes represents abstract categories of internal activities, such as weekly meetings, BEST Course in Summer meetings, working groups, workshops, FR calls, trainings, full member meetings, Board meetings, parties, alumni parties, informative meetings, and assemblies. It is modeled separately from activity sessions because many concrete sessions can occur for the same activity type.

• Candidate keys: at_name is chosen as the primary key because activity type names are controlled and unique.
• Attributes:
  • at_name: text, required, unique.
  • at_description: text, required, explanation of what the activity type is used for.

ActivitySessions

ActivitySessions represents concrete occurrences of activity types. For example, one weekly meeting, one FR call, one IT workshop, or one training session is an activity session. It is modeled as a weak entity dependent on ActivityTypes because a session is an instance of an abstract activity type and is identified in context by that type, start time, and location.

• Candidate keys: the chosen primary key is the composite key at_name + as_start_time + as_location, where at_name comes from the owning ActivityTypes entity and as_start_time and as_location are the partial keys.
• Attributes:
  • as_start_time: datetime, required, valid future or past timestamp depending on whether the session is planned or completed.
  • as_location: text, required, room/place/online location.

EventTypes

EventTypes represents abstract event categories, such as Job Fair, BEST Course in Summer, Motivational Weekend, beBESTIE, BOMBA, Hackathon, BEST Skopje Birthday, or other event types. It is modeled separately from event editions because the same event type can be organized multiple times over different years or editions.

• Candidate keys: et_name is chosen as the primary key because event type names are controlled and unique.
• Attributes:
  • et_name: text, required, unique.
  • et_description: text, required, explanation of the event type.

EventEditions

EventEditions represents one concrete edition of an event type, such as Job Fair 2026 or BEST Course in Summer 2026. It is modeled as a weak entity dependent on EventTypes because an edition exists as an edition of an abstract event type, and the edition is identified in context by its event type, title, and start date.

• Candidate keys: the chosen primary key is the composite key et_name + title + start, where et_name comes from EventTypes and start and title are the partial keys.
• Attributes:
  • start: date/datetime, required, valid event start date/time.
  • end: date/datetime, required, must be greater than or equal to start.
  • title: text, required, title of the event edition.
  • locations: multivalued text, required because the event has at least one known location; each value is a room/place/online location.

EventSessionTypes

EventSessionTypes represents abstract types of sessions inside event editions, such as official opening, company stands, official closing, lecture timeslot, coffee break, city rally, or social activity. It is modeled separately because many event sessions across different events can be of the same type and may require a typical number of volunteers.

• Candidate keys: est_name is chosen as the primary key because event session type names are controlled and unique.
• Attributes:
  • est_name: text, required, unique.
  • est_description: text, required, explanation of the session type.
  • needed_no_volunteers: numeric, required, non-negative integer representing the usual needed number of volunteers.

EventSessions

EventSessions represents concrete sessions within an event edition, such as one official opening, one company stand period, one coffee break, or one lecture slot. It is modeled as a weak entity dependent on EventEditions and EventSessionTypes, because an event session exists only as part of a specific event edition and it is an instance of an event session type.

• Candidate keys: the chosen primary key is the composite key et_name + title + start + est_name + es_start_time + location.
• Attributes:
  • es_start_time: datetime, required, valid timestamp within or near the event edition period.
  • location: text, required, room/place/online location.
  • es_title: text, required, title of the event session.

CoreTeamFunctions

CoreTeamFunctions represents available organizing-team functions for a specific event edition, such as Main Organiser, PR, FR, Logistics, Design, IT, HR, or another event-specific function. It is modeled as a weak entity dependent on EventEditions because available functions are defined per event edition and may differ between event editions.

• Candidate keys: the chosen primary key is the composite key EventEditions key + function_name.
• Attributes:
  • function_name: text, required, function name unique within the same event edition.

Applications

Applications represents applications submitted by students, with motivation letters, for BEST Skopje processes such as applying for a core team function, applying for participation connected to an event edition, applying to become a young member through BOMBA, or applying to become a mentor. It is modeled as a separate entity because applications have their own status and content and must be connected both to the applicant and to the event/function context.

• Candidate keys: application_id is chosen as the primary key because one student can submit multiple applications over time and natural combinations would be less stable.
• Attributes:
  • application_id: numeric identifier, required, unique.
  • motivational_letter: long text, required.
  • application_type: text, required, controlled value such as core team function, event participation, BOMBA young member, or mentor application.
  • application_status: text, required, controlled value such as submitted, accepted, rejected, or withdrawn.

Companies

Companies represents companies that cooperate with BEST Skopje for events, sponsorships, Job Fair participation, or similar event-level cooperation. It is intentionally simple because company contact management and communication are already handled in a separate system called naFRama.

• Candidate keys: c_id is chosen as the primary key because company names can change and may not be unique.
• Attributes:
  • c_id: numeric identifier, required, unique.
  • c_name: text, required.
  • naframa_reference: text, optional but unique when present.

Relationships

Students-Members Specialization

This specialization represents that every member is also a student, while not every student in the system must be a member. It is needed because external students can participate or apply for some activities/events, while members have additional BEST Skopje membership history, responsibilities, and roles.

• Candidate keys: inherited from the supertype.
• Attributes: no relationship attributes.

Members Membership-Type Specialization

This disjoint specialization separates Members into Observers, YoungMembers, FullMembers, and Alumni. It is needed to show the main membership stadiums clearly in the conceptual ER model. The historical periods of these stages are still stored through has_stage.

• Candidate keys: inherited from the supertype.
• Attributes: no relationship attributes.

OrganizationalFunctions Board/NonBoard Specialization

This disjoint specialization separates organizational functions into Board and NonBoard roles. It is needed because Board membership is treated as a function/role rather than as a membership stage, but the model still needs to distinguish Board functions from other coordinator functions.

• Candidate keys: inherited from the supertype.
• Attributes: no relationship attributes.

applies_with

applies_with connects Students to Applications. It represents the fact that each application is submitted by exactly one student, while one student may submit many applications. It is needed to know who wrote the motivation letter and whose application status is being tracked.

• Candidate keys: it is stored as a foreign key from Applications to Students.
• Attributes: no relationship attributes.

for_function

for_function connects Applications to CoreTeamFunctions. It represents applications that target a specific available core team function for an event edition. It is needed so that an accepted application can imply that the student became part of the core team in that function.

• Candidate keys: application_id is sufficient for this relationship since each function application targets at most one core team function.
• Attributes: no relationship attributes.

for_participation

for_participation connects Applications to EventEditions. It represents applications for participation in an EventEdition, such as participant of a Hackathon, as well as applications from full members to become a mentor and observers to become a young member in BOMBA.

• Candidate keys: application_id is sufficient because each application targets one event edition.
• Attributes: no relationship attributes.

available_for

available_for connects CoreTeamFunctions to EventEditions. It is the identifying relationship that states which functions are available for a specific event edition. It is needed because the same function name can appear in many event editions, but the available role belongs to one concrete edition.

• Candidate keys: the relationship contributes the EventEditions key to the weak entity key of CoreTeamFunctions.
• Attributes: no relationship attributes.

edition_of

edition_of connects EventEditions to EventTypes. It represents that each concrete event edition is an edition of exactly one abstract event type. It is needed to distinguish event categories from concrete yearly/dated editions.

• Candidate keys: the relationship contributes the EventTypes key to the weak entity key of EventEditions.
• Attributes: no relationship attributes.

part_of

part_of connects EventSessions to EventEditions. It represents that every event session belongs to one event edition, and an event edition is composed of event sessions. It is modeled as identifying because event sessions do not exist independently of the event edition they are part of.

• Candidate keys: the relationship contributes the event edition key to the weak entity key of EventSessions.
• Attributes: no relationship attributes.

session_inst_of

session_inst_of connects EventSessions to EventSessionTypes. It represents that a concrete event session is an instance of an abstract event session type, such as official opening or coffee break. It is needed so that sessions can be categorized consistently without duplicating type descriptions.

• Candidate keys: the relationship contributes the EventSessionTypes key to the weak entity key of EventSessions.
• Attributes: no relationship attributes.

instance_of

instance_of connects ActivitySessions to ActivityTypes. It represents that a concrete activity session is an instance of an abstract activity type, such as a weekly meeting, working group, FR call, training, or party. It is modeled as identifying because an activity session is defined through its type, start time, and location.

• Candidate keys: the relationship contributes ActivityTypes key to the weak entity key of ActivitySessions.
• Attributes: no relationship attributes.

related_to

related_to connects ActivitySessions to EventEditions. It represents activity sessions that are connected to a specific event edition, such as BEST Course in Summer meetings, FR calls for a Hackathon, or working sessions for an event. It is optional because many activity sessions, such as regular weekly meetings, may not be related to a specific event edition.

• Candidate keys: the natural key is the activity session key because each activity session is related to at most one event edition in the current model.
• Attributes: no relationship attributes.

required_attendance

required_attendance connects MembershipStages to ActivityTypes. It represents which membership stages are expected or required to attend a certain type of activity, such as full members being required to attend full member meetings.

• Candidate keys: ms_name + at_name is the natural primary key because the same membership stage should be listed at most once for the same activity type.
• Attributes: no relationship attributes.

participation

participation connects Students to ActivitySessions. It represents RSVP and attendance checking for activity sessions, such as meetings, workshops, trainings, FR calls, parties, or other internal activities. It is needed because students or members can say they are going and the organization can later record whether they attended.

• Candidate keys: index + activity session key is chosen as the primary key because one student should have at most one participation record for the same activity session.
• Attributes:
  • rsvp_status: text, required, controlled value such as going, not going, maybe, or default = no response.
  • rsvp_at: datetime, optional until an RSVP is submitted; required when an RSVP is recorded.
  • attendance_status: text, required when attendance is checked, controlled value such as present, absent, excused, late, online, left early or not checked.

announces_absence

announces_absence connects Members to ActivitySessions. It represents members announcing that they will be absent from an activity session, with a reason and time of announcement. It is needed for processes where absence from mandatory or important activities should be communicated before the session.

• Candidate keys: member index + activity session key is the natural primary key because a member can have only one active absence announcement per session.
• Attributes:
  • reason: text, required, explanation of the absence.
  • announced_at: datetime, required, timestamp when the absence was announced.

responsible_for

responsible_for connects Members to ActivitySessions. It represents members responsible for organizing or holding an activity session, such as the President holding weekly meetings, HR coordinating training sessions, or event organizers/core team members coordinating event-related activity sessions.

• Candidate keys: member index + activity session key is the natural primary key because the same member should not be linked twice as responsible for the same session.
• Attributes: no relationship attributes.

volunteers

volunteers connects Members to EventSessions. It represents members volunteering for concrete event sessions, such as helping at company stands, coffee breaks, official openings, social activities, or other event-session tasks. It is modeled separately from core team applications because volunteering for a session is operational help, not necessarily an organizing-team function.

• Candidate keys: member index + event session key + volunteer_role is the primary key since one member can volunteer in multiple roles for the same session.
• Attributes:
  • volunteer_role: text, required, description or controlled value of the volunteer task/role.

has_stage

has_stage connects Members to MembershipStages. It stores membership history: which membership stage a member had and during what period. It is needed even though member subtypes exist, because subtypes show the conceptual categories while this relationship records time periods and transitions.

• Candidate keys: member index + ms_name + valid_from is chosen because a member can potentially enter the same stage more than once over time.
• Attributes:
  • valid_from: date, required, start date of the membership stage.
  • valid_to: date, optional for the current active stage; When present, must be greater than or equal to valid_from.

mentorship

mentorship connects FullMembers to YoungMembers. It represents that a young member must have a mentor who is a full member.

• Candidate keys: the young member key is sufficient since each young member has exactly one mentor forever.
• Attributes: no relationship attributes.

is

is connects FullMembers to OrganizationalFunctions. It represents a full member holding an organizational function during a mandate. It is needed because Board membership and coordinator responsibilities are roles held by full members, not separate membership stages.

• Candidate keys: full member index + of_title + mandate is chosen because the same full member can hold different functions and the same function can be held by different full members in different mandates.
• Attributes:
  • mandate: text or date-range label, required, controlled format such as 2025/2026.

cooperation

cooperation connects Companies to EventEditions. It represents cooperation between a company and a specific event edition, such as sponsorship, partnership, Job Fair participation, challenge provider, or similar cooperation. It is intentionally simple because detailed company contacts and communications are outside this project scope.

• Candidate keys: c_id + event edition key + cooperation_type since one company can cooperate with the same event edition in multiple ways.
• Attributes:
  • cooperation_type: text, required, controlled value such as sponsor, partner, participant, challenge provider, or supporter.

Entity-Relationship Model History

Version	Changes
v01	Initial ERModel. No Students entity yet.
v02	Students entity added. Participation in an activity session generalised to Students. Generalised !BOMBAEdition and BOMBA into EventEditions and EventTypes. Added application for participation relationship Applications N:1 EventEditions (which is also for applying for BOMBA as a mentor / young member).
v03	Added MembershipStages entity with a "has_stage" relationship between MembershipStages and Members.
v04	Changed Applications for CoreTeamFunctions cardinality from N:M to N:1 since one unique application should not be used for more than one function. Added an "announces_absence" relationship with attributes reason and announced_at. These attributes were not added to the original "participation" relationship since many absence reasons would be null because many of the ActivitySessions would NOT be mandatory for the specific MembershipStages, and would not need absence announcements. Deleted is_mandatory, since it should be implied through business logic later on and here it is redundant.
v05	Added "required_attendance" relationship that would model what MembershipStages are required to attend what ActivityTypes and would make business logic implementation easier later on.
v06	Fixed relationship between FullMembers and YoungMembers regarding mentorship - a full member can be a mentor of many young members and not vice versa. Fixed relationship between OrganizationalFunctions and FullMembers to be N:M since a full member can hold more than one organizational function and an organizational function can be held by more than one full member in different mandates.
v07	Changed names to attributes and relationships so that all relationships are named and no attributes or relationships share the same name.
v08	Changed the weak entity keys of EventSessions, EventEditions and ActivitySessions to account for edge cases where two sessions or editions could otherwise have the same identifying values and need location/title/start time to distinguish them.

Entity-Relationship Model AI Usage

AI was used during the Entity-Relationship model phase only to review and improve my own initial model. The first versions of the model were created by me, and the AI was used afterwards to inspect the model, compare it with the project requirements, suggest possible improvements, explain unclear modeling choices, and help write the model documentation.

My conversations with AI models, just like now, are usually for challenging my own way of thinking about the project and debating possible inconsistencies thoroughly.

The final modeling decisions were made by me. Some AI suggestions were accepted, such as improving relationship names, clarifying weak entity keys, documenting data requirements, and checking consistency between the ER diagram and the written description. Other suggestions were rejected or adjusted when they did not match the intended scope of the project, for example adding unnecessary application review data, modeling event attendance, or expanding company management.

The changes made based on useful AI suggestions are documented in the model history above. The full AI usage documentation is shown below.

Name of AI service/solution that was used

OpenAI Codex / ChatGPT

URL: ​https://chatgpt.com/

Type of service/subscription: AI conversational and coding assistant, accessed through an OpenAI Plus subscription account.

Final result

Diagram:

Model description:

The final result is ERModel_v08, an Entity-Relationship model for BEST Skopje Hub. The model represents students, members, membership stages, organizational functions, activity sessions, event editions, event sessions, applications, companies, attendance, absence announcements, volunteering, mentorship, and event/company cooperation.

AI was not used to generate the final model from scratch. It was used to review my own model, suggest alternatives, explain unclear relationship/cardinality choices, and help improve the written data requirements. The final model decisions were selected and adjusted by me according to the actual scope of the BEST Skopje Hub project.

Entire AI usage log

The following is a line-by-line log of the relevant interactions with the AI during the ER modeling phase.

Prompt 1: Are you able to read the latest version ER model in this folder?

AI response 1: The AI inspected the project folder, identified the latest ER model file at the time, and confirmed that it could read the TerraER XML structure. It listed the main entities and relationships found in the model.

Prompt 2: Can you tell me if the ER model fulfills my requirements and if not, what is missing, and what is redundant?

AI response 2: The AI compared the ER model with the BEST Skopje Hub requirements and the attached project description. It identified which concepts were already represented and suggested possible missing or redundant concepts, including attendance, applications, core team functions, member stages, event editions, and company cooperation.

Prompt 3: I responded to the AI suggestions and explained which features were intentionally out of scope, such as attendance for events, application review data, and detailed company management.

AI response 3: The AI revised its assessment based on my clarifications. It agreed that several earlier suggestions were unnecessary for this project and narrowed the advice to the parts that mattered for the ER model.

Prompt 4: How could event/session eligibility be modeled in the ER Model?

AI response 4: The AI explained that eligibility could be modeled either as an attribute, as a relationship to MembershipStages, or through a separate audience group entity, but also explained that this could remain business logic if it was not configurable data.

Prompt 5: I'm confused about the relationship between FullMembers and OrganizationalFunctions. Should it be N:M or 1:N or N:1?

AI response 5: The AI explained that the relationship should be N:M because one full member can hold multiple organizational functions over time, and one organizational function can be held by different full members in different mandates.

Prompt 6: Should valid_from and valid_to dates be added? I thought mandate implies that.

AI response 6: The AI explained that mandate can be enough for the project if it represents the time period of holding a function. It suggested that valid_from and valid_to would only be necessary for more precise temporal tracking, which I decided not to include.

Prompt 7: All relationships should be named and duplicate attribute/relationship names need to be fixed.

AI response 7: The AI first suggested broad renaming, then corrected the suggestion after I clarified that only duplicate names needed to be changed. It helped identify duplicate names and suggested clearer names for relationships and attributes.

Prompt 8: I wanted better names than "has" for identifying relationships such as ActivitySessions and ActivityTypes.

AI response 8: The AI suggested names such as instance_of, edition_of, part_of, session_inst_of, and available_for, which helped improve the readability of the ER model.

Prompt 9: I asked the AI to help organize the data requirements for ERModel_v07 based on my existing ER model.

AI response 9: The AI extracted the entities, relationships, attributes, and keys from my existing model and suggested a draft structure for the data requirements. I reviewed, corrected, and adapted the explanations, candidate keys, and logical attribute types before including them in the final documentation.

Prompt 10: Write the data requirements in TracWiki notation.

AI response 10: The AI converted the data requirements into TracWiki-style headings and bullet lists.

Prompt 11: I asked about the key of EventSessions, especially because EventSessions is weakly identified by both EventEditions and EventSessionTypes.

AI response 11: The AI corrected its earlier interpretation and explained that the full key of EventSessions should include the EventEditions key, the EventSessionTypes key, and the partial key of EventSessions.

Prompt 12: Check ERModel_v08 and the newest data requirements.

AI response 12: The AI compared the actual ERModel_v08 file with the written data requirements and found several small mismatches, such as stale key wording and naming inconsistencies. These were corrected in the documentation.

Prompt 13: I repeatedly asked the AI to check the model and text again.

AI response 13: The AI rechecked the model and text multiple times, verifying that all entities, relationships, attributes, weak keys, relationship names, and data requirement descriptions matched. It found and helped fix small text issues.

Prompt 14: I asked why names such as EventEditions were being rendered as links in TracWiki.

AI response 14: The AI explained that TracWiki automatically links CamelCase words and that adding an exclamation mark before such words prevents automatic linking.

Prompt 15: I asked the AI to rewrite the text with exclamation marks before CamelCase entity names.

AI response 15: The AI generated a TracWiki-safe version of the text where CamelCase model names were escaped with !, preventing unwanted wiki links.