I want to create unique identifiers for my employee's in my databases, this is to avoid duplication across the databases i.e. if employee Barbra is stored in DB1, I don't want her to be created again in DB2.
My initial thought was to use a hash (e.g. SHA-1) create from the employee telephone number. That way the chance of collision is extremely low and values will be unique.
However I'm aware having such a long UID will cost additional resources for performing queries, such as SELECT, on the UID's due to the size of each UID, lack of sorted structure and datatype (VARCHAR).
Does anyone have any ideas as to how to solve this problem?
sha1 from birthday is a bad idea, people have same birthdays, you can get clashes regardless of potential collisions within the hashing algorithm itself. The range of input is limited (birthdays).
There are several options:
each database can implement auto_increment_offset
each database could implement compound primary key made of (id, db_id) where db_id is a numeric unique identifier of the database's location.
you can use UUID, it appears that it's costly but it's too early to assume anything without the data set size and hardware that this is ran at. UUID quickly solves your problem. You can easily store an UUID into a binary(16) column which gives you fixed width and low space footprint (example: select unhex(replace(uuid(), '-', '')).