lucidspoon (4/12/2011)
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.
If some of your things have integer keys, some have varchar(30) keys, some have nchar(6) keys, some have numerid(14,5) keys, some have date keys, some have datetime keys, and some have datetime2 keys, and some have binary(16) keys you can't use -1 as the "unreal" value for all of them; so rather than copying and pasting code that says something like "if @linked_thing = -1 return" everywhere you need to check for that case you have to pick the right version of "unreal" for the thing you are looking at. But as you say all your keys are integers that's not going to be a problem.
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".
If you already have inactive markers of end dates then you can use them to avoid most of the problem with introducing a row to represent "unknown"; but there may still be a problem if you ever do any reports on inactive or expired entries, or do tidy ups that clear out expired/inactive stuff. It's probably not going to be a big problem though.
Tom