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.