8 Replies Latest reply on Jun 9, 2016 10:35 AM by alexisp

    Database structure for travel agency

    alexisp

      Good day,


      I am building a database for a travel agency, and looking for the best/most efficient way to go about doing it.

       

       

      Basically, we're dealing with 3 categories of services providers that have particular details associated to them:

      1- HOTELS: location (city), types of rooms, price per room, additional services

      2- TRANSPORTATIONS: location (city), types of vehicles, price per car and per transfer, language spoken by the driver

      3- GUIDES: location (city), cost per service, per hour, per half-day, per day

       

       

      The requirements of the database structure:

      * Through a unique search module, being able to find any service provider and display the particular details associated to it

      * Building an itinerary quoting system, that begins with a unique client ID, then pulls out from the databases the required information to build an itinerary. I am thinking: begin with connecting the client ID with the current quote. Begin the quote: a first drop down would list the locations offered. A second dropdown would list the categories to choose from (hotels, transportation, guides).

      -> Based on the previous selection, a third dropdown would list the service providers from the category selected in the location selected, the products that they offer and the price associated with each one.

      * Selecting a product would "save it" along with the provider details, and it should start building an itinerary that includes hotels, transportations, guides, etc. over various days. And that would calculate a global price including various products.

      * Finally, the product selected would populate an agenda, that can be retrieved day by day. (i.e. today, the following activities have been scheduled for the clients, with the following service providers...). Eventually, it would send a confirmation email.

       

       

      Any thoughts are welcome !!

       

      Thank you, Alexis

        • 1. Re: Database structure for travel agency
          PeterDoern

          HI Alexis,

           

          It sounds like you're on the right track. This generic data model might help you a little: Travel Agencies Data Model..

           

          In this model, you could list all services together in a portal via the Services TO, and overlay the fields specific to each service type (airlines, restaurants, cars, hotels in the linked example). A portal filter would let them see just the services they wanted. Or, you could use a virtual list technique. Or, multiple tabs with a portal on each like the good old days.

           

          The "save it" step that you refer to would be the record in the Service Bookings table, which forms the backbone of your itinerary and agenda -- these seem to be different reports based on the same data. Note that if they are on a prolonged tour you might see the same hotel (for example) multiple times on the trip, e.g. they check in, stay 2 days, check out, go for a bus tour, then check in again at the end of the tour. This hotel would have 2 service booking records.

           

          I imagine the process would look a little like:

          1. Enter customer info

          2. Select destination

          3. Create Booking with status: In Progress/Pending/whatever

          4. Build your itinerary by selecting services, which would create Service Booking records

          5. Show the itinerary to the client

          6. Get feedback from client

          7. repeat steps 4-6 until client approves

          8. Confirm each Service Booking with the actual services

          9. Change Booking Status to Confirmed

           

          and so on.

           

          I hope this helps, and I hope I wasn't too presumptuous in the level of detail I gave.

           

          Peter

          1 of 1 people found this helpful
          • 2. Re: Database structure for travel agency
            alexisp

            Hi Peter,

             

            Many thanks for your valuable feedback! I really appreciate your input in this matter. It is definitely helpful.

             

            You are describing perfectly the system that I am trying to create.

             

            Regarding the Services listing, do you think it would be optimal to have 1 database for all, or 1 database for each type of service (i.e. hotel, guide, etc.)? Given that the records are quite different, wouldn't it be easier to have them in separate databases, and to have a portal searching the various databases?

             

            I am quite new at FM, and I have to say that I am not certain how to go about with point number 4: "Build your itinerary by selecting services, which would create Service Booking records". I think that you refer to the part that I previously described:

            * Building an itinerary quoting system, that begins with a unique client ID, then pulls out from the databases the required information to build an itinerary. I am thinking: begin with connecting the client ID with the current quote. Begin the quote: a first drop down would list the locations offered. A second dropdown would list the categories to choose from (hotels, transportation, guides).

            -> Based on the previous selection, a third dropdown would list the service providers from the category selected in the location selected, the products that they offer and the price associated with each one.

            Do you have any suggestion on how to structure and script this part?

             

            Once again, thank you very much!!


            Alexis

            • 3. Re: Database structure for travel agency
              PeterDoern

              Hi Alexis,

               

              I'm sorry if I threw too much information at you at once, given that you are new to FileMaker.

               

              Yes, each of your different service types are stored in their own table. Airlines in Airlines, Hotels in Hotels, etc.

               

              The Service table is more like a common point of entry for all services. In each of the individual service tables (Airlines, Hotels, etc) you would have a field fk_Service_ID.

               

              Every record in the Service table would have a corresponding record in *one* of the services tables.

               

              By "selecting services" I meant the very broad idea of clicking on a service in a portal which would run a script that would:

              1. capture the Booking ID (from the record you're viewing)

              2. capture the Service ID (from the portal)

              3. Create a record in Service_Bookings

              4. Set Service_Bookings::fk_Booking_ID to Booking ID

              5. Set Service_Bookings::fk_Service_ID to Service ID

               

              Service_Bookings is what's known as a "join table" because it literally joins two tables together.

               

              I hope this makes sense. There are a lot of concepts about relationships that you have to grasp in order to go much further with this, especially when it comes to understanding why you would have a Service table between the individual services and the Service_Booking table.

               

              Peter

               

              EDIT: Here is a link to a quick 'n' dirty demo that I hope will illustrate some of the concepts: Dropbox - TravelAgencyTest.fmp12.zip

              The demo is obviously incomplete. Only Bookings, Services, Service_Bookings,Hotels and Restaurants.

               

              To add Services, go to the Services layout. If the service is a hotel, enter details in the left-hand fields. If a restaurant, enter data in the right-hand fields. These fields are actually in the Hotels and Restaurants tables, respectively, and will create related records in there automatically. Under normal circumstances you will prevent users from entering Hotel *and* Restaurant data in the same Service record.

               

              I hope this helps!

              1 of 1 people found this helpful
              • 4. Re: Database structure for travel agency
                alexisp

                Dear Peter,

                 

                Once again, thank you very much for your help!

                 

                I have been studying table relationships this past week. You were right, it has helped me making good progress on the design of the booking system.

                 

                I am seeing your edit now with the link to the demo. I will review it over the weekend and let you know iI have any questions!

                 

                Thank you !!

                 

                Alexis

                • 5. Re: Database structure for travel agency
                  PeterDoern

                  Hi Alexis,

                   

                  I'm glad that you found my response helpful. Bear in mind that this is only one way to do things; I only tried to present a basic example.

                   

                  All the best,

                   

                  Peter

                  • 6. Re: Database structure for travel agency
                    alexisp

                    Hi Peter,

                     

                    Hope you are well !

                     

                    I have made good progress with table structure and relationships, thanks to your help. I am also able to create records that are assigned to specific type of service (i.e. Hotel, Transportation, Restaurant, etc.).

                     

                    At this point, I am trying to display the Services in the Bookings table, before I can select them to create various records in the Bookings table, and therefore building an itinerary.

                     

                    From the Bookings table, I created a portal that displays the Services by destinations, without too much trouble. However I am getting stuck when it comes to showing the actual Service Details, that belong to each service provider. These are located in the tables "Service | Hotels | List" and "Service | Ground | List" in my example.

                     

                    If I select "Chicago" in the destination list, it shows all the service providers in Chicago. If I select one of them, I would like to have the details of the service (i.e. the type of rooms, the price, or the price of transfers for the Ground Transp. providers, etc.) displayed in another portal on the side. I have been trying to create a TO of Services that would be linked to a "Selected Field" in Bookings, but I cant seem to get it straight.

                     

                    Then, a script in the "+ Add" button will add the record to the Bookings, as in your example.

                     

                    Do you have any suggestions?

                     

                    Thanks in advance!!

                     

                    Alexis

                    • 7. Re: Database structure for travel agency
                      PeterDoern

                      Hi Alexis,

                       

                      Sorry for the late reply -- I've been fighting a respiratory infection and I'm running at half-jets.

                       

                      I haven't had a chance to thoroughly look at your example. At first glance I'm not sure why you have TOs like "Service | Hotels" joining "Services" and "Service | Hotels | List"... are there multiple records in the List TOs for each record in the Hotels TO (for example)?

                       

                      If you haven't already, may I suggest that you look through the FileMaker Training Series: Basics for a primer on how you can get your relationships to work for you.

                       

                       

                      Peter

                      • 8. Re: Database structure for travel agency
                        alexisp

                        Hi Peter,


                        Very sorry to hear about your health issue.. I hope that it gets resolved soon!! And please don't use your remaining fuel on helping me with this project... although your help is very much appreciated!

                         

                        I set up Services like a portal, where I can enter the details of each hotel, restaurant etc.. Much like you did in your example. However there is a second layer of data that I need to enter, which is the data of all rooms that the hotel sell with each price, the data of all the transfers that each Ground Transp. provide at different price and with different types of vehicles, or the various types of tours that the guides offer, etc...

                         

                        For that second layer of data, it seemed evident that I needed a second database.. i.e. "Service | Hotel | List" or "Service | Ground | List". Isn't it the case?

                         

                        I did review the Basics of Filemaker Series and also the Relational Database Design, which were very helpful!

                         

                        Thank you Peter, be well!


                        Alexis