Entity-Relationship Model v.02

Diagram

Data Requirements

Entities

Users

Description: Users represents all individuals who interact with the system. This includes teaching staff who reserve resources for lectures and research, students who view availability and access permitted resources, and faculty administrators who manage the system. The entity is central to tracking who makes reservations and who approves them.

Candidate Keys:

• Primary key: user_id (chosen as a surrogate key for efficiency and to avoid issues with changing natural identifiers)
• Alternative candidates: email (unique per user, but may change)

Attributes:

Attribute	Data Type	Constraints	Description
user_id	INTEGER	PK, NOT NULL	Unique identifier for each user
first_name	VARCHAR(128)	NOT NULL	User's first name
last_name	VARCHAR(128)	NOT NULL	User's family name
email	VARCHAR(128)	NOT NULL, UNIQUE	Institutional email address used for login
password	VARCHAR(128)	NOT NULL	Hashed password for authentication

UserTypes

Description: UserTypes categorizes users into different roles within the system. This entity enables role-based access control, distinguishing between teaching staff, students, and faculty administrators. Each role has different permissions for viewing, reserving, and managing resources.

Candidate Keys:

• Primary key: type_id (surrogate key for referential integrity)
• Alternative candidates: type_name (unique, but surrogate preferred for foreign key references)

Attributes:

Attribute	Data Type	Constraints	Description
type_id	INTEGER	PK, NOT NULL	Unique identifier for each user type
type_name	VARCHAR(128)	NOT NULL, UNIQUE	Name of the role (e.g., 'Student', 'Teaching Staff', 'Administrator')
description	VARCHAR(128)	NOT NULL	Detailed description of the role's permissions and purpose

Resources

Description: Resources represents all physical and digital items that can be reserved within the faculty. Physical resources include laboratory equipment, projectors, rooms, and specialized tools. Digital resources may include software licenses, virtual machines, or online services. This entity is central to the reservation system. Availability windows are stored directly on the resource for simplicity.

Candidate Keys:

• Primary key: resource_id (surrogate key for efficiency)
• Alternative candidates: name (if unique within the system)

Attributes:

Attribute	Data Type	Constraints	Description
resource_id	INTEGER	PK, NOT NULL	Unique identifier for each resource
name	VARCHAR(128)	NOT NULL	Descriptive name of the resource
description	VARCHAR(128)	NOT NULL	Detailed description including specifications, capacity, or usage instructions
available_from	TIME	NOT NULL, DEFAULT '08:00'	Daily start time when the resource becomes available
available_to	TIME	NOT NULL, DEFAULT '20:00'	Daily end time when the resource stops being available
available_weekends	BOOLEAN	NOT NULL, DEFAULT FALSE	Whether the resource is available on Saturday and Sunday

ResourceTypes

Description: ResourceTypes categorizes resources into different types for easier navigation and filtering. Examples include 'Laboratory Equipment', 'Classroom', 'Software License', 'Projector', etc. The is_physical attribute distinguishes between physical resources (that have a location) and digital resources.

Candidate Keys:

• Primary key: type_id (surrogate key for referential integrity)
• Alternative candidates: type_name (unique, but surrogate preferred)

Attributes:

Attribute	Data Type	Constraints	Description
type_id	INTEGER	PK, NOT NULL	Unique identifier for each resource type
type_name	VARCHAR(128)	NOT NULL, UNIQUE	Name of the category (e.g., 'Projector', 'Laboratory', 'Software')
is_physical	VARCHAR(128)	NOT NULL	Indicates if resources of this type are physical ('Y') or digital ('N')

Locations

Description: Locations stores information about physical places where resources can be found. This enables users to know where to pick up or use a resource. Only physical resources are linked to locations; digital resources do not require this association.

Candidate Keys:

• Primary key: location_id (surrogate key for efficiency)
• Alternative candidates: (building, room) composite key

Attributes:

