4 Replies Latest reply: Aug 14, 2015 11:43 AM by Srikanth P RSS

    One to many Relationships

    sudhakar budde

      Hi,

       

      I have table Client (where Client_type in (Clients, Agents and Contacts)). Primary Key is CL_ID.

      I have another table WorkOrder with Cleint_ID, Agency_ID and Contact_ID. All these IDs join on Clients.CL_ID.

      So I have derived Clients (CL_ID as %_CLIENT), Agency (%_AGENCY) and Contacts (%_CONTACT) from Client table and Joined with their respective Cleint_ID(%_CLIENT), Agency_ID (%_AGENCY) and Contact_ID (%_CONTACT) in the WorkOrder table.

       

      Please see attached table view.

       

      My question is : is this approach correct? What is the best way to reduce these tables instead of deriving more? How can I resolve this one-to-many relationship? Please help me.

       

      Thanks in advance

        • Re: One to many Relationships
          Srikanth P

          Instead making 3 client  dimension tables , create one Client table with concatenate other client types like below.

           

          Clients:

          Load

               *,

               'Clients' as %Client_Type // this field differentiate type of client

          From Clients.qvd (qvd) ;

           

          concatenate (Clients)

          Load

               *,

               'Agents' as %Client_Type // this field differentiate type of client

          From Agents.qvd (qvd) ;

           

          concatenate (Clients)

          Load

               *,

               'Contacts' as %Client_Type // this field differentiate type of client

          From Contacts.qvd (qvd) ;

          • Re: One to many Relationships
            Peter Cammaert

            You did a nice job of creating a star schema out of a compact database representation (all contacts of whatever type in a single table) And if it works, then it's correct.

             

            A subsequent question you can ask yourself: is this an optimal solution? Is performance hampered by the extra hops and should you merge the fields you really need into the workorder table and throw away the others (throwing away what you don't really need is always a good thing)? Is there a massive amount of data present in the document, or is size rather limited and are we optimizing for fun while getting no real improvements...

             

            A good read by HIC: A Myth about the Number of Hops

            Another one by Miguel: The Importance Of Being Distinct

            And another more philosophical post by HIC about what to do when: Data Modelling: Clarity vs. Speed

             

            Peter