1 2 Previous Next 23 Replies Latest reply on Apr 24, 2012 12:42 AM by ian.moree

    Fundamental Concepts of Relational Theory _ANy New White Papers?

    ian.moree

      I have to tell you, finding this sort of "DOCUMENTATION" as well as Understanding Table Context for a New Developer within Filemaker is

      extremely challenging.

      It seems that lots of forums for filemaker deal with very Small databases / basic topics, specific calculation help, but having a proper Paper

      describing relational Context and theory within filemaker is just not found.

       

      I have read the books ( fmp bible ( ray cologon ), missing manual, filemaker beyond( CRAP! ),etc

       

      THis issue is when you are dealing with 7 - 15 tables that interconnect, you are looking at advanced documentation to help you with a basic concept? I am ranting now, but

      can someone Please, pretty please send me ANY links, docs, papers, devcon articles, etc,etc on these issues. I really am wanting to understand rather than just copying other persons solutions and adding small improvements to them.

       

      cheers & thanks for any help offered in advance,

       

      -ian

        • 1. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
          BruceHerbach

          ian,

           

          The book you mentioned definitly have their good points and have taken a lot of beginners from a basic start to sophisticated and complex solutions. I have found a lot of usful information and examples in both.  Ok, that stated,  here are some links to look at.  My guess is it wil take you some time, and  quite a bit of development before you are really comfortable with what the relationship graph can do.

           

          http://fentonjones.com/FM101.html  - lots of basic stuff

          http://dwaynewright.squarespace.com/filemaker-relationships/  - more basic information

          http://www.kevinfrank.com/anchor-buoy.html   - Anchor Bouy, One method of approaching a complex relationship graph

          http://www.youtube.com/watch?v=z4HjJP99HJA  - Jonn Howell's presentation on Anchor Bouy

          http://www.dbservices.com/articles/filemaker-naming-conventions-and-standards  - naming conventions can be very helpful

          http://www.teamdf.com/weetbicks/tagged/relationship/ - helpful blogs

          http://dwaynewright.squarespace.com/filemaker-relationships/2008/5/20/filemaker-the-difference-between-squid-and-anchorbuoy.html  - comparison of Anchor bouy and squid

           

          There is a lot more and you should look at the FileMaker training series as well.

          HTH
          Bruce

          • 2. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
            ian.moree

            Thanks Bruce;

             

            Been there done that with all the links you posted as well as the filemaker training series.

             

            Guess you are right!

             

            My guess is it wil take you some time, and  quite a bit of development before you are really comfortable with what the relationship graph can do.

            So be it!

             

            time heals everything, as they say... : )

             

            -i

            • 3. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
              Mike_Mitchell

              Ian -

               

              Maybe if you're looking at a specific issue or question, you could post it. We might be able to help.

               

              Mike

              • 4. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                ian.moree

                Ok Mike;

                 

                lets say i have an assembled  product ( Suite ) and i want to "BUILD" this using parent and child records ( many to many recursion)

                In my child table I have these components

                1. cake
                2. cupcakes
                3. stand color
                4. box

                In my Parent is the Product SUITE

                 

                so in building a new SUITE , i need to add these children items to the Parent

                And vice versa

                e.g Cake is Parent

                     CHild Items are : Flavor, filling , color, size

                 

                 

                I totally understand 1 - many recursion in relationships via self join relationships right

                 

                eg: President - Managers - Trainees* etc

                using self joins * main table ID --> (selfJoined_ child--> ID_Manager)

                                          main tale   ID Manager--> selfJoined_Parent ID

                 

                The thing i am trying to "WRAP" my head around besides context is figuging out

                if i have A product ( MAIN TO) --> assembly by Child::ID Child >-- Product to Assembly by Child to Product

                                              MAIN TO --> assembly by Parent::ID >--  Product to Assemby by parent ID to Product

                 

                 

                i have read Jonathan starks article, but wondered if it was still relevant but am still unclear.

                 

                Or am i thinking about this the entirely incorrect way. Perhaps i just need to simplify my thinking more.

                 

                -i

                • 5. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                  Mike_Mitchell

                  Well ....

                   

                  I'm a little confused. You say your components are cake, cupcakes, stand color and box. These are records in the sub-assembly, yes? And your final assembly is something called "suite"? So ... what is it that makes this a many-to-many?

                   

                  I also don't get is why flavor, filling, color, and size are child records of cake. Why aren't these data attributes?

                   

                  I've not done any of these recursive data models before, so I'm not familiar with them much except in theory. I may not be much help as a result. But I'm not understanding where your problem is coming from based on your explanation. Keep trying; I might catch on...    

                   

                  Mike

                  • 6. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                    ian.moree

                    Think of this.

                    a product table has a many to many relationship with itself, Therefore i May need an interim table to resolve this relationship.This table can be called' lets say an assembly table

                    Contains the

                    ID parent ( our main table ), id CHILD (main Table) , Qty Child Items required( eg. suite requires 1 cake ,24 cupcakes, 1 stand ), and Name

                     

                    So a table needs to track products and a table that tracks which products are components of other products

                    ( the assembled product)

                     

                    So i need PRODUCT::Id to connect to Assembly::ID_Child ( cake, cupcakes, stand ,etc)

                    and PRODUCT::id To connect to Assembly::ID_Parent ( SUITE)

                    then we need a recursive connection( assembly::ID connects to ID_Parent ( child assembly table)

                    and Assembly::ID connectin to ID_Child of the Parent Assembly TO

                     

                    But the main issue is

                    keeping track of which items belong to parent and which belong to Child (within context)

                    so if we are on Product Table , are we building the product here within context and if so, where to start

                    In essence do we even really need  a recursive strucutre to begin

                    with.

                     

                    WHy not just create  a CATEGORY table, Parts Table And connect via CategoryParts JOin

                    Then the other issue is showing it via a hierarchical dropdown view similar to explorer or find in mac!

                     

                    does this make sense yet?

                     

                    -i

                    • 7. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                      BruceHerbach

                      ian,

                       

                      I think what you are looking for is to display the lower level (assembly) records you could use a portal.  The records are related by the IDs as your screen shot shows.  Not sure if you need the middle table.  I usually have a kpID field in each record.  This is the primary key for the record.  And have a kf_somethingID this is the foreign key.  The foreign key should be the primary key of the parent record.

                       

                      So if you have 5 Assembly level records that relate back to a single Parent record they would all have the same value in the kf field.

                       

                      So if you then have a layout based on the Parent record and placed a portal based on the assembly record, it would show the 5 related assemblys.

                       

                      Does this help?

                      Bruce

                      • 8. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                        Mike_Mitchell

                        Ian -

                         

                        What's confusing me is the terminology. We're throwing around Parent, Child, Suite, Cake, Cupcake, Stand ...

                         

                        Is the Parent the same thing as the Suite? Or is the Suite a group of Parents?

                         

                        In other words, how many Assembly levels do we have here?

                         

                        Mike

                        • 9. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                          ian.moree

                          So all i would need is Parent::ID --> CHild::ID &

                          Child::ID --> Parent::ID

                          eg.

                          ParentName = "SUITE"

                          ParentID::ID = 1 ; Child::ID = 5 ; name = cake

                          ParentID::ID = 1 ; Child::ID = 6 ; name=cupcakes

                          ParentID::ID = 1 ; CHild::ID = 7 ; name =stand

                           

                          ParentNAME = "CAKE"

                          ParentID::ID = 5; Child::ID = 8 ; name =flavor

                          ParentID::ID = 5; CHild::ID =9 ; name = filling

                          on & on

                           

                          However is this a One parent to many Children  But my issue is dealing with Inventory, Assemblies, Components , Items,etc

                          So these objects can hvae zero or more than one parent!

                          one My one product has a many-to-many relationship with itself! So using a table such as Assemblies can resolve the relatonship, however i need them to talk

                           

                          as Suite is also contained with a Mothersday Package; Called Mums-SuiteVBerries

                          that contains itself as other components!

                           

                          see my conundrum!

                           

                          -i

                          • 10. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                            ian.moree

                            Suite is the parent

                             

                            Look at it like this:

                            Table is the parent

                            Consists of the children *(components )

                            12 nails, 14 screws, varnish, labor

                             

                            But the Table is then the child of another Parent ( Dining room set )

                            Consists of the children (*components)

                            4 chairs, Table, 12 nails, 14 screws, varnish, labor, wheels, etc,etc

                             

                            make sense now!

                            • 11. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                              ian.moree

                              It is the fundamental relationship understanding where to Start the design process( or at least something that makes sense to me)

                               

                              of this particularly annoying issue as i dont see where to begin. Other forums are saying download this, look at that, but they have figured out "THEIR" way of doing something that makes sense to them, not me.

                               

                              -ian

                              • 12. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                                Mike_Mitchell

                                Well, I feel your pain ...    

                                 

                                Personally, unless you're keeping detailed inventory of all the sub-elements, the recursive model is probably too detailed for what you're trying to do. Are you storing cakes and cupcakes in inventory, to be replaced later when they're used? Or are they produced on demand? (I hope it's the latter ...)  

                                 

                                In a case like this, where the individual records are expendable, what I would normally do is keep a template / list sort of model. You know what pieces are needed for any given order / suite. You have parallel sets of tables: One forms the template of what any given suite or component needs. The other contains actual orders. When you're ready for an order, you simply duplicate the template into an order and don't worry about trying to keep track of the records in the template as discrete items. They're not durable goods and you're not trying to track an inventory.

                                 

                                If you're trying just to keep track of consumables (like flour, baking cups, sugar, etc.), then you can easily put a quantity of each consumable associated with each commodity and subtract that from a consumables inventory as you make it. But that doesn't require the recursive model; all that requires is keeping track of the total of each consumable involved with each order and resolving it against the inventory.

                                 

                                Yes, I know; the database purists' heads will explode at the suggestion of such a thing. But any theoretical model will always yield to the real world at some point. The question we have to answer as designers is: Where is that point? And how many Excedrin do we want to go through before we get there?

                                 

                                Just my $0.02, which of course assumes I understand the problem. Smarter people than I will probably disagree.

                                 

                                Mike

                                • 13. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                                  ian.moree

                                  you know , i dont take excedrin, but i have had 4 advil so far and need another 3!

                                   

                                  Or are they produced on demand? (I hope it's the latter ...)  

                                   

                                  Of course, the LATTER!

                                   

                                  OK> any templates you know where to look or are you telling me to just build my own?

                                  template / list sort of model.

                                  How would i do this:

                                   

                                  you simply duplicate the template into an order and don't worry about trying to keep track of the records in the template as discrete items.

                                  Via script - Duplicate record from other table( TEMPLATE_TABLE)

                                   

                                  Where would one find more information about the recursive model/ Design aspects, RULES OF use,etc,etc

                                   

                                  thanks Mike, But still dont understand what you mean by template / list sort of model.

                                   

                                  -ian

                                  • 14. Re: Fundamental Concepts of Relational Theory _ANy New White Papers?
                                    BruceHerbach

                                    Ian,

                                     

                                    Going back to what I was saying about displaying the data in a portal.  The relationship between parent and child record is based on the ID.  If we take your example form earlier and change the field names... and add the foreign key to mix... so it looks like this/

                                     

                                    ParentName = "SUITE"

                                    ParentID::kpID = 1 ; Child::kpID = 5 ;  Child::kfParentID = 1;  name = cake

                                    ParentID::kpID = 1 ; Child::kpID = 6 ; Child::kfParentID = 1;name=cupcakes

                                    ParentID::kpID = 1 ; CHild::kpID = 7 ; Child::kfParentID = 1; name =stand

                                     

                                    ParentNAME = "CAKE"

                                    ParentID::kpID = 5; Child::ID = 8 ; Child::kfParentID = 5; name =flavor

                                    ParentID::kpID = 5; CHild::ID =9 ; Child::kfParentID = 5; name = filling

                                     

                                    You can now see that in each child record,  there is a pointer back to it's parent record.  So in the relations graph a parent to child relationsip would look like:

                                    ParentID::kpID = Child::kfParentID

                                    This is what establishes the hierarchy.  So as you add child records,  your script would enter the Parent::kpID value in the new child kf::ParentID field.   Once you have that you can display the child records,  use GTRR to open the child records in another window/layout, determine how many child records there.  This list just goes on...

                                     

                                    HTH

                                    Bruce

                                    1 2 Previous Next