If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Primary Key w/Two Means of Data Input
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! -- Dale, wishing we had one ERP database! |
#2
|
|||
|
|||
Primary Key w/Two Means of Data Input
hi Dale,
Dale wrote: 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 -- |
#3
|
|||
|
|||
Primary Key w/Two Means of Data Input
On Sun, 14 Dec 2008 07:13:00 -0800, Dale
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|