Update foreign key before appending records to central database

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be completed
for each inspection and the forms would be sent to a central location to be
entered into the database. 50 to 250 inspections would be done at a site and
50 to 250 forms would be entered into the database for each inspection.
There are approximately 70,000 records in the database currently. The number
of inspections has quickly grown to where it will soon be impossible to
enter all the forms into one central database. The plan now is to enter the
inspections into a dozen satellite databases consisting of TblOwner and
TblInspection and then after each inspection send the database file to the
central database location and append the records in TblOwner and
TblInspection into the same named tables in the central database. The
sattelite database location would then be sent an empty satellite database
to repeat the process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite database
arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records will
have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to be
updated to the corresponding OwnerID values (70001 to 70250) before the
Inspection records can be appended. The values OwnerID need to be updated to
will continue to increase as each sattelite database arrives. I need
suggestions on how to determine what values to update OwnerID in
TblInspection in the sattelite database to considering that OwnerID in the
TblOwner in the sattelite database may not be contiguous due to the nature
that it's an autonumber field.

Thanks,

Steve

re: Update foreign key before appending records to central database

Does order of the OwnerID really matter, or can you just create a
random ID for each customer? If you can use random numbers for the
IDs, then I see no reason why creating another field in the parent and
child tables is that big of a deal. Then you can populate the parent
values and cascade them to the children using the existing
relationships. Then if you use the RandomID as your PK in the
different databases, you shouldn't have a problem...

or did I miss a key element here?

re: Update foreign key before appending records to central database

Create a set of staging tables with cascading updates
from the primary key field. Import the records to the
staging tables, update the primary key values so that
they don't clash with existing values, then import to
the destination table.

Or use a multifield primary key with a site index.

Or use replication.

Or use random autonumber if you have only 2 or 3 sites.

(replication uses guid's instead of random longs for
more replication sites, but you will regret it if you
try to use guid autonumbers yourself: they are poorly
supported in queries and code)

(david)

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:B_fVe.10671$9i4.7994@newsread2.news.atl.earth link.net...[color=blue]
> Looking for suggestions ----
>
> A database was designed for a national automobile inspection program. In
> it's simplest form, the database has two tables:
> TblOwner
> OwnerID
> <Year/Make/Model owned by owner, Owner name/address, etc)
>
> TblInspection
> InspectionID
> OwnerID
> <~50 inspection results>
>
> Originally, inspections would be done at a site, a form would be completed
> for each inspection and the forms would be sent to a central location to
> be entered into the database. 50 to 250 inspections would be done at a
> site and 50 to 250 forms would be entered into the database for each
> inspection. There are approximately 70,000 records in the database
> currently. The number of inspections has quickly grown to where it will
> soon be impossible to enter all the forms into one central database. The
> plan now is to enter the inspections into a dozen satellite databases
> consisting of TblOwner and TblInspection and then after each inspection
> send the database file to the central database location and append the
> records in TblOwner and TblInspection into the same named tables in the
> central database. The sattelite database location would then be sent an
> empty satellite database to repeat the process. OwnerID and InspectionID
> would start over at 1.
>
> The problem is in the appending process. With 70,000 records, the last
> OwnerID is 70,000 at this point in time. When the first sattellite
> database arrives, the OwnerID in TblOwner will start at 1 and end at 250.
> Correspondingly, the foreign key, OwnerID in TblInspection, will contain
> values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
> will be appended to TblOwner in the central database. The Owner records
> will have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs
> to be updated to the corresponding OwnerID values (70001 to 70250) before
> the Inspection records can be appended. The values OwnerID need to be
> updated to will continue to increase as each sattelite database arrives. I
> need suggestions on how to determine what values to update OwnerID in
> TblInspection in the sattelite database to considering that OwnerID in the
> TblOwner in the sattelite database may not be contiguous due to the nature
> that it's an autonumber field.
>
> Thanks,
>
> Steve
>[/color]

