Schema Design: consigli necessari

Se le chiavi naturali sono difficili da identificare, puoi comunque utilizzare i surrogati sia per i database regionali sia per quelli principali.

Ad esempio, schema del database regionale:

CREATE TABLE main_table (
  region_id INT NOT NULL AUTO_INCREMENT,
  region_name CHAR(30) NOT NULL,
  [other fields],
  PRIMARY KEY (region_id)
);

Lo schema del database master sarà simile a questo:

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)
);

Il trucco consiste nel disporre che ogni sistema regionale fornisca un valore univoco per il campo "region_name". Quindi ogni sistema raccoglierà dati felicemente e non si verificheranno problemi durante l'aggregazione. Il database master avrà i suoi ID univoci e ci sarà un riferimento all'origine dei dati (region_id, region_name).

Il più grande mal di testa con questo approccio è il riferimento ad altre tabelle. Quando copi i dati dal database regionale a quello master, dovrai anche impiegare un approccio simile ad altre tabelle, che è, come ho detto, un mal di testa.

Un altro modo è usare un PK composto:

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)
);

In questo modo la tabella avrà esattamente lo stesso aspetto tra le regioni e il master. Allo stesso tempo, le chiavi composte sono un mal di testa da solo.

Mentre scrivo questa risposta, capisco che non esiste una soluzione facile, come sempre:)

Aggiornamento:

Se viene utilizzata una chiave naturale, ci sarà uno schema unificato:

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)
);

L'idea di una chiave naturale, che è unica indipendentemente da una regione. Nei libri, i soliti esempi sono il numero di previdenza sociale, il numero di passaporto, ecc.

Puoi avere una soluzione ibrida semplice. Lo schema rimane come illustrato sopra e tu stesso calcoli i valori ID:

UYT49.2873645
UYT49.2873646
UYT23.7824

La prima parte della stringa è l'ID della tua regione, la seconda parte, il numero auto-incrementale. Nel complesso, la stringa è globalmente unica, ed è ciò che desideri.

Potresti considerare l'utilizzo di un GUID come chiave primaria per ogni voce nel database. Quando i tuoi database regionali vengono uniti, non ti devi preoccupare degli ID duplicati.

Cerca di identificare le chiavi naturali dai tuoi dati, non causeranno gli stessi problemi dei surrogati quando arriverete a unire i vostri dati.

Come suggerito, usa qualsiasi implementazione UUID, GUID è uno di questi. Praticamente garantisce che gli id che si generano non sono equi. Altro su link

Il vantaggio di usarlo tra gli altri è - la maggior parte dei linguaggi / tecnologie / framework / database di programmazione lo supporta, in quanto è uno standard del settore (RFC Link: link ). Estratti da RFC ...

1. Motivation

   One of the main reasons for using UUIDs is that no centralized
   authority is required to administer them (although one format uses IEEE 802 node identifiers, others do not). As a result, generation on demand can be completely automated, and used for a variety of purposes. The UUID generation algorithm described here supports very high allocation rates of up to 10 million per second per machine if necessary, so that they could even be used as transaction IDs.

   UUIDs are of a fixed size (128 bits) which is reasonably small
   compared to other alternatives. This lends itself well to sorting,
   ordering, and hashing of all sorts, storing in databases, simple
   allocation, and ease of programming in general.