GUID / UUID for this scenario? Or a complex key? Or other

Question

I am currently working on a system that in some cases needs to work in a local database during the day and then replicate to a central server during the night. It cannot work from one central database, since local sites are not periodically connected to it. Data on the central server is intended for viewing and reporting only at the head office, so nothing needs to be replicated back to the site.

Each "site" is assigned a unique text key (human generated). However, the idea that each table in the database structure refers to a site key is not attractive.

Here is an example of a very shortened version of the schema without worrying about remote replication (which will work just fine for most clients): -

(I will only show the history table of the Area table so that everything is in order):

[Site]
SiteKey [PK] (Gauranteed 100% unique across all sites text based key)

[User]
SiteKey [FK -> Site]
UserID [PK]

[Area]
SiteKey [FK -> Site]
AreaID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[AreaHistory]
Site [FK -> Site]
AreaID [FK -> Area]
Description
UpdatedDtm
UpdatedUserID [FK -> User]
AuditedDtm

[Location]
AreaID [FK -> Area]
LocationID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[Sensor]
LocationID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserID [FK -> User]

[Reading]
LocationID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

Well, until I come to "combine" the database with the database on the central server. I am obviously going to run into the Location table because I am mixing data with an identifier generated on other sites.

The first way I thought about this problem was as follows:

gs short):

[Location]
SiteKey [FK -> Location, FK -> User] ** ADDED THIS
AreaID [FK -> Area]
LocationID [PK]
Description
UpdatedDtm
UpdatedUserID [FK -> User]

[Sensor]
SiteKey [FK -> Location, FK -> User] ** ADDED THIS
LocationID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserID [FK -> User]

[Reading]
SiteKey [FK -> Sensor] ** ADDED THIS
LocationID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

Basically, each table gets SiteKey, making each row unique to the site.

An alternative is this (using the UUID in some places): -

[User]
SiteKey [FK -> Site]
UserUUID [PK]

[Area]
SiteKey [FK -> Site]
AreaUUID [PK]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]

[AreaHistory]
Site [FK -> Site]
AreaUUID [FK -> Area]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]
AuditedDtm

[Location]
AreaUUID [FK -> Area]
LocationUUID [PK]
Description
UpdatedDtm
UpdatedUserUUID [FK -> User]

[Sensor]
LocationUUID [PK / FK -> Location]
SensorNo [PK]
UpdatedDtm
UpdatedUserUUID [FK -> User]

[Reading]
LocationUUID [PK / FK -> Sensor]
SensorNo [PK / FK -> Sensor]
ReadingDtm [PK]

Remember that this is abbreviated, but it illustrates the problem.

- , ? ID, , , , .

, , , , . , , , -)

, , , UUID , , ( ), .

, PostgreSQL.

PS, . , , , SiteKey.

Answer 1

, IMHO, , UUID, " ", , . , . UUID, SiteKey .

Answer 2

GUID/UUID. . .

Answer 3

, , , , , :

• , - , ( , SQL Server), , . , ( , ).

• , . , , , RI?

• , , ​​ , .

Answer 4

, , GUID ( ). , SQL Server, , , GUID , , . NEWSEQUENTIALID(), GUID:

INT vs ID

I use the COMB ID method, which also had the opportunity to extract data from the GUID, giving you a timestamp for creation (although not necessarily free, because it is not really indexable by itself) (since you already accept additional bytes for GUID vs. int )

I suppose some or most of this logic applies to postgressql, but as far as it is specific to indexing it or implementing clustering / heap, I have no direct experience.