re: Update foreign key before appending records to central database

My favorite strategy is to use a composite key in the central database that
includes a batch ID and the ID value generated in the remote database. You
generally want the batch ID anyway, so it's not an extra field, though it is a
larger PK index.

On Mon, 12 Sep 2005 14:09:37 GMT, "PC Datasheet" <nospam@nospam.spam> wrote:
[color=blue]
>Looking for suggestions ----
>
>A database was designed for a national automobile inspection program. In
>it's simplest form, the database has two tables:
>TblOwner
>OwnerID
><Year/Make/Model owned by owner, Owner name/address, etc)
>
>TblInspection
>InspectionID
>OwnerID
><~50 inspection results>
>
>Originally, inspections would be done at a site, a form would be completed
>for each inspection and the forms would be sent to a central location to be
>entered into the database. 50 to 250 inspections would be done at a site and
>50 to 250 forms would be entered into the database for each inspection.
>There are approximately 70,000 records in the database currently. The number
>of inspections has quickly grown to where it will soon be impossible to
>enter all the forms into one central database. The plan now is to enter the
>inspections into a dozen satellite databases consisting of TblOwner and
>TblInspection and then after each inspection send the database file to the
>central database location and append the records in TblOwner and
>TblInspection into the same named tables in the central database. The
>sattelite database location would then be sent an empty satellite database
>to repeat the process. OwnerID and InspectionID would start over at 1.
>
>The problem is in the appending process. With 70,000 records, the last
>OwnerID is 70,000 at this point in time. When the first sattellite database
>arrives, the OwnerID in TblOwner will start at 1 and end at 250.
>Correspondingly, the foreign key, OwnerID in TblInspection, will contain
>values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
>will be appended to TblOwner in the central database. The Owner records will
>have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to be
>updated to the corresponding OwnerID values (70001 to 70250) before the
>Inspection records can be appended. The values OwnerID need to be updated to
>will continue to increase as each sattelite database arrives. I need
>suggestions on how to determine what values to update OwnerID in
>TblInspection in the sattelite database to considering that OwnerID in the
>TblOwner in the sattelite database may not be contiguous due to the nature
>that it's an autonumber field.
>
>Thanks,
>
>Steve
>[/color]

re: Update foreign key before appending records to central database

David,

Thanks for responding. Could you give more details about your staging tables
concept.

Thanks!

Steve


"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43263e27$0$21179$c30e37c6@lon-reader.news.telstra.net...[color=blue]
> Create a set of staging tables with cascading updates
> from the primary key field. Import the records to the
> staging tables, update the primary key values so that
> they don't clash with existing values, then import to
> the destination table.
>
> Or use a multifield primary key with a site index.
>
> Or use replication.
>
> Or use random autonumber if you have only 2 or 3 sites.
>
> (replication uses guid's instead of random longs for
> more replication sites, but you will regret it if you
> try to use guid autonumbers yourself: they are poorly
> supported in queries and code)
>
> (david)
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:B_fVe.10671$9i4.7994@newsread2.news.atl.earth link.net...[color=green]
>> Looking for suggestions ----
>>
>> A database was designed for a national automobile inspection program. In
>> it's simplest form, the database has two tables:
>> TblOwner
>> OwnerID
>> <Year/Make/Model owned by owner, Owner name/address, etc)
>>
>> TblInspection
>> InspectionID
>> OwnerID
>> <~50 inspection results>
>>
>> Originally, inspections would be done at a site, a form would be
>> completed for each inspection and the forms would be sent to a central
>> location to be entered into the database. 50 to 250 inspections would be
>> done at a site and 50 to 250 forms would be entered into the database for
>> each inspection. There are approximately 70,000 records in the database
>> currently. The number of inspections has quickly grown to where it will
>> soon be impossible to enter all the forms into one central database. The
>> plan now is to enter the inspections into a dozen satellite databases
>> consisting of TblOwner and TblInspection and then after each inspection
>> send the database file to the central database location and append the
>> records in TblOwner and TblInspection into the same named tables in the
>> central database. The sattelite database location would then be sent an
>> empty satellite database to repeat the process. OwnerID and InspectionID
>> would start over at 1.
>>
>> The problem is in the appending process. With 70,000 records, the last
>> OwnerID is 70,000 at this point in time. When the first sattellite
>> database arrives, the OwnerID in TblOwner will start at 1 and end at 250.
>> Correspondingly, the foreign key, OwnerID in TblInspection, will contain
>> values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
>> will be appended to TblOwner in the central database. The Owner records
>> will have OwnerID values of 70001 to 70250. OwnerID in TblInspection
>> needs to be updated to the corresponding OwnerID values (70001 to 70250)
>> before the Inspection records can be appended. The values OwnerID need to
>> be updated to will continue to increase as each sattelite database
>> arrives. I need suggestions on how to determine what values to update
>> OwnerID in TblInspection in the sattelite database to considering that
>> OwnerID in the TblOwner in the sattelite database may not be contiguous
>> due to the nature that it's an autonumber field.
>>
>> Thanks,
>>
>> Steve
>>[/color]
>
>[/color]

