Tom.Thomson (4/12/2011)
Picking a value of -1 will work if you can guarantee that (a) -1 will never be a real key and (b) your keys are integers.
All of our primary keys are integers (or smallint), and every one of them is an indentity field to auto-increment to start at 1, so I don't see how -1 would ever be a key any other way.
Tom.Thomson (4/12/2011)
The other problem is that if you have a lot of cases where you need to distinguish leaves from non-leaves and the keys in these various cases you have to pick a lot of distinct root markers - one for each key type, and that can be a pain because you now can't use the same code to detect leaves.
I'm not sure if I understand what you're saying. I hope that means we aren't doing anything like that, but I'm curious what you mean.
Tom.Thomson (4/12/2011)
Having a row in the "lookup table" that represents unknown is possible, but I would hate it: I would probably be programming around its presence all the time, because what you calll the look-up table is generally the most important table, the one which gives the high level view of something.
Currently pretty much all of the lookup tables have a "Active" field, so there is already code when referencing those tables to make sure that the values should be displayed. Going forward, everything is being date tracked, so I was planning on having the -1 value in each table be end dated so that it would be treated the same as a value that has been "deactivated".