Synchronizing autonumber fields

Hi

I have the following tables setup in MYSQL:

Region 1 Region 2
HQ
Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
Tbl1 autonumbered-PK

To explain the above. Basically there are two regions that collect
information and then at the end of each month they have to send the
information to HQ. This is fine, but the problem comes when the
information (the data in the tables) is submitted to HQ. All three
tables have the same names and the same structure. We want to
synchronize the information sent by the regions into one table at HQ.
How can we do this without having the duplicate number problem?

Thanks

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Synchronizing autonumber fields

Leidago !Noabeb wrote:

> Hi
>=20
> I have the following tables setup in MYSQL:
>=20
> Region 1 Region 2
> HQ
> Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
> Tbl1 autonumbered-PK
>=20
> To explain the above. Basically there are two regions that collect
> information and then at the end of each month they have to send the
> information to HQ. This is fine, but the problem comes when the
> information (the data in the tables) is submitted to HQ. All three
> tables have the same names and the same structure. We want to
> synchronize the information sent by the regions into one table at HQ.=

> How can we do this without having the duplicate number problem?

Typically you would do this by having a sufficiently large offset
between the numbers. =20


/Per

--=20
Per Jessen, Zürich (19.6°C)


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Synchronizing autonumber fields

hi there,

this is typical app for db repliction mechanism. it depnds of the size off
your project.

have a look here:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.htm l

if this is oversized for u do a dump of each table without the
auto_increment fields. if u read in these tables the main database then does
use its own record id.
to identifie these records use a subsidairy finegerprint field.

another way would be not to use a ai field for identifying the record, use a
timestamp field to have an unique index on the tables. it is very unlikeley
that two records are written at the same time in the various subsidaries. a
timestamp field is a breakdown to the milisecond. however there is still a
chance of 1 to some billion, that two records have the same key.

just some possibilities

cheers
ralph
ralph_deffke@yahoo.de


""Leidago !Noabeb"" wrote in message
news:5bcf496e0908110004w94d29c2j4b01806822ca0143@mail.gmail. com...
> Hi
>
> I have the following tables setup in MYSQL:
>
> Region 1 Region 2
> HQ
> Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
> Tbl1 autonumbered-PK
>
> To explain the above. Basically there are two regions that collect
> information and then at the end of each month they have to send the
> information to HQ. This is fine, but the problem comes when the
> information (the data in the tables) is submitted to HQ. All three
> tables have the same names and the same structure. We want to
> synchronize the information sent by the regions into one table at HQ.
> How can we do this without having the duplicate number problem?
>
> Thanks



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Synchronizing autonumber fields

On Tue, 11 Aug 2009 10:04:52 +0300, "Leidago !Noabeb"
wrote:

> Hi
>
> I have the following tables setup in MYSQL:
>
> Region 1 Region 2
> HQ
> Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
> Tbl1 autonumbered-PK
>
> To explain the above. Basically there are two regions that collect
> information and then at the end of each month they have to send the
> information to HQ. This is fine, but the problem comes when the
> information (the data in the tables) is submitted to HQ. All three
> tables have the same names and the same structure. We want to
> synchronize the information sent by the regions into one table at HQ.
> How can we do this without having the duplicate number problem?
>
> Thanks

You can change system, so it will increment not by one, but by 2:

then Region 1 with have numbers:
1, 3 ,5 , 7 , etc
Region 2: 2, 4 ,6, 8 etc

then they are going to be easily mixed together.
As a bonus - it will be easy to see from where this record came.

Only problem - you can do it only on server level, not table level
http://forums.mysql.com/read.php?10,269379,269436#msg-269436


--

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Synchronizing autonumber fields

On Aug 11, 2009, at 3:04 AM, "Leidago !Noabeb"
wrote:

> Hi
>
> I have the following tables setup in MYSQL:
>
> Region 1 Region 2
> HQ
> Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
> Tbl1 autonumbered-PK
>
> To explain the above. Basically there are two regions that collect
> information and then at the end of each month they have to send the
> information to HQ. This is fine, but the problem comes when the
> information (the data in the tables) is submitted to HQ. All three
> tables have the same names and the same structure. We want to
> synchronize the information sent by the regions into one table at HQ.
> How can we do this without having the duplicate number problem?
>
> Thanks
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

The simplest thing would be to have the main table have it's own auto
increment key and use the two sub tables keys as a numerical field,
perhaps as a foreign key.

As a general rule, ai keys should only be used for uniqueness, and not
have any real meaning within the datasets.

Bastien

Sent from my iPod

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Synchronizing autonumber fields

At 9:04 AM +0200 8/11/09, Leidago !Noabeb wrote:
>Hi
>
>I have the following tables setup in MYSQL:
>
>Region 1 Region 2
> HQ
>Tbl1 with autonumbered (PK) Tbl1 with autonumbered (PK)
> Tbl1 autonumbered-PK
>
>To explain the above. Basically there are two regions that collect
>information and then at the end of each month they have to send the
>information to HQ. This is fine, but the problem comes when the
>information (the data in the tables) is submitted to HQ. All three
>tables have the same names and the same structure. We want to
>synchronize the information sent by the regions into one table at HQ.
>How can we do this without having the duplicate number problem?

Leidago :

Basically you have data from Region 1 and Region 2 and you want to
send that information to HQ.

There are several ways to do this, but why not simply have a field in
your table that defines which region. Clearly, HQ doesn't want to
lose track of where the data came from, right?

As such, I don't see any problem with HQ receiving data from either
region regardless of the possibility of the data having the same
"number problem", whatever that may be.

The "number problem" is reminiscent of one of those "We have to
consider the index of the records for some purpose" when that's
usually for the internal workings of the database. If you want to
access this data like a relational database, then you can use the
index. But remember that the indexes do not have to be sequential
without gaps. In the real world records are both created and deleted.

In any event, Region 1 data might have:

Index - Name - Address
1. Sam 123 Main
2. Joe 456 Elm
3. Ed 789 Oak

So Region 2 data might have:

Index - Name - Address
1. Harry 456 Oak
2. Sally 789 Pine
3. Wally 123 Maple

Thus, HQ data would be:

Index - Region - Region Index - Name - Address
1. 1 1 Sam 123 Main
2. 1 2 Joe 456 Elm
3. 1 3 Ed 789 Oak
4 2 1 Harry 456 Oak
5. 2 2 Sally 789 Pine
6. 2 3 Wally 123 Maple

Just add the data sent from Regions 1 and 2 to the HQ database. Also,
note that the indexes do not have to match, or be sequential without
gaps -- they could have been:

Region 1:

Index - Name - Address
456. Sam 123 Main
458. Joe 456 Elm
560. Ed 789 Oak

Region 2:

Index - Name - Address
1010. Harry 456 Oak
1014. Sally 789 Pine
1021. Wally 123 Maple

Thus, HQ could be:

Index - Region - Region Index - Name - Address
4567. 1 456 Sam 123 Main
4568. 1 458 Joe 456 Elm
4569. 1 560 Ed 789 Oak
4570 2 1010 Harry 456 Oak
4571. 2 1014 Sally 789 Pine
4572. 2 1021 Wally 123 Maple

This is the way I would solve this problem. In fact, depending upon
what you want to do with the data, I might dispense with recording
the Name, Address, and other such data in HQ and just record the
indexes from Region 1 and 2. After all, that's redundant data unless
you're going to drop the data collected in Region 1 and 2 after the
exchange. If so, then there's no need to record their indexes.

HTH's

tedd


--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php