re: Update foreign key before appending records to central database

Steve,

Thank you for responding! Your startegy seems like it would be good if
starting from scratch but im this case the first batch of owner records will
be appended to the central database and their OwnerIDs will become 70001 to
70250. The foreign key in the inspection records must then take on these
same values.

Steve


"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:gioci1t355plgsc7femjv8p7abhmgljlpu@4ax.com...[color=blue]
> My favorite strategy is to use a composite key in the central database
> that
> includes a batch ID and the ID value generated in the remote database.
> You
> generally want the batch ID anyway, so it's not an extra field, though it
> is a
> larger PK index.
>
> On Mon, 12 Sep 2005 14:09:37 GMT, "PC Datasheet" <nospam@nospam.spam>
> wrote:
>[color=green]
>>Looking for suggestions ----
>>
>>A database was designed for a national automobile inspection program. In
>>it's simplest form, the database has two tables:
>>TblOwner
>>OwnerID
>><Year/Make/Model owned by owner, Owner name/address, etc)
>>
>>TblInspection
>>InspectionID
>>OwnerID
>><~50 inspection results>
>>
>>Originally, inspections would be done at a site, a form would be completed
>>for each inspection and the forms would be sent to a central location to
>>be
>>entered into the database. 50 to 250 inspections would be done at a site
>>and
>>50 to 250 forms would be entered into the database for each inspection.
>>There are approximately 70,000 records in the database currently. The
>>number
>>of inspections has quickly grown to where it will soon be impossible to
>>enter all the forms into one central database. The plan now is to enter
>>the
>>inspections into a dozen satellite databases consisting of TblOwner and
>>TblInspection and then after each inspection send the database file to the
>>central database location and append the records in TblOwner and
>>TblInspection into the same named tables in the central database. The
>>sattelite database location would then be sent an empty satellite database
>>to repeat the process. OwnerID and InspectionID would start over at 1.
>>
>>The problem is in the appending process. With 70,000 records, the last
>>OwnerID is 70,000 at this point in time. When the first sattellite
>>database
>>arrives, the OwnerID in TblOwner will start at 1 and end at 250.
>>Correspondingly, the foreign key, OwnerID in TblInspection, will contain
>>values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
>>will be appended to TblOwner in the central database. The Owner records
>>will
>>have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to
>>be
>>updated to the corresponding OwnerID values (70001 to 70250) before the
>>Inspection records can be appended. The values OwnerID need to be updated
>>to
>>will continue to increase as each sattelite database arrives. I need
>>suggestions on how to determine what values to update OwnerID in
>>TblInspection in the sattelite database to considering that OwnerID in the
>>TblOwner in the sattelite database may not be contiguous due to the nature
>>that it's an autonumber field.
>>
>>Thanks,
>>
>>Steve
>>[/color]
>[/color]

