I am presently focusing on a method that in some instances will have to operate on a nearby database throughout your day, after which duplicated to some central server throughout the evening. It cannot all run in one central database because the local sites are from connection with it periodically. The information in the central server is perfect for viewing and confirming only at hq, so nothing must be reverse duplicated to the website.

Each "site" is offered a text based unique key (human produced). However, the idea of making every table within the database design reference the website secret is not appealing.

Here's one particular very reduce version from the schema without worrying about remote replication (that will work fine for almost all clients) : -

(I'll only show a brief history table for that Area table, to help keep things short) :

[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]

That is fine, until I arrived at "merge" the database using the database in the central server. I am clearly getting clashes within the Location table because I am mixing data with ID's produced at other sites.

The very first way I figured for this problem was to get this done:

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]

Essentially, every table will get a SiteKey making each row unique towards the site.

An alternate is (using UUIDs occasionally) : -

[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]

Don't forget this is cut lower, however it demonstrates the issue.

What are the options I might be missing? I figured about ID remapping but that appeared introducing new a whole lot worse bad dreams.

The annoying thing about this is it's a very few cases which use this, the relaxation are pleased with one database serving many sites. However, the customer wanting this may be the greatest -)

I figured maybe I possibly could use UUID only for them, but which means creating exceptions everywhere so might as well only use them throughout (where neccessary) if I am going for their services.

Whether it makes any difference, I am using PostgreSQL.

PS, this discussion might be highly relevant to this situation. I simply keep wondering if there's one other way thinking about I've got a nice gauranteed unique SiteKey.

Most replication situations require using a GUID/UUID. I'd add someone to every table. Educate yourself concerning the performance implications of utilizing one and just how to best prevent them inside your particular database after sales.

I have pretty much arrived at the final outcome that IMHO, with this scenario, using UUIDs is a little of the "fast solutionInch, possibly even a little of the hack. I have made the decision that for me personally, within this situation, utilizing a composite secret is cleaner. Utilizing a UUID, I might as well of used just preprended the SiteKey to every ID.