6 Replies Latest reply on Apr 28, 2012 6:16 AM by brianquillin

    Base Question about Anchor Buoy Tables

    brianquillin

      Title

      Base Question about Anchor Buoy Tables

      Post

      Quick question about the A/B method:

      Why are separate tables used for Addresses, Phones, Emails, etc in many of the existing examples?

      Why would a single Attributes table not suffice for collecting those in one table?

      (I guess at that point I'm nearly back to collecting all that information into a Person or Company table like I have done before.)

      Forgive the simplicity of the question, I'm working to learn this methodology and I want to understand the rationale behind segmenting these items.

      Thanks in advance...

        • 1. Re: Base Question about Anchor Buoy Tables
          Jade

          If you keep these attributes in one table then you are forced to limit the number of each.  You could, for instance, allow up to 3 phone numbers and use a repeating field.  But what happens when a contact needs 4?  By using a separate table, you can have any number of phone numbers per contact. Whenever you are tempted to repeat the same field in a record, you should probably use a related table instead.  It's part of the normalization process in designing your database (lookup normalization in WikiPedia). By adding these extra tables, you are reducing data dependance and redundancy, wasted space, and record locking conflicts at the cost of extra overhead handling the relationships.

          • 2. Re: Base Question about Anchor Buoy Tables
            brianquillin

            Thanks...

            I'm still very new at this and I'm wanting to wrap my head around which method would be best for me and my solutions.

            I've seen a sample solution built with all contact info (address lines, phones, emails) being collected in one Attributes table.  In that example, a Person's contact info was entered into global fields (from a Globals table) that were then stored in the Attributes table (which seemingly eliminated many TOs by using filtered portals based on the type of the Attributes table).

            This method allowed for unlimited entries of contact information (addresses, phones, emails) with a pretty clean presentation on the entry layout.

            (I have just enough understanding to be dangerous; I'm sure there is some major flaw that I should be aware of.)

            I'm starting to redesign one of my bigger FMP 11 solutions (for my skill level) and I've decieded to go from the ground up in FMP 12 (I need the practice).  At this point, I'm thinking of going with the more traditional (I assume) approach of using separate tables for Addresses, Phones, and Emails.  (If so, does that mean that I'll need a TO for every 'type' of information; business, home, cell, personal, direct line, etc.?

            Thanks again...

            • 3. Re: Base Question about Anchor Buoy Tables
              Jade

              http://databases.about.com/od/specificproducts/a/Should-I-Normalize-My-Database.htm 

              If you read the reference, you will gather that it's often wise to denormalize at some point for speed (both development and processing).  So it may be better to use a value list for 'type' on the related tables rather than use a separate table for 'type' with the associated many-to-many join tables.  It's unlikely that more than one 'type' would be assigned to the same number but not impossible.  In this unlikely scenario, you would end up duplicating the number for each type which affects the data integrity since the user would then have to edit the same number more than once.

               

               

               

              • 4. Re: Base Question about Anchor Buoy Tables
                brianquillin

                Excellent read... thank you...

                It seems that I should have started there; at this point, I prefer to do a little extra work and understand completely the inner workings rather than use someone else's method that I don't fully understand.

                I bookmarked the link...

                • 5. Re: Base Question about Anchor Buoy Tables
                  Jade

                  The anchor/buoy method has nothing to do with how you decide to normalize your database.  It has everything to do with how you present the TO's clearly on the Relationship Graph.

                  • 6. Re: Base Question about Anchor Buoy Tables
                    brianquillin

                    understood...

                    thanks again...