re: Update foreign key before appending records to central database

Make a copy of the source tables. You can use the
source database, destination database, or third database.

Make sure the tables have cascading update defined
on the relationships. (Change if necessary)
Create an update query for the primary table, which
updates the primary key to [original value] + [offset]
Get the offset in the query by using DMAX() on the
destination table. The cascading update will fix
the dependant tables.

After you have updated the staging tables,
append to the destination tables

Then delete the values from the staging tables,
or discard the database.

(david)

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:7BCVe.11121$9i4.4269@newsread2.news.atl.earth link.net...[color=blue]
> David,
>
> Thanks for responding. Could you give more details about your staging
> tables concept.
>
> Thanks!
>
> Steve
>
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:43263e27$0$21179$c30e37c6@lon-reader.news.telstra.net...[color=green]
>> Create a set of staging tables with cascading updates
>> from the primary key field. Import the records to the
>> staging tables, update the primary key values so that
>> they don't clash with existing values, then import to
>> the destination table.
>>
>> Or use a multifield primary key with a site index.
>>
>> Or use replication.
>>
>> Or use random autonumber if you have only 2 or 3 sites.
>>
>> (replication uses guid's instead of random longs for
>> more replication sites, but you will regret it if you
>> try to use guid autonumbers yourself: they are poorly
>> supported in queries and code)
>>
>> (david)
>>
>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>> news:B_fVe.10671$9i4.7994@newsread2.news.atl.earth link.net...[color=darkred]
>>> Looking for suggestions ----
>>>
>>> A database was designed for a national automobile inspection program. In
>>> it's simplest form, the database has two tables:
>>> TblOwner
>>> OwnerID
>>> <Year/Make/Model owned by owner, Owner name/address, etc)
>>>
>>> TblInspection
>>> InspectionID
>>> OwnerID
>>> <~50 inspection results>
>>>
>>> Originally, inspections would be done at a site, a form would be
>>> completed for each inspection and the forms would be sent to a central
>>> location to be entered into the database. 50 to 250 inspections would be
>>> done at a site and 50 to 250 forms would be entered into the database
>>> for each inspection. There are approximately 70,000 records in the
>>> database currently. The number of inspections has quickly grown to where
>>> it will soon be impossible to enter all the forms into one central
>>> database. The plan now is to enter the inspections into a dozen
>>> satellite databases consisting of TblOwner and TblInspection and then
>>> after each inspection send the database file to the central database
>>> location and append the records in TblOwner and TblInspection into the
>>> same named tables in the central database. The sattelite database
>>> location would then be sent an empty satellite database to repeat the
>>> process. OwnerID and InspectionID would start over at 1.
>>>
>>> The problem is in the appending process. With 70,000 records, the last
>>> OwnerID is 70,000 at this point in time. When the first sattellite
>>> database arrives, the OwnerID in TblOwner will start at 1 and end at
>>> 250. Correspondingly, the foreign key, OwnerID in TblInspection, will
>>> contain values of 1 to 250. When TblOwner is appended, all the fields
>>> but OwnerID will be appended to TblOwner in the central database. The
>>> Owner records will have OwnerID values of 70001 to 70250. OwnerID in
>>> TblInspection needs to be updated to the corresponding OwnerID values
>>> (70001 to 70250) before the Inspection records can be appended. The
>>> values OwnerID need to be updated to will continue to increase as each
>>> sattelite database arrives. I need suggestions on how to determine what
>>> values to update OwnerID in TblInspection in the sattelite database to
>>> considering that OwnerID in the TblOwner in the sattelite database may
>>> not be contiguous due to the nature that it's an autonumber field.
>>>
>>> Thanks,
>>>
>>> Steve
>>>[/color]
>>
>>[/color]
>
>[/color]

re: Update foreign key before appending records to central database

Thanks, David, appreciate the help!

Steve



