Kind Of Urgent... Problem With Mapping to RDB

[steverbs]

Hi all.

I am currently working on a project that involves the importing of large amounts of data into an Access database and have run into a problem with the mapping of the software system to the relational DB. The problem lies in that two of the key tables have primary keys that are strings and not automated. For example, the Customers table uses the Customer Account No for the primary key and this is referenced by the Orders table. This works for the existing system as the customers are created in an accounts package that prevents a primary key being created twice, but how can I map an object's OID to this?

I was thinking of adding a second, automatic key, such as replication ID. I would then map the OID to this and make a second relationship between the tables (in the RDB and the BLL) that uses this new ID column, but I'm not sure if this would break the existing software system, which is very large, complicated and undocumented. Also, I would have to update all of the live data so that the new relationship fields were populated.

Does anyone have any experience with a similar problem? Any advice here would be greatly appreciated.

Thank you.

Stephen.

 

[SQLSister]

This is a one-time import or will it occur on a scheduled basis?

If it a one-time I would not mess with the current table structure as there may be code that breaks if a new field is added (yes I know you shouldn't wqrite code that way, but in an undocumented system, who knows what bizarre methods they have used.)

I would bring the data into a holding table, then assign the customerids to it using the same methodology that the user interface uses to come up with new ids. To prevent duplicates, I would do this during non-production hours or if it is a 24/7 shop, I would do it during off hours and tell everyone the database would be down during this time persiod and that they could not add customers from 1:00 am to 1:30 am or some such. After creating the new ids, I would then load the data to the real table.

If it must be loaded on a regular schedule and done programmically, I might try your approach, but only after I got a development copy of the database and interface and tested the heck out of it before I loaded the change to the real one.




Questions about posting. See faq183-874

 

[stevexff]

Steve

Assuming you need to add new customers and/or accounts to the tables, then you need to look closely at the key generation component of the existing system, undocumented or not. How does it guarantee uniqueness? Can you replicate this? Or even re-use it in some way, for example by preallocating a range of values that will not collide with anything inserted by the accounts system?

Steve

 

[steverbs]

Thanks for the replies. The main problem with using the existing key system is that the account numbers are manually typed in by the users whenever a new customer is created (all new customers are currently manually created in the accounts package, Sage, and the Access DB's customers table is then updated everyday via an ODBC link to Sage). While the user types in the new key, the accounts package, Sage, lists all of the matching keys to ensure that a unique key is created. The users weren't too keen on the suggestion that they watch the import process and type in a new key each time one of the customers, potentially 1000's of which will be added in the import process every morning during the winter period, were created.

Also, while the format of the new keys is not particularly important as the imported customers will either be matched to existing customers (no key generation needed) or put under a cash-customer head-account (key needed) the users require that the key be no longer than 8 chars and be relatively easy to deal with.

Cheers,

Stephen.

 

[SQLSister]

Ok here's what I suggest - you design a group of account number sthat can only be used for imported customers. Is there any reason why these cannot be numbers? If so just add an autonumber field and then make the PK value the same as the automnumber field during the import process. And make sure the user interface does not allow users to add an account that uses only a number.



Questions about posting. See faq183-874

 

[steverbs]

Nice one. That's given me a perfect solution. What I'll do is create a separate table with an autonumber field and take the value from that and prefix it with something like 'CSH' and use that as the primary key/OID.

Thank you very much.

Stephen.