4 Replies Latest reply: May 4, 2018 11:53 AM by Michael Tarallo RSS

    Basic Data Modeling Issue

    Helene Rao

      I am new to Object Modeling and have an issue i need advice on:

       

      I have a list of Contacts in my object model stored in a Contact Table (ContactId, Name, Birthday).

       

      I want to use a reference to the contactId in many other areas of the object model

       

      - I have a table that holds Accounts (AccountId, Name)

      - I have  table that holds Assets (AssetId, Name, Value)

      - Accounts have Shareholders that are Contacts(ContactId)

      - Assets are by owned by one or many Accounts (AccountId)  or  one or many Contacts (ContactId)

      - Assets are managed one Management Company

      - A Management Company can be managed by one or many Contacts (ContactId)

       


      In my QLik Dashboard, I ultimately want to be able to consolidate when i select a contact , what assets they own directly or via  one or many accounts and what they manage via a Management company.


      I am in the process of drawing this in Excel and am here:


      - I created a contact Table (ContactID, Contact Name, Birthday)

      - I created an Asset Table (AssetID, Asset Name, Value, MgtCoID)

      - I created an Account Table (AccountID, Account Name) and an AccountAssetOwnership Mapping Table (AccountID, AssetID, Ownership%) . In order to reflect that individuals can own an asset, I  created "individual" accounts for each individual. There can be more than one account co-owning the same asset.

      - I created an AccountContactMapping Table (ContactID, AccountID). A contact can own multiple Accounts (eg their own indivisual account and they sharehold of a company account)


      With this i can get to what every contact owns directly or via an account.


      How do i add my management company manager link ? I am getting stuck.

      - I created a table to store all management companies (MgtCoID, Management Company Name)

      - If i add a MgtCompany Manager Mapping Table (MgtCoID, ContactID) then I create a circular reference

      - Should I add the MgtCo as accounts in my Accounts Table and create a "function" column in the AccountAsset Mapping Table with two types "Owns" or "Manages". But then how do i add the link to say that Contacts manage a MgtCompany?


      Any Help very appreciated. My head is spinning. H


        • Re: Basic Data Modeling Issue
          Digvijay Singh

          Not sure but what if you join your management company table with the contact table, I mean keep common table for contact ID and related management company ID. I am trying to suggest that you associate your contacts to accounts and account to assets through one way link. But do you have MgmtCoIDs without any contact ID? in that case you may need to create dummy ID before joining the Mgmt company info in the contact tables.

          • Re: Basic Data Modeling Issue
            Michael Tarallo

            HI Helene, I've pinged a few of our community experts to see if they can assist you. Please note they may be busy on other projects, but if they have a moment I know they will help him some way.

             

            stalwar1 gwassenaar jpe

             

            In the mean time - can you see if this video helps at all? - It explains how to fix a circular reference.

             

            https://www.youtube.com/watch?v=vPrsPXW2N2I

             

            Can you add the ContactID to the mgmt companies table and forego the link table with ContactID?

             

            I can create a sample as well for you. If I don't see some of our experts jump on this, I can assist after my webinar.

             

            When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

             

            Regards,

             

            Michael Tarallo (@mtarallo) | Twitter

            Qlik

            • Re: Basic Data Modeling Issue
              Helene Rao

              Thank you all for your advice. After some more thinking this is how I have for now moved along.

               

              As a contact can play the role of MgtCo manager, direct owner and account shareholder at the same time and if they do I want to see the whole tree of sub objects they have an influence on, i have for now :

               

              - merged all contacts (mgrs, direct owners and shareholders)  into one contact table

              - merged all accounts and mgt companies into one table and added a field that tells

              me what type of account they are (Mgt Co or Asset Owner)

              - created a contact to account mapping table where I can state that a contactID is a shareholder in account’ X, a manager in account Y. The mapping table has a contact ID, a an account ID and a relationship type.

              - that account table is linked to  assets As per above

               

              This way I have no circular reference. however if a contact plays both the role of owner and manager of an asset then I have to make sure I am not double counting the value of the asset in my sums.

               

              Watching your videos and reading up about circular references , I came to the conclusion that in fact my contactId did all represent the same thing but that their relationship to assets were multiple and of many types. I hope this is the right way to go.