3 Replies Latest reply on Feb 21, 2015 5:50 PM by FentonJones

    Tricky db setup question



      Tricky db setup question


      I am designing a db to track a company's insurance policies.

      • 1 company has many subsidiaries and each subsidiaries have many locations which has many different buildings.
      • 1 company has many different policies which has many different coverages, claims, assets, etc.
      • The 2 main tables are company and policies yet lots of tables coming out of policy. What is the best way to set that up? 

      The way it's set up needs to be redone...I'm just not sure how to structure it. 
      Any help would be GREATLY appreciated!!!


        • 1. Re: Tricky db setup question

          I don't like to see a post have no answer, when something can be said (and I know something about it :-). Basically, what you need is somewhat difficult, to handle multible "levels". The basic idea is to have a "parent id" in every field which is a "child" of another. When you further than those two levels, you get into a hierarchy ( I can't remember it either).

          This method is shown in its simplest way here at this web site (look for it; there are several tools available at this site). Its name is: Hierarchical Portal


          • 2. Re: Tricky db setup question

            Thank you so much!


            • 3. Re: Tricky db setup question

              "1 company has many subsidiaries and each subsidiaries have many locations"

              The above is what may be a "hierarchy"; however it depends on how you want to interact with these, which level of the above you would want to interact with as a "company" itself. Locations is a little questionable, as they could just be another table, "off to the side", used when needed by a choice, rather than each seen as a "company" of its own.

              A "hierarchy" with the three levels about would mean that each is a record in the Company table. This would have some advantages, as far as seeing other tables (since they'd all be coming from one table). But there would be some disadvantages, such as if you just wanted to see the unique names of companies (if several have the same name).

              One thing I think I'd want in such a hierarchy of companies, would be to not have a "view all from the beginning", as the example file did. I would want to do a Find for the company I wanted to see first, then start seeing the rest of the hierarchy (if any), from that level down.

              I have been working on a version of my own, which uses a slightly different method. I'm using (more) relationships, and calculation fields. I have not yet added a via the same as they did (though I think I could add it). It can see all its "children", and its "parents". It only has about 4 levels however; but that seems enough. Let me know, and I'll finish it off.