9 Replies Latest reply on Aug 6, 2010 8:57 AM by philmodjunk

    Relationships: how many is too many?

    smashingly

      Title

      Relationships: how many is too many?

      Post

      Hi, 

      I have a bit of a problem with a database I'm working on.  The easiest way to explain it is to upload my relationship graph.  I'll start the description at the Trip table & TripMain layout.  In terms of business rules, a Trip consists of a bunch of people going somewhere, and can contain many Flight Requests, Hotel Requests, and Car Requests.  The Trip layout (based on table Trip) contains a portal to TripJoin.  TripJoin is a join table which links Person records to Trip records, i.e. which people are going on a particular trip.  Along with the 2 foreign key records in each join table record, I store a few attributes that are specific to that person for that trip - which cabin class they've requested, which passport they are using (some have two, you see), and a calculation which looks at which of their passports has been selected, and displays the correct passport number on the layout.  Visas (UK, USA, Schengen) are done similarly but auto-guessed based on the destination country/continent for that trip.

      The Trip layout contains a button/script which creates a flight request and auto-populates it with all the Trip participants (thanks Phil for teaching me that!).  It does this by copying key fields into the FlightReqJoin table.

      I have two table occurrences of Flight Request, because in terms of business rules, it is possible to create "standalone" flight requests which are independent of any Trip which is currently occurring.  This is so that the client can generate ad-hoc flights not particularly related to (or worthy of creating) a Trip.  So a Flight Request links to Persons using FlightReqJoin.  And for Flight Requests that are tied to a trip, a 2nd TO called Flight Request 2 allows me to relate a flight request to a trip via the TripID(fk) field.  Note that my FlightReqJoin table, unlike the TripJoin table, does *not*, at this time, have fields which store Passport In Use, Visa In Use, etc type information.

      My problem is this - on layout Flight Request there is a portal, based on FlightReqJoin, which lists all the people (Person table records) on that Flight Request.  I have added a field "Passport #" which is set to show data from the TripJoin::Passport_num_in_use field - which is a calculated field based on which drop-down option (passport 1 or passport 2) was selected on the Trip layout.  On the Trip layout this works fine - the correct passport number is shown.  On the Flight Request layout, no passport number is shown.

      Looking at the relationship graph, I have to wonder - am I demanding too much, too many relationship "jumps" for FileMaker Pro?  Or is there just no way for FMP to relate this information - essentially I'm asking it to grab the field Passport_selected field from TripJoin somehow.  Looking at the rel graph I think this can only really be done via the 2nd TO of FlightRequest, yes?

      So is there a way to achieve that?  If I was to change my Flight Request layout to be based on my 2nd TO of Flight Request, it would fix this problem (I think) but would break the portal - it simply lists a mish-mash of people (some of whom are repeated many times), along with the date of birth and resident-permit number of the first person in the Person table.  Presumably because FMP can't relate backwards from Flight Request 2 (TO2) all the way to FlightReqJoin without going via Trip, TripJoin, Person and then FlightReqJoin.

      Should I just add the cabin_class and passport_selected fields to the FlightReqJoin table also (and update my auto-populate script on Trip, so that it copies that info across) ?  Essentially the guts of what I want in terms of functionality, is this:

      A. The user can create an ad-hoc flight and select the passport in use, for each passenger, manually.
      B. The user can create a flight from a Trip record, in which case the passport in use is already selected, and that info would be copied over to the new Flight Request record.

      Hope this makes sense... any ideas??

      Screen_shot_2010-08-03_at_18.06.37.png

        • 1. Re: Relationships: how many is too many?
          philmodjunk

          I've spend several minutes looking at your graph and reading the text and I'm still not sure that I have it all straight.

          When you trace the graph from Flight Request to FlightReqJoin to Person to TripJoin in order to get to the Passport In Use field, you loose track of what trip "owns" the flight request and there may not be any trip record to own it if it's an "ad hoc" flight request. There could be any number of TripJoin records (or none) that match to your record in Flight Request because your person may have made many trips (and they'll be listed in TripJoin once for each trip) or none.

          I think you should treat all "ad hoc" flight requests as trips with one flight request and thus go from Flight Request to Trip to TripJoin as you can from Flight Request 2.

           I imagine that you want to treat them differently because these represent cases where the Boss says "I'm flying to XXX and back tomorrow." and you have to jump to get everything set up correctly. I'd devise layouts that streamline such quick trips by eliminating or automating steps where possible, but still treat this as a "trip" like anyother when it comes down to data and their relationships.

          • 2. Re: Relationships: how many is too many?
            smashingly

            Hi Phil,

            Something I failed to mention in my initial post, is the fact that the people on a given Trip, are not always present on every flight request related to that trip.  I.e. 50 people may go from London to New York, then during that trip, 5 people may fly back to London.  Or the boss could decide to fly 3 people out from Puerto Rico to join them in New York - these people would not be on the original Trip list.  I always envisaged Flight Requests being - in a sense - child records of a parent Trip, but the user began creating flight requests without a parent Trip.  I can curb this behaviour, the client will be OK with that.  I agree with you regarding the reasons why FMP can't figure out which records relate to which (because it's "backfeeding" via Person and gets strung up on the one-many relationships along the way).  But I still cannot see how I can get this to work, when the Flight Request layout itself is based on Flight Request, the portal on the layout is based on FlightReqJoin - which is necessary to show the flight passengers' Person details; the problem here is that I can't get FMP to grab the passport number (TripJoin::Passport_num_selected, which is a calc based on TripJoin::Passport_type_selected, which is a drop-down the user selects for each trip participant on the Trip layout).   Are you saying that I should base my Flight Request layout on the 2nd TO (Flight Request 2) ?  I'm pretty sure I tried that already and the portal (based on FlightReqJoin) got screwed up.

            If you are suggesting that I don't use a join table (FlightReqJoin) at all, then that's not going to work, as far as I can tell, because like I said, on a given trip record of say 50 participants, you can have multiple flight requests related to that trip, and they will not necessarily be all 50 people from the parent Trip.

            Note that the main business purpose of the Trip and Flight Request functionality, is to streamline the paperwork sent to the coordinating H.Q. that handles all flights, arrival clearance, etc.  So it's not important if (as in my example above) 3 people are flown over later, who were not on the original trip.

            I'm starting to think that the way to get this passport_in_use field populated in my Flight Request layout, may be to just modify my "new flight request" script (triggered from the Trip layout) so it copies the passport_in_use drop-down selection to a new field in my FlightReqJoin table, and I'll add the calc field in there too (essentially, Case ( pass_in_use = "Normal passport" ; Person::Passport ; pass_in_use = "Service passport" ; Person::Service_passport ) ).  In other words, replicate the per-person-per-trip storage of cabin-class, passport-in-use etc functionality already present in TripJoin/Trip, to FlightReqJoin/Flight Request.  Then banish the client from creating ad-hoc trips, so they can only be created from Trip.

            Thoughts?

            • 3. Re: Relationships: how many is too many?
              philmodjunk

              The main point I was trying to make is that "you can't get there from here" given your current table occurrences and relationships. Either you restructure your relationships, your layout or both.

              As I understand your graph and business model, you have persons who may not use the same passport (that seems odd by the way--never heard of it being possible to have more than one) on every trip so it makes sense to specify the passport to be used in your trip join table. But your current graph will not connect a specific flight request record to a specific trip join record--there could be many or none. If there are many matching records, this will link to the "first" such matching join record and if this has a blank field, it'll return blank back to the portal on the flight request layout. In any case, it won't be from the right record unless this person is making their very first and only trip.

              Moving from flight Request 2 to TripJoin will get you all the personnel listed for that trip and will give you the correct passport information for that trip. However, you now will see all the people listed for any part of the trip--not just the current flight. You'd need to filter this portal to exclude people who aren't on the given flight.

              The more I study your graph the more I think you need to combine the two join tables so that you get a "star join" with Flight Requests, Person and trip all linking to the same join table. This requires listing the same person multiple times for the same trip if there is more than one flight request, but sorting and filtering and certain summary report techniques for that matter can still get you duplicate free lists of your travelers--either for a given flight or for the entire trip.

              Keep in mind I may have different ideas after I think awhile, but that's the direction I'm leaning at the moment.

              • 4. Re: Relationships: how many is too many?
                smashingly

                Hi Phil,

                I definitely understand your point.  Regarding the passport issue, I'll explain it a little more.  Many of the people in the Person table are from a country (lets call it Utopia) which requires all non-citizens to have a "Service Passport".  So you could be British, with a British passport, but live and work in Utopia, so you are given a Utopia Service Passport, which you use to enter/exit Utopia.  So, when a given trip involves travel to/from Utopia, all non-Utopia citizens will be travelling on their service passport.  If the trip was from London to New York then they'd use their country-of-origin passport.

                Another more common example is with people like me - I have dual nationality, New Zealand & UK, so I have two passports.  I pick and choose which one to use depending on the circumstances (Kiwi for when I visit NZ or Australia, and UK when I'm doing EU travel).

                I can see that the current business requirement - which has led me this far, in terms of the database structure - has sort of stalemated me, with two requirements that are conflicting.  I understand in principle this concept of a "star join" - in a way it's like taking the concept of joining 2 tables and making it 3-dimensional.  Each row in the star-join table would represent the combination of one person on one trip on one flight.  I would probably also want to include Hotel Requests and Car Requests with this idea since they are also sub-aspects of a parent trip.

                It sounds like it would be more complicated - more new territory for me! - especially regarding showing the right detail in portals, but one of the great things about FMP11 is the addition of portal filtering.  For a given Flight Request record, which has a unique ID (FlightReqID), the portal on the Flight Req layout would need to be filtered based on FlightRequest::FlightReqID = SuperStarJoinTable::FlightReqIDfk...  would it be as simple as that?  And how would it work with ad-hoc F/Reqs and H/Reqs, or would I be better to simply ban that kind of usage - like you say, force the creation of a trip each time.

                I am keen to hear of any other ideas you think of, as and when you have time... I'm open to changing the DB around to suit, as I find that often this can open the way for me to improve the overall solution even more, as well as learn more about FMP.

                cheers

                Ash

                • 5. Re: Relationships: how many is too many?
                  philmodjunk

                  I'd definitely avoid creating Flight Request records without also having a "trip" record and resulting join records to tie everything together.

                  You might be able to set up a special "ad hoc" flight layout that allows such quick trips, but uses scripts to fill in the missing records automatically so that the user doesn't have to go through extra data-entry steps that are unecessary when the trip involves a single flight.

                  • 6. Re: Relationships: how many is too many?
                    smashingly

                    Ok, good idea.  I've just looked at the datasets and out of 178 Flight Requests, only THREE are linked to a parent trip.  OMG!  Very strange, since the whole point of the parent child relationship between Trips and FlightReqs is that it copies across all the people you've already painstakingly selected for your Trip, into the Flight Request.  I need to have a chat with the client and find out what's going on.  If he doesn't want this kind of link between Trips and FlightReqs then I won't bother!

                    ...The thing that's been churning over in my mind about this new database structure (star-join) is how I'm going to migrate existing records to the new star-join table.  There's only about 70-80 trip records (making 1283 tripjoin records).  The TripJoin records would be the initial basis for my new SuperJoin table, but I'd need to somehow script the import of the FlightReq and HotelReq data.  I think it would be best to first write a script to take all orphan/ad-hoc FR and HR records and auto-create a parent Trip record for them.  Then write another script to process all FR and HR records using some iterative loops that look at each record, what parent TripID it relates to, and then grabs all the FR/HR's join table's PersonIDfk values, then uses this to populate the SuperJoin table... I'm thinking it's going to involve a triple nested loop...

                    Do you have a more streamlined idea as to how existing data could be migrated?

                    • 7. Re: Relationships: how many is too many?
                      philmodjunk

                      I'm still thinking....

                      I've added this thread to my favorites so that I can find it again.

                      No offense intended, but this one's gone beyond the "free forum help" level and requires some significant effort and time to pull together all the parts of the system, analyze them and suggest a solution.

                      • 8. Re: Relationships: how many is too many?
                        smashingly

                        Hi Phil,

                        No offence taken!  I do realise that I'm asking for advice on something that's a bit uncommon, and specific to my solution... it's good to push the boundaries a little on the forums but I don't wish to be a drain if it's such a unique topic that it's not benefiting anyone else.  Would it be more appropriate if I subscribed to the paid tech forum (what's it called again? tech community?) which I was offered when I upgraded to FMP 11?  Or is that more or less the same...

                        A small update about the database usage.  Well, actually it has quite a big impact on this discussion.  Misunderstanding of the client's pattern of usage and terminology used.  TRIPS are used for requesting to HQ for big private/chartered flights and generating associated paperwork; Flight Requests are used for sending 1 or 2 staff here or there on commercial flights and are NOT related to a Trip in any way.  It was one of those subtle nuances of the user's usage pattern of the DB which I hadn't picked up on until we were sitting in the same room today and I asked him why only 3 out of 150+ Flight Requests are linked to a Trip.  

                        I have backed up the DB and just removed the 2nd TO of Flight Request and its relationship to Trip.  Flight Requests will now get their Passport_type_in_use and resulting Passport_num_in_use fields by manual selection of the former (pp type) and calculation of the latter from the Person table pp fields, the same way it happens on the Trip layout.

                        Hotel & Car Reqs, however, will need to be 'children' of a given trip.  BUT because there's no need to pull TripJoin-specific information (such as passport_type_in_use) and use it to pull info from Person, we will not need a star join - correct me if I'm wrong though.  The only info on a Hotel or Car request relating to the trip is the source city, which is pulled from Trip, not TripJoin. 

                        This means the whole original complaint - about Flight Reqs not showing passport info - isn't really an issue any more - Flight Reqs will be generated completely separately (from the main menu), and because there'll be no relationship to Trip, they can pull their Passport info from Person via FlightReqJoin.

                        I guess an important lesson here has been that the client doesn't always know what they want, and that small differences in interpretation can result in me running off and developing something clever which the client doesn't need, or use, the way I intended.

                        It may seem like we've spent time on a problem that now doesn't exist, but I'm really glad that it's taught me about star joins and the circumstances under which they'd be necessary or useful.

                        thanks v.v.v.v.much as always

                        Ash

                        PS: my girlfriend saw the title of this thread "How many relationships is too many?" on my browser screen last night and said "WTF?!" ;-)

                        • 9. Re: Relationships: how many is too many?
                          philmodjunk

                          Instead of paying for another forum, I think you need a consultant to sit down with you and your boss (or his staff) and do a complete analysis of your system, procedures and needs to make sure that your system will do what's needed effectively and accurately.

                          I don't know much about the other forum. My understanding is that membership provides access to more techincal info--some of which is Non-disclosure pre-release stuff, but frankly don't have time for another forum even if I had the $$ to pay for access.

                          On your PS. Database Relationships are easy. People relationships are the true challenge in life!