Solved

contact changes company. where are the orders?

Posted on 2013-01-23
7
255 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 …
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…

776 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