Solved

contact changes company. where are the orders?

Posted on 2013-01-23
7
254 Views
Last Modified: 2013-01-25
I have a delemma on how to best manage client contacts. We have tblContacts with the name of the person, etc. This also has a CompanyID which is linked to tblComanies.

When a person moves from campany A to company B this creates a little havoc with that persons orders.

I am wondering if there is a best practice for this type of situation.

Any suggestions?
0
Comment
Question by:Shawn
  • 4
  • 2
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 38812138
So .., you guys previously though person and company was a 1:1 relationship, meaning a perso is related to one and only one company.

Now it appears that it's really a one-to-many relationship as person A jumps from company 1 to company 2.

Your options are:
(1) Create a new person for them, and assign to company 2.  No coding, but you lose the history of that person with company 1 without doing a SQL hack of some sort.

(2) Create a tblPersonCompany table that holds the FK's for both person and company, and probably some dates to help you track the history.  Rewrite a bunch of code to pull this off.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38812168
(2) looks like the solution though a little daunting at first glance
0
 
LVL 1

Author Comment

by:Shawn
ID: 38812224
ok, wrapping my head around it and this should work. I will keep my 1:1 relationship and add your suggested table tblPersonCompany. I can populate this via a temp query and set up contacts to populate the 'old' system and the 'new' system....then slowly, as needed migrate to the new one.

thank you :)

PS would you recommend any sites/books on real life concepts, relations, scenarios similar to this?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 38812458
>> When a person moves from campany A to company B this creates a little havoc with that persons orders. <<

Why??

The order itself should have a company id (usu. called a "customer id" or some equivalent) on it that does not depend on the Contact.

Because the company id for a contact can change, but the company id for an order never changes.


Aside from that, if a person changes companies, you could just issue them a new ContactID.  After all, it's almost certain other contact info about them has changed too: job title, phone number, mailing address, etc..  If you wanted, you could add a separate table with a list / history of anyone issued multiple contact ids.  I would certainly do that in preference to losing the contact info stored for existing orders, particularly current ones.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38812616
>>The order itself should have a company id (usu. called a "customer id" or some equivalent) on it that does not depend on the Contact.

agreed. though certain fields in the contact belonging to the company (framework contract IDs) was not automatically changed. There is just a lot of cleanup to do and we need to prevent this from happenning again.

>>... you could just issue them a new ContactID....If you wanted, you could add a separate table with a list / history of anyone issued multiple contact ids.  
good idea.thx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38814524
FYI, you should never change order history.  An order once complete is a permanent record.

If/when you do data mining or other historical analysis, you want the order to be accurate to the time it was done, not adjusted for today's data.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38814867
>>FYI, you should never change order history.  An order once complete is a permanent record.

Agreed, and this is usually the case.  :-)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now