locked
Link Entities (One or Many) RRS feed

  • Question

  • We are using xRM as a development base (not using the CRM part really - except to reuse entities like contact) and developing our own custom UI (WPF 4.5)  Please ignore references to Organisation, Case etc. as these are just for example (they are all custom entities).

    We have 8 main or core objects which connect to many line of business applications.  If you were designing a database you would create many link/transaction tables to allow for the many to many relationships and additional attributes required.  Say Person could link to case, organisation etc.  So you could have PersonCaseLink - with what role they have, the id's of each etc., then you could have PersonOrganisationLink with role etc.

    In xRM you could have one PersonLink which has ids of each entity that relate, the type of entity it relates to (Organisation, Case etc.) and the role. (Similar to this http://mscrmgoodies.blogspot.co.uk/2012/02/custom-lookup-in-crm-2011.html).  So PersonLink contains the links to all other entities which basically works like ManyToMany direct relations work in crm2011, just I can add additional required fields that are common (roles are specific to each entity relation so these can be held in look ups).

    What's the best practice or result of doing these on workflow, security etc. - Do  I use lots of tables or create one if the additional fields are the same for all entities?

    Any help would be appreciated and sorry for the question being so abstract

    Stuart

    Thursday, October 18, 2012 12:49 PM

Answers

  • Hi Stuart,

    I agree and understand your dilemma. Every project is unique and depending on requirements the best decision should be taken.

    My advise would be to go for lot of staging/linking entities. I mean don't go for one to manage all.

    If you go for many staging entities, writing plugins, custom workflow activities, reports will be very easy. Also your system will also be easily upgradeable and easily maintained. Also you can decouple your whole design. You can design your whole system into modules or you can call them processes. For example one process is Case Management. One process can be called as Account/Contact management. Then there would be entities which will overlap in these 2 processes.

    If you go for one managing all approach, designing would be difficult. Then even if you design, developing (plugins, workflows, JScrips and Reports) would be difficult. Also remember in future you might need to integrate your xRM with lets say SharePoint or ERP systems like Dynamics GP or AX. Then if you have one managing all, your integration points will not be simple to expose.

    So my advise would be to go for many staging entities.

    I hope this helps. If my responses answered your question, please mark all my responses as an answer and also vote all of them as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Friday, October 19, 2012 10:56 AM

All replies

  • Hi Stuart,

    I am having hard time understanding your question and approach. I try to provide some statements, as we need to start somewhere.

    In CRM (or xRM), we carefully select the type of relationships. N:N relationships are not always implemented. N:N should only be created if that relationship can not be any case 1:N or N:1.

    In CRM/xRM also there is a linking tabe created for N:N relationships. For example below in CRM I have 2 entites. One out of the box contact and one custom called movies. There is N:N between contact and movies, as there can be many contacts as actors in many movies. Below if the query for the N:N relationship:

    select	new_name, * 
    from	new_Movie 
    	inner join new_new_Movie_Contact_Actors on new_movie.new_MovieId = new_new_Movie_Contact_Actors.new_movieid
    	inner join Contact on new_new_Movie_Contact_Actors.contactid = Contact.ContactId

    The best practice is to sparingly use N:N and only if it is really needed. Instead of creating N:N, you can also create staging/linking entity in CRM. So for above I could have created a separate entity called Actor. This Actor then would have N:1 with both Contact and Movies entity. So Contact and Movies both will come as a lookup on the Actor entity.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Friday, October 19, 2012 2:52 AM
  • That is what i have stated above, so do i create a lot of staging/linking entities or link/transaction tables as I call them.  Or do I just have one and use that to manage all relationships, so as an example

    One Many Many One

    Person  PersonOrganisationLink Organisation

    Person PersonCaseLink Case

    One Many Many One

    Person Personlinks Case, Organisation (all)  

    Personlinks would have something like the following

    URN Person, URN of Other Entitiy (Case, Organisation), role (relationship) and type, which is the type of entity that relationship describes 

    E.G.

    PERS1 CASE1 SubjectOf CASE

    PERS1 ORG1 Employee ORGANISATION

    PERS4 CASE1 Witness CASE

    etc.

    Sorry does that make sense, essentially create one table to manage all relationships rather than have many link/transaction tables

    Thanks

    Stuart

    Friday, October 19, 2012 9:06 AM
  • Hi Stuart,

    I agree and understand your dilemma. Every project is unique and depending on requirements the best decision should be taken.

    My advise would be to go for lot of staging/linking entities. I mean don't go for one to manage all.

    If you go for many staging entities, writing plugins, custom workflow activities, reports will be very easy. Also your system will also be easily upgradeable and easily maintained. Also you can decouple your whole design. You can design your whole system into modules or you can call them processes. For example one process is Case Management. One process can be called as Account/Contact management. Then there would be entities which will overlap in these 2 processes.

    If you go for one managing all approach, designing would be difficult. Then even if you design, developing (plugins, workflows, JScrips and Reports) would be difficult. Also remember in future you might need to integrate your xRM with lets say SharePoint or ERP systems like Dynamics GP or AX. Then if you have one managing all, your integration points will not be simple to expose.

    So my advise would be to go for many staging entities.

    I hope this helps. If my responses answered your question, please mark all my responses as an answer and also vote all of them as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Friday, October 19, 2012 10:56 AM
  • Thanks,

    Yes this goes with what i was thinking regarding solution design.  For speed i've decided to have a chronology table with the same information in, which is updated by workflows when each entity has new or deleted values so basically having the separate link tables and also having one.  I realised the question was abstract and it always depends on what your doing but thanks for your input.

    Stuart

    Friday, October 19, 2012 11:13 AM