2 Replies Latest reply on Jun 8, 2010 6:58 AM by ianmanning

    Head office, Branch, Employee what's the best structure?

    ianmanning

      Title

      Head office, Branch, Employee what's the best structure?

      Post

      I have a CRM database with companies and employees in separate tables related by a key field. Some of our companies have a head office with sub branches.

      I want to be able to show the related branches if I look at any of the branches or employees.

      I assume the best structure will be to add another table for head office  and add another Key & foreign key.

      The number of companies with sub branches is very small compared to single site companies and I was wondering if there are any other ways to achieve this?

      Thanks

      Ian

       

        • 1. Re: Head office, Branch, Employee what's the best structure?
          ryan

          1.  how are the companies and employees tables related?

          2.  do you already have a "branch" field setup in one of your tables?

          3.  If a company currently has several locations (branches), how are those being stored:  are they listed on the Company's record, etc?

           

          • 2. Re: Head office, Branch, Employee what's the best structure?
            ianmanning

            Thanks for the reply.

             

            1. Each company has a primary Key (auto enter serial number) each employee (contact) has a field with the company number (foreign key)  the relation ship is "employee" table "foreign key"  = "Company" table "primary Key"

             

            2. There is not a branch field at he moment but I can add any fields as necessary. I will almost certainly have to have a field containing the branch name/identifier if I remove it from the company name (see answer to 3.)

             

            3. The companies and their head office are currently in the "Companies" table each branch has the town name added to the company name.  ie tesco (Chelmsford)  This naming convention has been carried over from an ACT database and does not have to be maintained. In fact we want to remove the town name because the address will be used in our quotations and will have to be edited manually each time if we can't remove it.

             

            Thanks in advance

            Ian