• Database
• Appearance
• Code

Design

There are many aspects of the system to design. Primarily, these are the database behind the system, the appearance, and the actual source code driving it. These will all be covered in the following sections.

Database

The first task when designing any database is to identity the core entities and relationships involved, and construct an Entity-Relation (ER) Diagram based on that.

Initially, the following entities were identified, by reviewing the A3 sheets used by the current booking system.

• Activity, e.g. Squash, Soccer
• Venue, e.g. Squash Court 1, Sports Hall 2
• Venue Group, e.g. Squash Courts, Sports Halls
• Booking, representing a booking for a given venue at a given time
• Slot, representing a particular time slot, e.g. 10:00-10:30 (does not specify the date)
• User, representing a user's online account

Figure: Initial Entity-Relation Diagram for the database

The initial ER diagram constructed is shown in Figure . One relationship worth discussing is activity_venue, which specifies which activities are permitted in which venues. (So that, for example, squash is only permitted in squash courts.) The first conceptual problem with this schema is the relationships between booking, venue, and activity. Figure shows a many-one relationship between booking and venue, and between booking and activity. However, these relationships only require that each booking is for an existing activity, in an existing venue. Therefore, a booking entry specifying the activity ``squash'' in a venue of type ``sunbed'' would be perfectly legal under this schema.

Therefore, the schema was updated, to replace the two aforementioned relationships with a single many-one relationship between booking and activity_venue. This does not actually change the database, because MySQL has no support for foreign keys; it is only a better way of conceptually viewing the relationship, and would make a difference in a DBMS that supports foreign keys. Now, the pair (activity_id, venue_id) from the booking relation must also exist in the activity_venue relation. This will clearly make the example of playing squash in a sunbed illegal, since this combination would never exist in the activity_venue relation.

Another problem with this schema is the simple representation of venues and the activities each venue can be used for. There is no problem for simple venues such as squash courts or sunbeds, but sports halls cause a problem. For example, Sports Hall 1 can be divided in many ways, as shown in Figure . The following points should also be made:

• The halls can theoretically be divided up in any possible way. For example, at any one time, part A may be used for soccer, while part E is used for a basketball half court!basketball, and parts 7 and 8 are in use for badminton courts.

• Parts C-F are half courts for basketball; these are often used if only a small number of people wish to play basketball, since it means there are more badminton courts free, which are often in high demand. If a large enough number of people wish to play, they will simply have to book two adjacent parts at the same time.

Figure: Example of how sports halls can be used

This problem was solved, after investigation into possible solutions, using the concept of a compound venue. To easily allow bookings for any permitted sport in such venues, Sports Hall 1A is defined as a compound venue consisting of badminton courts 1-4, and so on.

In terms of the database, this required the addition of the compound_venue relation. This defines a many-many relationship between venue and venue, as shown in Figure (page ). This is used to specify venues which are actually comprised of multiple, smaller venues.

A complication arises when a booking for a compound venue is made. When such a venue is booked, a number of entries are automatically placed in the booking relation for the individual parts of the compound venue, as shown in Table . (Fields such as date, time, and user have been omitted for clarity.) This creates a many-one relationship between booking and booking, as shown in Figure . Note that the sub-booking entries are not strictly necessary -- they could be determined by consulting the compound_venue relation for the venue specified by the master booking entry. However, the sub-bookings make operations that check if a venue is booked at a specified time much simpler, because the SQL would be similar to the following:

Before the support for compound venues was introduced, the (Activity, Venue) pair was required to exist as a valid entry in activity_venue, as discussed earlier. (There was no explicit foreign key constraint enforcing this, because MySQL does not support referential integrity constraints.) With compound venues, this restriction can no longer be enforced, as booking entries will be inserted specifying (Activity, Venue) combinations which cannot be made directly. For example, if an indoor soccer pitch is a compound venue comprising four badminton courts, these extra bookings will be for soccer, but in a venue which is actually a badminton court.

Such compound bookings have other minor implications relating to the database integrity. If a row for a part of a compound venue were lost from the booking relation, that part could be double-booked for an activity permitted in that venue. Using the example in Table again, if booking id 3 were deleted, it would be possible for a user to book badminton in Sports Hall 1A (2), even though the whole of Sports Hall 1A was booked for soccer.

