Solved

contact changes company. where are the orders?

Posted on 2013-01-23
7
257 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

713 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