Database Schema (Models)

Relevant source files

The following files were used as context for generating this wiki page:

• finder/models.py
• finder/views.py
• finder/admin.py
• generate_sample_data.py
• finder/forms.py
• README.md

The database schema for the Campuspal Room Finder application is defined using Django's Object-Relational Mapper (ORM). It consists of two primary models, Room and ClassSchedule, which work together to store all necessary information about campus rooms and their respective schedules. This data layer is the foundation for the application's core features, including room searching, timeline visualization, and data management.

The models are designed to be populated from external Excel files, providing a straightforward way to manage and update campus data. The schema is optimized for read-heavy operations, such as fetching all schedules for a given room on a specific day, through the use of foreign key relationships and database indexes.

Sources: finder/models.py, README.md:195-201

Entity-Relationship Diagram

The diagram below illustrates the relationship between the Room and ClassSchedule models. A single Room can have multiple ClassSchedule entries, representing a one-to-many relationship.

This diagram shows that each ClassSchedule must be associated with exactly one Room, while a Room can be associated with zero or more ClassSchedule entries.

Sources: finder/models.py:7-68

Models

The application's data structure is defined by two Django models in finder/models.py.

Room Model

The Room model stores the physical attributes and metadata for each room on campus.

Sources: finder/models.py:7-29

Fields

Field	Type	Constraints	Description
name	CharField(max_length=100)	Unique with building	The identifier for the room (e.g., "A101").
building	CharField(max_length=100)	Unique with name	The building where the room is located (e.g., "Apollo Building").
capacity	PositiveIntegerField	default=0	The maximum seating capacity of the room.
amenities	JSONField	default=list	A JSON array of strings listing available amenities (e.g., ["Projector", "Whiteboard"]).
photo_url	URLField	blank=True, null=True	An optional URL to an image of the room.

Sources: finder/models.py:8-13

Constraints and Meta Options

• A UniqueConstraint ensures that the combination of name and building is unique across the database, preventing duplicate room entries.
• The verbose_name is set to "Room" and verbose_name_plural to "Rooms" for better readability in the Django admin interface.

# finder/models.py:15-21
    class Meta:
        verbose_name = "Room"
        verbose_name_plural = "Rooms"
        constraints = [
            models.UniqueConstraint(
                fields=[
                    "name",
                    "building"],
                name="unique_room_building")]

Sources: finder/models.py:15-21

ClassSchedule Model

The ClassSchedule model represents a single booking or class event, linking a course to a specific Room at a scheduled time.

Sources: finder/models.py:32-68

Fields

Field	Type	Constraints	Description
course	CharField(max_length=50)		The course code (e.g., "CS101").
subject	CharField(max_length=100)		The full subject name (e.g., "Intro to Programming").
day	CharField(max_length=3)	choices=DAY_CHOICES	The day of the week for the class, restricted to "Mon", "Tue", "Wed", "Thu", "Fri".
start_time	TimeField		The time the class begins.
end_time	TimeField		The time the class ends.
room	ForeignKey(Room)	on_delete=models.CASCADE	A required foreign key linking to the Room where the class is held. The related_name is "schedules".
lecturer	CharField(max_length=100)		The name of the lecturer.
is_cancelled	BooleanField	default=False	A flag to indicate if the class has been cancelled.

Sources: finder/models.py:33-51

Relationships and Indexes

• Foreign Key: The room field establishes a many-to-one relationship with the Room model. If a Room is deleted, all its associated ClassSchedule entries are also deleted (on_delete=models.CASCADE). This relationship is crucial for querying all schedules for a given room.
• Database Indexes: Two indexes are defined to improve query performance:
  1. On (day, start_time, end_time): Speeds up searches for schedules on a specific day and time.
  2. On (room, is_cancelled): Optimizes fetching schedules for a particular room, especially when filtering out cancelled classes.

# finder/models.py:56-61
        indexes = [
            models.Index(fields=["day", "start_time", "end_time"]),
            models.Index(fields=["room", "is_cancelled"]),
        ]

Sources: finder/models.py:56-61

Data Flow and Management

The models are primarily managed through data uploads and are heavily utilized by the search functionality and admin interface.

Data Population

Data is loaded into the Room and ClassSchedule models from .xlsx files. This process can be initiated via a staff-only upload page or through management commands.

The generate_sample_data.py script provides a reference for the expected Excel file structure and content, including how amenities should be formatted as a JSON string.

The upload_excel view in finder/views.py handles the file processing. It reads the Excel files using pandas, clears existing data, and creates new Room and ClassSchedule objects.

The following flowchart shows the data loading process from the upload page:

Model Usage in Application Logic

• Search: The main search_rooms view queries the Room model based on the selected building. It uses prefetch_related('schedules') to efficiently load all related ClassSchedule objects for the filtered rooms in a single additional query, avoiding the N+1 problem.
• Forms: The RoomSearchForm dynamically populates its building choices by querying the Room model for all distinct building names.

Sources: finder/views.py:27-30, finder/forms.py:46-51

Admin Interface

Both models are registered with the Django admin site, providing a user interface for administrators to view, create, edit, and delete records.

• RoomAdmin: Displays a list of rooms with their name, building, and a calculated count of scheduled classes. It allows filtering by building.
• ClassScheduleAdmin: Provides a comprehensive list view of all schedules with filtering by day, cancellation status, and building. It uses select_related('room') to optimize the retrieval of room information.

Sources: finder/admin.py:5-36

Summary

The database schema, centered around the Room and ClassSchedule models, is a well-structured and efficient core for the Campuspal Room Finder. The one-to-many relationship between rooms and schedules, combined with strategic indexing and optimized querying (prefetch_related), ensures performant data retrieval for the application's primary function of displaying room availability timelines. The data import mechanism from Excel files simplifies data management, making the system robust and easy to maintain.