Attribute	Data Type	Constraints	Description
location_id	INTEGER	PK, NOT NULL	Unique identifier for each location
building	VARCHAR(128)	NOT NULL	Building name or code (e.g., 'TMF', 'FINKI-A')
room	VARCHAR(128)	NOT NULL	Room number or name within the building

Reservations

Description: Reservations is the core transactional entity that records each booking of a resource by a user. It tracks the time period, status (pending, approved, rejected, completed), and purpose of each reservation. This entity connects users with resources and enables conflict detection and usage analytics. Recurring reservations are linked together via a shared recurrence_group_id, with the actual recurrence pattern logic handled by the application backend.

Candidate Keys:

• Primary key: reservation_id (surrogate key for unique identification)
• Alternative candidates: None suitable (combination of user, resource, and time could work but is complex)

Attributes:

Attribute	Data Type	Constraints	Description
reservation_id	INTEGER	PK, NOT NULL	Unique identifier for each reservation
start_time	TIMESTAMP	NOT NULL	When the reservation begins
end_time	TIMESTAMP	NOT NULL	When the reservation ends (must be after start_time)
status	VARCHAR(128)	NOT NULL	Current status: 'pending', 'approved', 'rejected', 'completed', 'cancelled'
purpose	VARCHAR(128)	NOT NULL	Description of why the resource is being reserved
created_at	TIMESTAMP	NOT NULL	When the reservation was created in the system
recurrence_group_id	UUID	NULL	Groups reservations that belong to the same recurring series; NULL for one-time reservations

Relationships

has_type (Users - UserTypes)

Description: Each user is assigned exactly one user type that determines their role and permissions in the system. A user type can be assigned to many users. This is a mandatory relationship for users (total participation) since every user must have a defined role.

Cardinality: N:1 (Many users to one user type)

Participation:

• Users: Total (every user must have a type)
• UserTypes: Partial (a type may exist without assigned users)

makes (Users - Reservations)

Description: Users create reservations for resources. One user can make multiple reservations over time, but each reservation is made by exactly one user. This relationship tracks who requested each reservation.

Cardinality: 1:N (One user makes many reservations)

Participation:

• Users: Partial (not all users make reservations)
• Reservations: Total (every reservation has a creator)

approves (Users - Reservations)

Description: Faculty administrators approve or reject reservations. This relationship is separate from 'makes' to distinguish between the user who requested a reservation and the administrator who processed it. One administrator can approve many reservations.

Cardinality: 1:N (One administrator approves many reservations)

Participation:

• Users: Partial (only administrators approve)
• Reservations: Partial (pending reservations have no approver yet)

reserved_in (Reservations - Resources)

Description: Each reservation is for exactly one resource. A resource can have many reservations over time (though not overlapping for the same time period). This is a mandatory relationship for reservations since every booking must specify what is being reserved.

Cardinality: N:1 (Many reservations to one resource)

Participation:

• Reservations: Total (every reservation must be for a resource)
• Resources: Partial (a resource may have no reservations)

categorized_as (Resources - ResourceTypes)

Description: Each resource belongs to exactly one resource type for categorization purposes. This enables filtering and determines whether the resource is physical (requires location) or digital. The relationship is mandatory for resources.

Cardinality: N:1 (Many resources to one resource type)

Participation:

• Resources: Total (every resource must have a type)
• ResourceTypes: Partial (a type may exist without resources)

located_at (Resources - Locations)

Description: Physical resources are associated with a location where they can be found or used. Digital resources do not have locations. One location can house multiple resources (e.g., a lab room with multiple pieces of equipment).

Cardinality: N:1 (Many resources to one location)

Participation:

• Resources: Partial (only physical resources have locations)
• Locations: Partial (a location may have no resources assigned)

Entity-Relationship Model History

Version	Date	Changes
v01	2025-01-19	Initial version with 8 entities and 8 relationships
v02	2026-02-05	Removed RecurrencePatterns entity; added recurrence_group_id to Reservations for simpler recurring reservation handling (pattern logic moved to backend). Removed AvailabilitySlots entity and available_during relationship; added available_from, available_to, available_weekends attributes to Resources.