As a travel business (or indeed any business), it is a basic necessity to be able to create reports in order to operate and manage the company effectively. In my experience, reporting is usually very bespoke to the business in question. Different companies have different things that make them tick - load factor, profit, exposure, passenger numbers etc, and even greater variation in terms of operational needs and practices.
Recently I have been helping users of the Tiger Bay Tour Operator system to get to know the data they have available to them and create some bespoke reporting for their businesses. The Tiger Bay system includes a reporting suite with a good range of operational, financial and management reports that can be run within the user interface. Further reporting opportunities are provided in the form of partially de-normalised MS-SQL views.
1) Know your entities
2) Understand relationships
One-to-many – One booking has four passengers. Join the tables together and you will get four results. It could look like you now have four bookings if you simply count the results, or four times the revenue if you sum the results.
One-to-one – Each booking has one and only one booking reference. Each booking reference only relates to one booking. Normally you will find both data in the same table, but not always. If they are separate tables and you join them correctly, it should not alter the number of results you get.
Many-to-many – Each customer has multiple interests (e.g. ‘Skiing’, ‘Walking’ etc). Each interest is related to multiple customers. Join these correctly and you will get one row per combination, just like one-to-many.
3) Learn about joining tables
Many-to-many relationships are normally handled through a third ‘bridging’ or ‘junction’ table. So there is the customers table, the interests table, and then a third table where each row contains a relationship between a specific customer and a specific interest.
How you join will affect the number of results you get. If you use an inner join, you only get results that exist in both tables (e.g. you join the client table to the booking table. You get one result for each booking that each client has made. Any client that has not made a booking, and any booking with no client is excluded from the results). If you use a left or right outer join, all the records from one table will show, but only the ones with a matching value in the other table will show (e.g. A list of all clients whether or not they have a booking, showing each booking if there is one. Any bookings without a client do not show.). You can also use a full outer join which shows all records from both tables.