Convert entity relationship type with data. (n:1 to n:n)

You are always recommended to determine all your relationships in between the entities prior to implementation of CRM. Anyhow, when it comes to practice you will face a lot of exceptions. In most cases Customer/Business analyst could have a need to change certain relationships due to change of requirements. One common change would be to have a need of a many-to-many relationship instead one many –to-one.

Will take a scenario; If a contact can participate in an event, simply we would create a n:1 relationship in contact. In fact, one can simply fill the lookup shown in the contact form. In the db, you will have an extra field to hold the relevant event id (i.e. new_eventcontactid) in contact enity.

Now, if it is required that contact to be able to participate for more than one event, current solution is not suitable. Then you will be asked to convert current relationship to a n:n relationship. How you do it without losing the data?

Create an n:n relationship in between Contact and Event (actually new_event). If the relationship name is new_event_project, you will find a table in the SQL with the same name that holds the relationships. Actually the primary keys of the related entity instances. Now you will need to fill this tables with the data in previous n:1 table as below t-sql script does.

insert into new_event_project (new_contact_eventid,contactid,new_eventid)
select NEWID(),ContactId,new_eventcontactid 
from contact
where new_eventcontactid is not null

Now you can delete the n:1 relationship in contact entity.