"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:4327c000$0$21189$c30e37c6@lon-reader.news.telstra.net...[color=blue]
> Make a copy of the source tables. You can use the
> source database, destination database, or third database.
>
> Make sure the tables have cascading update defined
> on the relationships. (Change if necessary)
> Create an update query for the primary table, which
> updates the primary key to [original value] + [offset]
> Get the offset in the query by using DMAX() on the
> destination table. The cascading update will fix
> the dependant tables.
>
> After you have updated the staging tables,
> append to the destination tables
>
> Then delete the values from the staging tables,
> or discard the database.
>
> (david)
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:7BCVe.11121$9i4.4269@newsread2.news.atl.earth link.net...[color=green]
>> David,
>>
>> Thanks for responding. Could you give more details about your staging
>> tables concept.
>>
>> Thanks!
>>
>> Steve
>>
>>
>> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
>> news:43263e27$0$21179$c30e37c6@lon-reader.news.telstra.net...[color=darkred]
>>> Create a set of staging tables with cascading updates
>>> from the primary key field. Import the records to the
>>> staging tables, update the primary key values so that
>>> they don't clash with existing values, then import to
>>> the destination table.
>>>
>>> Or use a multifield primary key with a site index.
>>>
>>> Or use replication.
>>>
>>> Or use random autonumber if you have only 2 or 3 sites.
>>>
>>> (replication uses guid's instead of random longs for
>>> more replication sites, but you will regret it if you
>>> try to use guid autonumbers yourself: they are poorly
>>> supported in queries and code)
>>>
>>> (david)
>>>
>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>> news:B_fVe.10671$9i4.7994@newsread2.news.atl.earth link.net...
>>>> Looking for suggestions ----
>>>>
>>>> A database was designed for a national automobile inspection program.
>>>> In it's simplest form, the database has two tables:
>>>> TblOwner
>>>> OwnerID
>>>> <Year/Make/Model owned by owner, Owner name/address, etc)
>>>>
>>>> TblInspection
>>>> InspectionID
>>>> OwnerID
>>>> <~50 inspection results>
>>>>
>>>> Originally, inspections would be done at a site, a form would be
>>>> completed for each inspection and the forms would be sent to a central
>>>> location to be entered into the database. 50 to 250 inspections would
>>>> be done at a site and 50 to 250 forms would be entered into the
>>>> database for each inspection. There are approximately 70,000 records in
>>>> the database currently. The number of inspections has quickly grown to
>>>> where it will soon be impossible to enter all the forms into one
>>>> central database. The plan now is to enter the inspections into a dozen
>>>> satellite databases consisting of TblOwner and TblInspection and then
>>>> after each inspection send the database file to the central database
>>>> location and append the records in TblOwner and TblInspection into the
>>>> same named tables in the central database. The sattelite database
>>>> location would then be sent an empty satellite database to repeat the
>>>> process. OwnerID and InspectionID would start over at 1.
>>>>
>>>> The problem is in the appending process. With 70,000 records, the last
>>>> OwnerID is 70,000 at this point in time. When the first sattellite
>>>> database arrives, the OwnerID in TblOwner will start at 1 and end at
>>>> 250. Correspondingly, the foreign key, OwnerID in TblInspection, will
>>>> contain values of 1 to 250. When TblOwner is appended, all the fields
>>>> but OwnerID will be appended to TblOwner in the central database. The
>>>> Owner records will have OwnerID values of 70001 to 70250. OwnerID in
>>>> TblInspection needs to be updated to the corresponding OwnerID values
>>>> (70001 to 70250) before the Inspection records can be appended. The
>>>> values OwnerID need to be updated to will continue to increase as each
>>>> sattelite database arrives. I need suggestions on how to determine what
>>>> values to update OwnerID in TblInspection in the sattelite database to
>>>> considering that OwnerID in the TblOwner in the sattelite database may
>>>> not be contiguous due to the nature that it's an autonumber field.
>>>>
>>>> Thanks,
>>>>
>>>> Steve
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]