Primary Key w/Two Means of Data Input

[Dale]

Here is a brief description of what I'm doing before asking the question.

I'm building a CRM type solution for tracking customer information -
contacts, quotes, etc. We have existing customer information that I can
download from our old, still in use, DOS system into an excel file that I can
import/link into our Access 2003 CRM Database. These customers have a unique
customer number: 12ACC1234 as an example. I figured I should use 12ACC1234
for a primary key so that when I update the excel file at some point in the
future, I can maintain integrity with the tracking CRM data we've added in
Access. If I assigned a generic primary key from Access (i.e. 1, 2, 3, 4)
that when I refresh the excel sheet with new customers entered into the old
DOS system, there would be a missmatch - customer number 12ACC1234 may have
had a primary key of 15 but now is 16 since we've entered a new customer in
the old DOS system that falls before customer 12ACC1234.

My dilema is a prospective customer that we want to create new in Access to
track CRM data but they won't as of yet be entered into the old DOS system -
only when we sell a product or service will a customer be given a customer
number such as 12ACC1234 in the old DOS system.

So the question is: am I faced with keeping two sets of tables of CRM data
and therfore two different primary keys? One based on existing clients
(12ACC1234) and one based on potentail clients (1,2,3,4)?

Thanks in advance from this novice user!

 

[Stefan Hoffmann]

hi Dale,

So the question is: am I faced with keeping two sets of tables of CRM data
and therfore two different primary keys? One based on existing clients
(12ACC1234) and one based on potentail clients (1,2,3,4)?

The question is: What does happen with your old system? Do you migrate
to your new one? Do you use this customer number as a reference number
in (all) other systems.

While your in migration, I think, you need to create the customers in
your old system, otherwise you may get the mentioned conflicts.

But it is not wrong to design your new system using surrogat keys
(autonumbers).

I would use:

Table Customer:
ID, Primary Key, Autonumber
ReferenceKey, Text(), Unique Index
ReferenceImported, Boolean

Depending on the answers to the questions above, you may consider
storing the numbers in an extra table for imported values only:

CustomerReference:
CustomerID, Foreign Key
ReferenceKey, Text()



mfG
--> stefan <--

 

[John W. Vinson]

If I assigned a generic primary key from Access (i.e. 1, 2, 3, 4)
that when I refresh the excel sheet with new customers entered into the old
DOS system, there would be a missmatch - customer number 12ACC1234 may have
had a primary key of 15 but now is 16 since we've entered a new customer in
the old DOS system that falls before customer 12ACC1234.

Don't confuse an AUTONUMBER with a PRIMARY KEY. Microsoft does what it can to
foster this confusion by suggesting an autonumber when you create a table -
but an autonumber is *not* the only way to get a PK! A Primary Key can be a
Number, a Text value, even a Date/Time - anything but a Memo field. It can
even consist of multiple fields (up to ten in fact).

If your customer number is stable, unique, and can be generated easily, by all
means use a Text datatype containing 12ACC1234 as your primary key. This will
maintain consistancy with your existing system and should work fine.