Also, it introduces functional dependencies into the booking relation which mean it is no longer normalised, such as master_id  slot_id, venue_id. Because this is not a trivial dependency, and master_id is not a superkey of booking, this implies the booking relation is no longer in BCNF (Boyce-Codd Normal Form) [#!korth!#, page 224]. Alternatively, separate relations for bookings and the sub-bookings could be used, as shown in Table . (The left hand relation shown would, as with Table , have more columns, such as date, time, and user name.) In fact, in this schema, the sub-booking relation is completely unnecessary, since it would be as easy to determine the sub-bookings from the compound_venue relation, as discussed earlier. Therefore, this schema adds a relation unnecessarily, because if two relations are to be used, it would be better to use booking and compound_venue, rather than booking and an otherwise unnecessary relation.

Yet another alternative would be to include all the sub-bookings in the booking relation, but not to include a master_id field at all, as shown in Table . This is possible since the sub-bookings can be identified by finding all bookings with the same date and time, as the master booking, and which have a venue which is a part of the venue from the master booking:

The added complication this would require in the code is likely to be worse than using a relation that is not fully BCNF.

Despite these minor issues, the database design overall is a good one, provided the user interface does not allow the database to enter an inconsistent state, which this system does not. In some cases, a design decision was made to choose convenience over full normalisation, but there are few of these cases, and the implications are all fairly small.

As an example of how the system is careful not to let the database enter an inconsistent state, we shall consider the code which deletes a booking. The code is similar to the following, where $n represents the booking_id of the booking to delete.

DELETE FROM booking WHERE master_id = $n
DELETE FROM booking WHERE booking_id = $n AND master_id IS NULL

This clearly shows that it is not possible to delete only a single part of a compound booking, since the first DELETE will delete all parts, and the second will only delete a booking if it is a master booking (because its master_id is NULL). Therefore, even if a bug in the system (for example, a parameter passed incorrectly by the user interface) meant that $n contained the ID number of a single part of a compound booking, that part would not be deleted by this code.

Using the example from Table , with $n=3, no entries would be deleted, because there are no tuples with master_id equal to 3, nor are there any with booking_id equal to 3 and a NULL master_id. The correct way to delete this booking is with $n=1, which using similar logic, will correctly delete the master booking and all the sub-bookings.

The only case in which a single part of a compound booking could be deleted is if the DBMS crashed at a point when a DELETE statement had only partially completed execution. This problem can be avoided if a DBMS supporting transactions and ACID properties [#!korth!#, page 240] is used.

Due to MySQL's lack of support for referential integrity, the system must also be careful to implement these constraints in the business layer.

The functional dependencies are listed in full in Appendix . They show that, with the exception of booking, all relations in the database conform to BCNF. The booking relation conforms to 2NF (Second Normal Form) but nothing higher than that [#!db21!#, page 7-8].

Figure: Final Entity-Relation Diagram for the database

Figure shows the final ER diagram for the database used by this project, and Appendix shows the full database schema.

Appearance

There were two main factors when designing the appearance of the website:

• Usability
• Consistent look-and-feel when compared to the existing Sports Centre website at http://www.leeds.ac.uk/sport

The latter was very easy to achieve, due to the simple design of the Sports Centre website -- the website developed by this project simply uses the same background image and colour scheme, as shown in Figure and Figure . This is only a very simplistic level of similarity, but it has proved to be sufficient to give users the impression that the booking system is connected to the rest of the Sports Centre website. It was chosen to omit the vertical navigation bar, present on the Sports Centre website, from the booking system, simply because some large tables are used, and this navigation bar would occupy too much space on the screen.

Figure: Screenshot of the Sports Centre website

Figure: Screenshot of the Online Booking system

The former involved analysing common operations on the website formally, using HCI principles, along with simply asking potential users of the system what they believed could be improved. Further HCI analysis is included in Section (page ), and some discussion of the appearance and usability of the site is included in Chapter .

Code

In a way, the code is actually the simplest part of the design for a project like this one -- the majority of the code is either querying the database (which was covered in Section ) or sending output to the browser (which was covered in Section ).

Each page of the site is contained within a different source file, so each file is fairly small and self-contained (the largest source file is 15KB). Additionally, a library of common functions was created, and included using the PHP include statement from each source file. Functions in this file range from very general functions, to very specific functions, as shown below.

• General functions, used by almost all pages:
  • dbconnect(): Connects to the database, and stored the connection handle in a global variable $dbh

  • site_header(): Prints out the common header for each HTML page

  • check_login(): Checks that the user is logged in (see Section ) and shows a login form otherwise. This function makes it very easy to keep the site secure, since on any page requiring an authenticated user, a call to this function must simply be made near to the beginning of the program.

• Specific functions, used only by a small number of pages:
  • find_booking(): Checks whether there is a booking at a given time in a given venue

  • send_intro_email(): Sends an email, either when a user signs up, or when they request that their password be reset

This only shows a very small proportion of the total number of functions; in total there are approximately 40 functions.