integer primary key for replication

Question

I am weighing my options for integer primary keys that can be used in multi master replication. (I'm pretty much sold on using integer keys instead of GUIDs)

The best I can come up with is having the most significant data first and having the server number last: eg. invoice 1 on server 1 = 101 invoice 1 on server 2 = 102 where the non serverno part (invoiceno) comes from a db number generator

algorithmically: gen_id(INVOICENO_GEN, 1) * 100 + serverno and you can get the server number by both looking at the value and mathematically.

Which leaves room for 99 servers while still being short and readable and won't collide. Using that scheme and normal integer size column would make the max rows (21 474 836) or if bigint is used many billions.

for instance the invoice table keys would look like this:

Server 1    
101
201
301
401

Server 2    
102
202
302
402

So my question is: any critiques or flaws I have overlooked?

The database is Firebird.

Answer 1

How about just creating a composite primary key?

Define a "ServerID" to set on each server, e.g. 1, 2, 3, 4 etc. as an INT. Then have the "InvoiceID" as an INT on your Invoice table.

Create the primary key to be (ServerID,InvoiceID).

That way, you have room for way more than 99 servers, and you don't have to manipulate / calculate any ID's or anything like that.

Of course, any table referencing your Invoice table now needs to use (ServerID,InvoiceID) as the foreign key, too - but I guess having the ServerID everywhere in your tables isn't going to be a problem if you need to replicate those tables, too.

Marc

Answer 2

In general, don't use numeric types for anything that isn't a number. Treating the digits with special significance makes your numbers no longer strictly numeric. A string is usually more suited to scenarios like this where you want to add some environment-specific data (usually for replication).