Application Development

Scope

This phase concerns itself with implementing the use cases defined in P3 of the project, found at: https://develop.finki.ukim.mk/projects/routemk/wiki/ApplicationDesign

Source

The source code for the application can be found at the following link ​https://github.com/KikoTheFinker/RouteMK, and includes setup through:

• Docker (recommended, especially for the DB)
• Native (no containers)

---

Indexes

To optimize query performance and reduce full table scans as data volume grows, the following indexes were added based on real usage patterns in analytical queries, views, and joins.

1. Most queries join trip and route using route_id, for example in company_performance_view, top_selling_routes_view, ticket statistics, and weighted usage reports. Without this index, every join would trigger a sequential scan of the trip table. With the index, the database can directly locate all trips for a given route.

CREATE INDEX idx_trip_route_id ON trip(route_id);

---

2. These two indexes accelerate lookups and joins involving route endpoints. They support queries that find trips between specific locations, which for our application is especially iseful to detect subroutes, one of the most important features. They're also useful in displaying routes with origin - destination metadata.

CREATE INDEX idx_route_from_location_id ON route(from_location_id);
CREATE INDEX idx_route_to_location_id ON route(to_location_id);

---

3. Filtering by trip status (e.g., 'COMPLETED', 'NOT_STARTED') is common in ticket statistics and reporting queries. This index ensures that the database can filter relevant trips quickly instead of scanning the full trip table. It's especially important in analytical use cases and dashboards.

CREATE INDEX idx_trip_status ON trip(status);

---

4. Views such as company_performance_view and top_selling_routes_view frequently join route and transport_organizer through transport_organizer_id. This index speeds up company-based route lookups, revenue calculations and per-organizer reporting. So, this index is useful for when we're calculating company metrics/revenue by organizer.

CREATE INDEX idx_route_transport_organizer_id ON route(transport_organizer_id);