Introduction
Recently, for a large website commissioned by the local agency for tourism, I had to design a system for storing Skibus timetables. The requirements were straightforward:
- store multiple bus lines;
- each line is a round trip, therefore it has two directions (outbound and return). But the route may not be the same;
- each line is effective between two dates
The aim of the website was to provide timetables to regular users using standard HTML tables instead of static PDF files, allowing also mobile devices to browse them without hassle. The users could also choose to browse the timetables by line or by bus stop. Moreover, for each bus stop, the site should have allowed the automatic generation of A3 PDF timetables for later printing.
The Process
The picture shows the timetable of the line N°4, with the two directions. Each direction has 16 stops, but if you look carefully you’ll notice that they’re not the same. Also you can notice that the time interval between the same bus stops on the two directions is not always equal (e.g. look at Pozza Aloch Buffaure).
This picture has been used to build a model for the database. The entities of the final design are outlined in the following picture:
We can clearly indentify the main entities (tables) of our data model, their relationships and attributes.
- The main entity of our model is obviousy the Line, outlined in blue. The attributes of this entity are the color (in hex format) and the starting and ending dates.
- Each Line holds two Direction entities (in red). The attributes of this entity are the type of direction (outbound or return), which can be represented by a boolean field, its name and the Line it belongs.
- In azure we have the Stop entity which uniquely identifies each bus stop. Its attributes include the name and the coordinates. The relationship between Direction and Stop is clearly many-to-many because each Direction has many Stop, but a Stop can belong to many Direction.
- Each Direction has many Route (in green), each one matches a complete bus route. Route attributes are the corresponding Direction and a field for storing special notes (e.g. running only on working days/holidays and Sundays etc.). It’s important to notice that a Route can cover all the stops, but it can also start in a different bus stop or end before its completion. This fact suggests that we have to relate the Route entities to Stop entities, using a many-to-many relationship. The actual time will be expressed as a time field in the pivot table.
The resulting EER model of the database is visible in the following figure and it uses Crow’s foot as relationship notation.
On the front-end, for each Direction we use a foreach loop to print the header (in other words, the Stop that belongs to the Direction) and then we loop through each Route and through each related Stop to see which cell we have to fill with the time. The markup generation in this section is our only problem, in fact we have to draw the table first from top to bottom and then from left to right (the header on the first column and then each Route) to match the timetable in written format. However we know that HTML tables are built first from left to right and then from top to bottom (row after row). The way I managed to solve this problem was to generate the table the standard way, printing the header on the first row and then each Route on a different row (including the empty cells) and then use jQuery on document.ready to transpose the table. The code is the following
$('table').each(function () {
var $this = $(this);
var newrows = [];
$this.find('tbody tr').each(function () {
var i = 0;
$(this).find('td,th').each(function () {
i++;
if (newrows[i] === undefined) {
newrows[i] = $('<tr></tr>');
}
newrows[i].append($(this));
});
});
$this.find('tr').remove();
$.each(newrows, function () {
$this.append(this);
});
});This is the final result:
