• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

contact changes company. where are the orders?

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
Shawn
Asked:
Shawn
  • 4
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ShawnAuthor Commented:
(2) looks like the solution though a little daunting at first glance
0
 
ShawnAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott PletcherSenior DBACommented:
>> 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
 
ShawnAuthor Commented:
>>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
 
Scott PletcherSenior DBACommented:
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
 
ShawnAuthor Commented:
>>FYI, you should never change order history.  An order once complete is a permanent record.

Agreed, and this is usually the case.  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now