Uniqueness across databases/data centers - How to manage keys?

When you are designing a database, most people don't think about a need that may arise to synchronize data across databases or data centers - to their support, they don't have to in most cases. However, with cloud computing gaining mainstream momentum and importance of geographic load balancing, more and more are finding a need to migrate to such need. I would recommend to start off your initial design with such in your thoughts.

There are different techniques and models that are common. Every need is different and level of complexity is different. Let's look at basic techniques that could satisfy most common needs and evaluate what advantages/disadvantages they bring.

Universally Unique Identifiers
The most popular and first one to come to your thoughts. The intent of UUID is to support such needs and can be trusted reasonably well. However, when you put to a practical perspective there may not be very many advantages to use them as keys, be it surrogate key. Why?

• If you plan on sorting with your key, may be not a good option
• You may not prefer alpha-numeric keys - for performance reasons you may prefer numeric
• There is no real good way to identify where the id was generated (like what data center)

Compound keys
While this is certainly a good idea, having your identity combination of unique number and say an identifier to database, it's probably not a great idea to have compound keys as your primary key.

Spaced (or offset) numbers
Most databases now support very long numeric data types and you can design to space your key values really large with a projected growth. This allows you to keep your keys numeric data type and having sort capabilities and also identifying the origin.

Pre-allocated offset series
In this model you would reserve offset series for each of your database, just like the previous case. The difference is you could probably have same length of series on all databases (upto 9) with initial digit indicating the database.

While there are many other ways and models, it is always a good idea to start planning with these thoughts in your mind when you are designing.

Think through...