11 Replies Latest reply on Mar 11, 2011 1:49 PM by brian.curran

    Address Hierarchy, must have been done before?



      Address Hierarchy, must have been done before?


      I'm sure this will have been done many times before so I'm hoping for suggestions on how to set up the following structure:

      Sites (_kp_site_id, _kf_company_id)
      These are locations where we perform services, i.e. the 'Delivery/Shipping' address.

      Company (_kp_company_id, _kf_group_id)
      A company might own several Sites but the administration of them may be handled at the Head Office, i.e. this could be the 'Billing' address.

      Group (_kp_group_id)
      This top level would only be used on rare occasions. Maybe to group several Companies together.

      So, in summary:
      Group could be classed like a Country, which has many States (Companies) with each state having many Towns (Sites).

      Ideally, I would like to set up a new Site and then allocate it to a parent Company if appropriate. Then, on the odd occasion, allocate the Company to a parent Group.

      Our business RULES:
      We always have a Site where we perform the actual service work.
      We often have Companies where we send invoices for multiple Sites.
      We sometimes need to group Companies together for reporting purposes or to share out blocks of pre-billed services.

      Any suggestions?

        • 1. Re: Address Hierarchy, must have been done before?

          You seem to have this all laid out like I would. I'm not sure what kind of suggestions you might be looking for.

          Hmmm, Company and Sites both appear to store addresses. As such, these could be located in the same table with a different label to distinguish them. This does not prevent associating multiple sites with a company as that can be done with a self join. This is an option, separate tables can be made to work also--that's just a suggestion for you to consider when you analyze the design of your system.

          Group would appear to be a simple table that does not store addresses. It would seem that you can link occaisional company records to a group simply by entering groupID number into a field defined for that purpose. Companies not part of that group would leave this field empty.


          Groups::_kp_Group_id = Companies::_kf_Group_ID
          Companies::_kp_Company_id = Sites::_kf_Company_ID

          This all works out the same basic way whether you use 3 tables or 2. With two tables, Companies and Sites become table occurrences with the same data source table.

          One interesting issue to think about as you work out the design is how you would handle cases where a Company's address is also a "site" that you need in your reports, billing and dispatching. You might have two copies of the same record, but that's a redundancy we try to avoid. With a single Addresses table for both companies and sites, you might have a single record that is designated as both a "site" and a "company" by formatting a field with check boxes so you can select both designations for the same record.

          • 2. Re: Address Hierarchy, must have been done before?

            Thanks Phil, I think your last paragraph sums it up best. We also store Group addresses so maybe a single table could hold all 3 addresses?

            Customer Variations:

            1)   A domestic house where Mr. & Mrs. Bloggs lives would be the Site address where we do the work as well as the Company address where we send the bill and also the Group address for reporting purposes. In reality I would prefer to set them up with just one address in a single table and tick 3 boxes to specify that this address is good for everything.

            2)   The Group could be Google with YouTube, Picasa and FeedBurner as the Companies. Each company could have several Sites. Google may want us to report on a Group wide, Company wide or Single site basis. Invoices for YouTube Sites may go direct to them but Picasa and FeedBurner invoices may be handled by Google.

            A single Address table sounds like the best option.
            A selection checkbox field with all 3 boxes ticked would work well for No. 1) above.
            No. 2) could be dealt with by entering each of the Site addresses (ticking just 1 box for Site) and entering a FK number for the relevant Company. Company addresses would also be entered separately along with Group addresses.

            I haven't heard of a self-join before so not sure how that would work...

            • 3. Re: Address Hierarchy, must have been done before?

              SelfJoins are just like any other relationship except that both table occurrence boxes refer to the same data source table.

              I think I'd restructure this a bit:


              Customers::CustomerID = Addresses::CustomerID

              Using a check box field, any given address can be labled with any combination of Group, Company or Site.

              A portal to Addresses on the Customers layout would list all related addresses.

              For purposes of working with Groups I might define a Groups Table so that I can also have this relationship:


              • 4. Re: Address Hierarchy, must have been done before?

                To ensure I understand correctly:

                Does this mean a '1 to Many' relationship between a Customers table and an Addresses table?

                • 5. Re: Address Hierarchy, must have been done before?

                  Yes, one to many. You could have just one address or 20 if the company has a lot of site addresses.

                  • 6. Re: Address Hierarchy, must have been done before?

                    Thanks Phil, I'll mock it up and see if I can get it to work...

                    • 7. Re: Address Hierarchy, must have been done before?

                      That all appears to work ok, thanks Phil...

                      On a 'Group' layout, I added a Portal to 'Companies' and then a Portal to 'Sites. This listed the single Group record at the top, 3 Company records in the middle and then 10 Site records at the bottom (sorted by Company id, then Site id).

                      What I'm not sure about is whether I need to add the Company address to the Addresses table as well as the Company table. Or separate them out across the tables. If it needs to be duplicated/triplicated then ideally, I would like to add it to the Addresses table and then when I tick the Company checkbox, it would copy the details up to the Company table. Same for the Group checkbox.

                      Then again, it seems odd to have the same information in 3 tables (potentially). I'm not sure what is the best way forwards...

                      • 8. Re: Address Hierarchy, must have been done before?

                        Not what I had in mind. All address data would be in the same addresses table. The only info in the other tables would be non address fields such as the name of a company, group or site location. The point to having a unified addresses table is precisely to avoid that duplication.

                        • 9. Re: Address Hierarchy, must have been done before?

                          Ah that makes much more sense, now I understand. It's difficult to visualise how the work that's being done now in the design stage will all fit together in the user interface later. Maybe I should have planned it all out a bit better! Wink

                          I have to say, FileMaker is much easier to learn and get to grips with than MS Access ever was!

                          • 10. Re: Address Hierarchy, must have been done before?

                            I've also tried to avoid laying out a complete solution for you, choosing instead to toss out bits and pieces as suggestions for you to assemble together into a final solution that works for you and which you understand how and why it works the way it does. I know that can be a bit confusing at times, but hope the final result will work better for you than just blindly following a "cookbook" recipe for creating your system.

                            • 11. Re: Address Hierarchy, must have been done before?

                              And I very much appreciate it too, a 'cookbook' receipe would feel a bit like cheating at this stage of my learning. Saying that, I often like to see something that works and then deconstruct it to see how the individual pieces all fit together - tutorial files are good for this. The Northwind db file in MS Access was great for this very reason.