Append records that have auto numbered key (97)

Topic

New Reply

I have over 35 regions that each need their own database. I must maintain a master database. I did not want to deal will synchronized databases, and all regions can not access the master database anyway. Each region is required to have their own, separate data, and they will send me their data quarterly. At that time I want to append all data into my master database. My main question is this: I was going to use an auto number as the primary key for the records (there is no other unique number in this database such as a ssn number or order number). Can I use an autonumber as the primary key in all of the databases, and still be able to append the data into the master database. What if there are duplicate numbers in some of the databases? Is there another way for me to create a primary key, other than an auto number? Any suggestions will be greatly appreciated!

Thanks,
Claudie

Reply | Quote

Replies

First of all, how do you relate the data you import back to the data that comes from the regions? You’ll need to do that in order to insure that the same data doesn’t get imported twice.

The easiest way I can suggest is to have an autonumber built into the table or tables in your master database. If the regions are already using an autonumber in their versions, you should have an additional long integer field in the tables in your master database. When you append the records from each region, you would append their autonumber to your long integer field and allow your database to create its own autonumber for your use and as a primary key. You would create an additional unique key on each table using the long integer field and the region identifier to insure that the same record could not be imported twice.

If the regions happen to have duplicate autonumbers (which is highly likely), it won’t be a problem with this approach, but you’ll still be able to handle updates if necessary because you have preserved their individual keys in your table.

Reply | Quote

Thank you! I have one more question about appending the records to the master table. Can you make the append, only import new and changed reecords? I did not want to import all records each time, but I also, did not want to only import new records, because then I would not get any pre-existing records that have been edited.

Reply | Quote

In theory it is possible – BUT how do you know which records have been imported already and which have not. Rather than using an autonumber key, I would create a primary key that uses one additional fields over and above what the regions send you. That would be the Region identifier. In each append query you would specify which region sent you the records. That in conjunction with their autonumber would be unique. Then if they send you records you could check for duplicates and reject them. You might also put a field into each of the records that indicated they had been sent to the central site, and as a part of the process of sending the data to you, put the flag in. That way they would only send records that had not been sent.

Another option that would give you unique keys in the short run would be to make the autnumber field Random rather than Increment. That way the record IDs run from -2,000,000,000 to +2,000,000,000 and there is only a small probability that you will ever come up with dups. Obviously over time if you generate lots of records then it does get larger. I find that kind of key awkward to work with however if you need to work at the table level to debug a problem. Short numbers are easier to remember and put into filters or queries. The other option is to use GUIDs – they are virtually guaranteed to be unique. They do however consume more space, and we’ve seen significant performance hits if you try to use them as the primary key. Hope this helps.

Reply | Quote

If you’ve created a unique key based on the region identifier and their (converted) autonumber primary key, you won’t import the same records twice. Changed records aren’t handled with appends, they’re handled with updates. If the source tables contained a datestamp that was changed when the record was edited, you could match the stamps up and update records where the datestamp in the region’s data was later than the one stored in your master database.

Reply | Quote