I'm working on a project with client that needs to rely on a proprietary data set. They also have custom data that logically fits alongside the proprietary data. What they do is use the same tables but with negative ids. So an Example would be
Fields: animal_id, name, can_purr
So the organization that provides the data provides Animals
1, Cat, yes 2, Dog, no 3, Mouse, no
And then my client is adding their Frankenmals
-1, Werecat, yes -2, Werewolf, no -3, Jackalope, no
So that other FKs of Animal can be reused by Werecat and Jackalope. Every so often, a new animal is recognized by the data maintainer and they add, or something evolves into a new thing so the original keys are important to maintain. The frankenanimal industry all uses this database and can understand me if I talk about Animal 1, but not if I have turned Animal 1 into something other than a Cat that can purr. There is no relationship implied or maintained between -2 and 2.
The negative numbers may be the best way to do it. It makes my skin crawl because then the ID represents something other than just record identification.
Many other systems are changing at this time. So if this isn't a great design, now's the time to change it.
Is there anything so wrong with the negative numbers approach? The main risk I see is if the data maintainer decides to use negative identifiers for their own purposes? An automated refresh could cause havok. But the automation could check for that condition, so, not so bad?
I just can't find much in the way of best practice advice on this. So have you done the negative numbers approach and regretted it? Why? Or have you transformed to a new schema and then regretted that for maintenance or some other reason?
My apologies if this is too subjective. Just not sure how to discover if I'm just being illogical or if the double purpose on the record ID is bad enough to merit a redesign.