RE: The age-old database design question - Integers or GUID/UUID for primary keys ? What are your thoughts ?

@UUID:
The maria paper you mentioned says: "The probability of a collision is almost none."
A few years ago DB2/400 stated: "UUIDs are most likely unique"
The reason for this is, that a part of the UUID depends on timestamp. This could be solved by a function (provided by the datatbase, mentioned from Birgitta; or by a UDF) or has to be solved in the application layer in the insert process. Uniqueness is guaranteed anyway by the primary key.

@merging data:
This is done for UUIDs by a part of the key is dependend on the box it is generated from. Same could be done for the number keys: just add another key column for the customer or the box the record is inserted from.

@your indexes:
The ID is not needed as a part of your CustNumber_Key and CustName_Key. The ID is only needed for the elementary join operations and normaly ensured by your referential constraints

@how to decide:
It depends on your needs! For heavy transactional workload of the insert and delete (!!!) processes, I would do extensive workload tests first. In a datawarehouse project (it was the biggest real world database on as400 in germany of those days) the decision was to use bigint keys, generated in the application layer (blocking in the process outperformed autoincrement numbers - maybe dependend on the current implementation).

@some thoughts:
- index size is very important!
-- indexes could get bigger than the tables
-- delete performance of reorg processes depends on index size
- bulk operations could be speeded up by parallelism and are influenced by your index decision

@my preference:
Bigint keys optionally extended by a key for the source-system

D*B