Solved

contact changes company. where are the orders?

Posted on 2013-01-23
7
253 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
Comment Utility
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
Comment Utility
(2) looks like the solution though a little daunting at first glance
0
 
LVL 1

Author Comment

by:Shawn
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
>> 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
Comment Utility
>>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:ScottPletcher
Comment Utility
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
Comment Utility
>>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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 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

11 Experts available now in Live!

Get 1:1 Help Now