12 Replies Latest reply on May 9, 2012 4:35 AM by Sorbsbuster

    How does FM Really Handle Cartesian Products?

      Title

      How does FM Really Handle Cartesian Products?

      Post

      If you have two tables, tableA and TableB where TableA has 10 rows and TableB has 10 rows, then the cartesian product would have 100 rows (TableA's size * TableB's size).

      For cartesian products, please refer to the first question here: http://www.techinterviews.com/database-management-interview-questions

      Based on replies on this form, then, I'm a bit confused how FileMaker Pro handles cartesian products.

      I just created two tables in MySQL. TableA has values 1…10 and tableB has values 11…20. With no Join clause, and SQL like this:

      SELECT * FROM TABLEA, TABLEB, we get the result set below (100 rows).

      Does FM use the term cartesian product in a different way?

      Cartesian products are generally something to actively avoid in most database situations.

      FM may be smoothing out the rough edges here so cartesian products aren't to be avoided (in FM), I don't know.

      Can anyone clarify this?

      - m

      1    11
      2    11
      3    11
      4    11
      5    11
      6    11
      7    11
      8    11
      9    11
      10    11
      1    12
      2    12
      3    12
      4    12
      5    12
      6    12
      7    12
      8    12
      9    12
      10    12
      1    13
      2    13
      3    13
      4    13
      5    13
      6    13
      7    13
      8    13
      9    13
      10    13
      1    14
      2    14
      3    14
      4    14
      5    14
      6    14
      7    14
      8    14
      9    14
      10    14
      1    15
      2    15
      3    15
      4    15
      5    15
      6    15
      7    15
      8    15
      9    15
      10    15
      1    16
      2    16
      3    16
      4    16
      5    16
      6    16
      7    16
      8    16
      9    16
      10    16
      1    17
      2    17
      3    17
      4    17
      5    17
      6    17
      7    17
      8    17
      9    17
      10    17
      1    18
      2    18
      3    18
      4    18
      5    18
      6    18
      7    18
      8    18
      9    18
      10    18
      1    19
      2    19
      3    19
      4    19
      5    19
      6    19
      7    19
      8    19
      9    19
      10    19
      1    20
      2    20
      3    20
      4    20
      5    20
      6    20
      7    20
      8    20
      9    20
      10    20

        • 1. Re: How does FM Really Handle Cartesian Products?
          Sorbsbuster

          The Cartesian join in Filemaker is more commonly interpreted to mean "give every record in this table complete access to every record in that table."  But it can perform the kind of build-up you ask for.  For example, if you have tables A and B joined by a Cartesian relationship, and you show all records from Table A and export Field A from Table A and Field B (via the Cartesian relationship) from Table B and open the result in Excel you will see that it will have created the list as you describe above - except that it will only show the first instance of the Field A each time.

          That can probably be got around, but I've never had to use that characteristic of the feature, so I've never bothered.

          • 2. Re: How does FM Really Handle Cartesian Products?

            Great answer, thanks.

            I would never want a product result of two tables (with tableA * TableB rows) like the example I gave above. Cartesian products are always things to be avoided except in very special sitautions -- none of which I've ever encountered.

            Seems like FM makes this concept friendlier, which is a good thing.

            Thanks.

            - m

             

            • 3. Re: How does FM Really Handle Cartesian Products?
              philmodjunk

              A key difference between Filemaker and more typical SQL based systems is that in the other systems, you use a SQL query to set up what is essentially a "virtual table" for a given form or layout. FileMaker layouts are based on a single physical table as specified by the table occurrence listed in "show records from". Thus, in Access and other systems, using a cartesian join in the Query for a report or form produces a record set of 100 records in your example. In FileMaker, the layout is based on either one table or the other, but not both as it is not based on any join at all. Thus, it has a found set of just 10 records, but a portal to the related table will list all 10 records of the other table no matter which record is current on the layout.

              This may make cartesian joins a useful problem solver rather than poor relational design to be avoided, but this approach also shows up as a limitation in other cases. Consder trying to produce a list type report based on an outer join in Filemaker, for example of something very simple to do in SQL that is not at all simple to produce in FileMaker.

              • 4. Re: How does FM Really Handle Cartesian Products?

                PMJ,

                Thanks for another one of your excellent replies. :)

                Your knowledge of this product is simply amazing.

                - m

                • 5. Re: How does FM Really Handle Cartesian Products?
                  Sorbsbuster

                  Of all the things in all the world that I want Filemaker to add to the product, the ability to create a listing of 100 x 1,000 = 100,000 records is just below button-driven tooth extraction.  So: if I find that FM13 has that in its feature set, I'll know where to come looking...

                  • 6. Re: How does FM Really Handle Cartesian Products?
                    philmodjunk

                    I don't need it for cartesian joins, but the same approach supports outer joins, union queries, cross tabs...--which are quite useful. And just because we might gain the ability to do something silly as a side effect of getting something more flexible and powerful than the current approach doesn't mean we have to go ahead and do that silly thing... Wink

                    • 7. Re: How does FM Really Handle Cartesian Products?

                      Sorbsbuster,

                      Are you saying you would like some type of way to generate test data easily?

                      If so, currently, you'd write a script to do that, right?

                      Perhaps I missed your point.

                       

                      - m

                      • 8. Re: How does FM Really Handle Cartesian Products?
                        Sorbsbuster

                        Sorry, Mork, I was being sarcastic.  At the risk of sounding argumentative, I have never felt any urge to see SQL implemented further within Filemaker.  It is amazingly useful to allow the sharing of data between applications - ODBC and SQL from MySQL to Filemaker, from 4D to Excel - absolutely brilliant.  But extracting filtered, sorted, data from a cross-table query is like.   Watching.                Paint.                                   Dry.

                        Having to write all that code?  It just feels like I should be doing it in a kipper tie and flared trousers by the light of a lava lamp.  Thank goodness FM and Excel come with decent SQL Wizards to help me write the stuff.  And surely it was SQL that the guy had in mind when he said "The great thing about Standards is there are so many of them."  The number of times that I've written a Query exactly like the book says, only to find it doesn't work because my Query didn't read the same book as me, apparently.  And Heaven help you if you put a space in the wrong place.

                        There are many features that I would like the Filemaker engineers to provide me with, but the ability to turn a table of 100 records and another of 1,000 into an output of 100,000 records doesn't even feature on my Wish List.

                        As Phil says, though, there may well be other Seriously Big Advantages come alongside that ability; maybe I just have no imagination, but so far I haven't realised what they might be.

                        That said, I've signed up for next week's FM Academy's webinar on the newly-added ExecuteSQL script step, so maybe they'll show me what I've been too blind to see.

                        • 9. Re: How does FM Really Handle Cartesian Products?

                          I've come at this from the exact opposite perspective: I've been doing SQL for 20 years and can't imagine not having that incredible power at my finger tips in any database. I spend most of my day using Java with Oracle back-ends.

                          I like the way FM does things (I still haven't bought it <s>), but I feel like I'd be giving up a lot of power. FM would be perfect for me if it also included a SQL window where I could just enter queries for the local database of any complexity. Maybe I wouldn't do that all the time, but there are times when I don't need a layout, I just want to see a few resultsets.

                          With folks like you and Phil on this forum, however, most, if not all, of the FM problems I might run into could be quickly solved. :)

                          Please post back with your thoughts on the ExecuteSQL webinar.

                          Thanks,

                          -- m

                          • 10. Re: How does FM Really Handle Cartesian Products?
                            GuyStevens

                            Hey Mork

                            I don't know if this can be helpful for you or not, but here is how I set up my tables and my layouts in Filemaker.

                            When I make a table I'll always add "Tbl" in front of the name. So for instance a table of clients would be called TblClients.

                            Filemaker automatically makes you a layout for this called TblClients.

                            Now if I want to make a layout for the user I'll create a new one called LayClients.

                            And I always put my layouts and my tables in two seperate folders.

                            That way the user interacts with the LayClients. But I can go into the TblClients and see the raw data. I can interact with it if I quickly want to look something up or if I want to try stuff out.

                            You can go in table view or create a layout for yourself in form view.

                            Anyway, try it out, it's pretty handy sometimes.

                            • 11. Re: How does FM Really Handle Cartesian Products?

                              Good suggestions.

                              Thanks!

                              -- m