Being Delusional About Replacing Identity With Self Generated Id

SLQ Server 2005

Hi Guys,

Well, the big re-write of our app in Russia is an official failure and it has come back to us (after 2+ years at $20K per month). I suggested we get the data model straightened out before we do anything else.

Here's a notion that I'm expecting to have to stomp, but I thought I'd run it by you smart guys first.

Basic structure will be Admin DB as a hub and each customer (because they all request this anyway for 'security') will have a separate DB for their Disability/Leave data.

As an additional layer of 'security' it was requested that, say, the unique id of a person would be unique across all client databases. Hence if the interface queries medical info on Person 123 for Customer B there is no possiblity of accidentally returning Person 123 from Customer H, because 123 only exists as a primary key in the Person table in Customer B's database. Each available key number is used uniquely across the admin & customer databases. (As you may guess, we've had several instances of company a's data going to company b.)

We're new to the idea of splitting out customers into separate DB's but our plan is to keep the customer DB's identical in every aspect except data.

Is there a functional way to ensure a key's uniqueness across our whole structure (Admin DB as Hub and multiple client db's)? Are we nuts to want this?

Note I did look at some info on 'creating your own identity' key thinking that maybe we could keep an 'identity' in a table in the Admin DB for each client table that needs this feature. But it sounds like I'll end up with issues for sure when bulk inserts are run unless the insert is run on the table in the Admin DB.

I can't imagine anyone is going to try to answer this, but it made me feel better to type it all out here!