Jump to content

Primary key considerations for distributed databases


NotionCommotion

Recommended Posts

I have multiple databases distributed over individual distributed servers, and each server is maintained by different parties.  There is a single "master" or "parent" database which includes a database_child_unique_identifier column which is some UUID which uniquely identifies each child database (i.e. database_child1, database_child2, etc).  When a record is added to a child table, a corresponding record must be added to the master table.

An application exists on all the child servers which will allow a record to be inserted, and a REST API will be used to add a corresponding record to the master database.  The IDs will be up to 32 characters long, obviously must be unique if they are primary keys, and will be foreign keys of other tables in their corresponding databases.  The ID of this record is not immutable, and the application will also allow it to be changed and will use the REST API to update the master table (along with cascade update for the foreign keys).

Lastly, the ID has "meaning".  When adding a record to a child database, the user must know the ID is valid, and the master server will use this ID to obtain data from yet other distributed servers via a SOAP interface.  It is these other servers that ultimately define the value of the ID, they are unique in each, and are under the control of the user who maintains each child database.

 

Using a natural key meets all my needs, however, breaks many of the best practices of a primary key (not being immutable, has meaning, and some would argue is too long).

 

Alternatively, I can add a surrogate PK to each table and make the previous ID which has meaning UNIQUE, and pass both of them to the master server.  Other than being more "proper", however, I don't know what value this will bring.

 

Please provide recommendations.

database_master.table_parent
-id (PK)
-database_child_unique_identifier (PK)
-data

database_child1.table_child
-id (PK)
-data

database_child2.table_child
-id (PK)
-data

database_child3.table_child
-id (PK)
-data
...

database_childN.table_child
-id (PK)
-data
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.