14 Replies Latest reply on Jun 10, 2009 8:02 AM by TTiger

    Secondary Instance of Table.

    TTiger

      Title

      Secondary Instance of Table.

      Post

      Hello,

       

      WHat affect will creating a secondary instance of a table have on my database? What I have set us An Events, Visitors, Participation, and Payment Table. Payment table has two keys, VisitorID and EventID. Using those ID's I have it set to lookup the visitor name and the event title when an ID is specified in the Payment table. In order for me to use the same lookups in the Participation table it made me create a secondary instance of the participation table in order to use VisitorID and EventID as keys. Will there be any affect on my database by having two instances of one table?

        • 1. Re: Secondary Instance of Table.
          mrvodka
             not really. Actually it is quite ordinary for a solution to have multiple table occurrences. Although it has been debated about having mutiple TO's possibly having an affect on performance, that it generally set for where there are hundreds of TOs.
          • 2. Re: Secondary Instance of Table.
            philmodjunk
              

            "Will there be any affect on my database by having two instances of one table?"

             

            Of course there will or you wouldn't create the extra table instance (also called table occurrence) in the first place. :smileywink:

             

            When you have two or more instances of the same table, you still have only one physical table. Filemaker uses the additional instances to define additional relationships between your tables. Thus, you still have a single set of the actual data, but each new table instance gives you a new way to group, sort, link your data. When you select a specific instance (by selecting it in a portal set up dialog, for example), the relationship that links that table instance to another table will determine how your database will function in this particular case.

            • 3. Re: Secondary Instance of Table.
              TTiger
                 So then when creating portals do display information will I use the first occurrence or the second occurrence?
              • 4. Re: Secondary Instance of Table.
                mrvodka
                   Its all relative to where your parent record is coming from and also what the relationship is doing... Think of it this way... A portal is merely a window to view the records established by your relationship.
                • 5. Re: Secondary Instance of Table.
                  philmodjunk
                    

                  Think of it this way: 

                   

                  When you set up a portal on a layout, your layout should refer to one table instance in your graph and your portal should refer to a different table instance. (Note: every "box" in your graph is a table instance whether they refer to the same source table or not.) The relationship that links the two instances will determine which records appear in your portal.

                  • 6. Re: Secondary Instance of Table.
                    RickWhitelaw
                      

                    Hi Mr_Vodka,

                     

                    Some would go even further, as I do, and see many small tables as more efficient than fewer tables with many fields."database Normal" theory is interesting and I've done some (very little) reading on the subject. My current seven-file solution uses 208 tables. Many of them consist of a pk and one or two other fields. I haven't bothered to count the TOs. It runs quite quickly. Filemaker is different in that it shows Calculation fields as part of the tables and Calculation fields are generally not indexed. The more indexing, the higher the storage needs . . . the more calcs, the higher the CPU demands I suppose. 

                     

                    RW 

                    • 7. Re: Secondary Instance of Table.
                      comment_1
                        

                      I don't think breaking a table arbitrarily into smaller chunks is more efficient (or better in any way). You should have as many tables (not TO's) as required by the data model - i.e. one table per tracked entity.

                       

                       


                      RickWhitelaw wrote:
                      the more calcs, the higher the CPU demands I suppose. 

                       

                       That's right - and that's why stored calculations should be generally preferred (with some exceptions). Storage is cheap, time isn't.


                      • 8. Re: Secondary Instance of Table.
                        TTiger
                           How do I create another table occurrence on my own? When my first occurrence was created FileMaker did it for me.
                        • 9. Re: Secondary Instance of Table.
                          RickWhitelaw
                            

                          Hi Comment,

                           

                          I know from reading your posts that you're an experienced Filemaker user/developer, so what I have to say is likely very obvious, but, then again, all sorts of folks read this forum. What I mean by smaller tables as related to efficiency is shown by a simple example. Supposing, in a contacts table, you need to track name, address, mobile phone and web URL. Given that almost everyone has an address it makes sense to include address in the main table. If only 50% of people have mobile phones it seems more efficient to create a related table for that, as opposed to including mobile phone in the parent table. If 5% have web URLs it makes even more sense to create another table for that purpose. This is how I've always worked and assumed this was more efficient from a storage and CPU point of view. However I'm not an expert on this and welcome any opinions you or others might have. 

                          • 10. Re: Secondary Instance of Table.
                            comment_1
                              

                            I am not going to formulate any rigid rules about what's better - each case needs to be evaluated on its own. Breaking off phone numbers, for example, into a separate table makes sense in many situations, since some people may have more than one, some none. I am not sure I would go as far as to create a table for a specific TYPE of phone numbers, though.

                             

                            OTOH, there can be no doubt that searching in related fields is slower, and that relationships require caching, and that unstored calculations cannot be used on the child side of a relationship - all these and other factors must play a role when deciding the best approach for a specific solution.

                            • 11. Re: Secondary Instance of Table.
                              mrvodka
                                

                              Either choose from the bottom left icons...

                              1st one - allows you to choose a table  OR

                              3rd one from left - allow you to duplicate a TO.

                              • 12. Re: Secondary Instance of Table.
                                philmodjunk
                                  

                                mr_vodka wrote:

                                Either choose from the bottom left icons...

                                1st one - allows you to choose a table  OR

                                3rd one from left - allow you to duplicate a TO.


                                 

                                Anybody else mildly irritated by the fact that these buttons have neither labels nor tool tips to tell you what they do?
                                • 13. Re: Secondary Instance of Table.
                                  mrvodka
                                     Perhaps mildly... But there are plently of other things that irk me far more. ;)
                                  • 14. Re: Secondary Instance of Table.
                                    TTiger
                                       great thanks for the help!