If the natural keys are hard to identify, you can still use surrogates for both regional databases and the master one.
For example, regional database schema:
CREATE TABLE main_table (
region_id INT NOT NULL AUTO_INCREMENT,
region_name CHAR(30) NOT NULL,
[other fields],
PRIMARY KEY (region_id)
);
The master database schema would look like this:
CREATE TABLE main_table (
id INT NOT NULL AUTO_INCREMENT,
region_id INT NOT NULL,
region_name CHAR(30) NOT NULL,
[other fields],
PRIMARY KEY (id)
);
The trick is to have every regional system to supply unique value for "region_name" field. Then each system will happily collect data and no problems will happen during aggregation. The master database will have its own unique IDs and there is going to be a reference to the origin of data (region_id, region_name).
The biggest headache with this approach is references to other tables. When you copy data from regional to master database, you will also have to employ similar approach to other tables, which is, as I said, a headache.
Another way is to use a compound PK:
CREATE TABLE main_table (
region_id INT NOT NULL AUTO_INCREMENT,
region_name CHAR(30) NOT NULL,
[other fields],
PRIMARY KEY (region_id, region_name)
);
This way the table is going to look exactly the same between regions and the master. At the same time, compound keys is a headache on its own.
As I'm writing this answer, I understand that there is no easy solution, as always :)
Update:
If a natural key is used then there is going to be one unified schema:
CREATE TABLE main_table (
id CAHR(32) NOT NULL, -- most likely ID is going to be a string, not number
region_name CHAR(30) NOT NULL, -- if you want to keep the info about the origin of data
[other fields],
PRIMARY KEY (id)
);
The idea of a natural key, that it's unique regardless of a region. In books, the usual examples are Social Security Number, Passport Number, etc.
You can have a simple hybrid solution. The schema stays as depicted above, and you calculate ID values yourself:
UYT49.2873645
UYT49.2873646
UYT23.7824
The first part of the string is your region id, the second part - auto-incremental number. As a whole, the string is globally unique which is what you want.
