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
