Entity-Relationship Model v.10

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: student_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:
  • student_index: numeric 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 student_index is used.

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 student_index from Members.

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 student_index from Members.

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 student_index from Members.

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 student_index from Members.

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_id and ms_name. ms_id is chosen as the primary key because membership stage names are text labels. ms_name is unique.
• Attributes:
  • ms_id: numeric identifier, required, unique.
  • 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_id and of_title. of_id is chosen as the primary key because function titles are text labels. of_title is unique.
• Attributes:
  • of_id: numeric identifier, required, unique.
  • 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_id from OrganizationalFunctions.

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_id from OrganizationalFunctions.

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, and informative meetings. It is modeled separately from activity sessions because many concrete sessions can occur for the same activity type.

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

Locations

Locations represents reusable places where event editions, event sessions, and activity sessions can happen.

• Candidate keys: l_id and l_name. l_id is chosen as the primary key because location names are text labels. l_name is unique.
• Attributes:
  • l_id: numeric identifier, required, unique.
  • l_name: text, required, unique.

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.

• Candidate keys: as_id is chosen as the primary key because it keeps references to a concrete session simple.
• Attributes:
  • as_id: numeric identifier, required, unique.
  • as_start_time: timestamp, required, valid future or past timestamp depending on whether the session is planned or completed.

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_id and et_name. et_id is chosen as the primary key because event type names are text labels. et_name is unique.
• Attributes:
  • et_id: numeric identifier, required, unique.
  • 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.

• Candidate keys: ee_id is chosen as the primary key because titles and start times can change or repeat.
• Attributes:
  • ee_id: numeric identifier, required, unique.
  • ee_start_time: timestamp, required, valid event start time.
  • ee_end_time: timestamp, required, must be greater than or equal to ee_start_time.
  • ee_title: text, required, title of the event edition.

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.

• Candidate keys: est_id and est_name. est_id is chosen as the primary key because event session type names are text labels. est_name is unique.
• Attributes:
  • est_id: numeric identifier, required, unique.
  • est_name: text, required, unique.
  • est_description: text, required, explanation of the session type.

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, because an event session exists only as part of a specific event edition.

• Candidate keys: the chosen primary key is the event edition key + es_no, where es_no is the partial key inside one event edition.
• Attributes:
  • es_no: numeric, required, unique inside one event edition.
  • es_start_time: timestamp, required, valid timestamp within or near the event edition period.
  • es_title: text, required, title of the event session.
  • needed_no_volunteers: numeric, required, non-negative integer.

EventFunctions

EventFunctions represents available organizing 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 event edition key + ef_no, where ef_no is the partial key inside one event edition.
• Attributes:
  • ef_no: numeric, required, unique inside one event edition.
  • ef_name: text, required.

Applications

Applications represents applications submitted by students, with motivation letters, for BEST Skopje processes such as applying for an event 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 event 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.

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.

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.

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.

for_function

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

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.

available_for

available_for connects EventFunctions 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 identifies EventFunctions through event edition key + ef_no.

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.

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 identifies EventSessions through event edition key + es_no.

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.

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.

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 specific event, 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.

takes_place_at

takes_place_at connects ActivitySessions to Locations. It represents the location where a concrete activity session is held.

hosted_at

hosted_at connects EventEditions to Locations. It represents the one or more locations connected to an event edition.

• Candidate keys: event edition key + location key.

held_at

held_at connects EventSessions to Locations. It represents the location where a concrete event session is held.

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: student_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 deault = no response.
  • rsvp_at: timestamp, 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 student_index + activity session key is the natural primary key if a member can have only one active absence announcement per session.
• Attributes:
  • reason: text, required, explanation of the absence.
  • announced_at: timestamp, 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 coordinating event-related activity sessions.

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

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 event function applications because volunteering for a session is operational help, not necessarily an organizing-team function.

• Candidate keys: member student_index + event session key is chosen because one member is stored once for the same event session.
• Attributes:
  • volunteer_role: text, required.

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 student_index + ms_id + 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.

holds_function

holds_function 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 student_index + of_id + mandate_year 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_year: numeric, required, year of the organizational function mandate.

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 is chosen because one company is stored once per event edition in this model.
• 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.
v09	Removed "required_attendance" relationship after realising it is redundant.
v10	Changed string primary keys to numeric identifiers where needed. Renamed the student key from index to student_index. Added Locations as an entity. Changed ActivitySessions, EventEditions, EventSessionTypes, EventSessions and EventFunctions to use numeric identifiers/partial numeric identifiers. Changed EventSessions, EventEditions and ActivitySessions to not be weak entities. Renamed CoreTeamFunctions to EventFunctions and renamed the relationship between FullMembers and OrganizationalFunctions from is to holds_function. Changed if some relationships are mandatory or not based on logic.

AI Use

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.

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 available on the following page: ERModelAIUsage.