>You advocated using a non-zero, non-null value as a foreign key. I asked you to defend that. If you don't want to, or can't, that's up to you. I am happy to let it go, probably better that way.
>
>Mike, I stated why in several posts. So either you're not reading everything, or worse...
>
>Databases are not intellectual playgrounds for developers - they are consumed by an ever-widening body of non-developers in ways that go beyond static reports. That's my issue with these questions of "is it better to store a 0 or a NULL"....the question is really rather trivial (and I'm disappointed that one other forum member didn't realize that), and it's missing a bigger issue. Databases are accessed in varying ways by varying levels of users, and it's not always T-SQL that's being used, at least not directly.
>
>No, that doesn't mean catering every database design just for end users, but it does mean building to requirements. And in this instance, there are many business requirements to report on/analyze metrics by unknown/unclassified/unused. I've already cited a few and don't feel the need to state them again.
>
>Tools like PowerPivot, QlikView and countless others have been surfacing over the last five years to empower end users building "mini" datamarts. When fact tables/transaction tables store nulls as FK values, these tools don't work particularly well (nor should they be expected to) in allowing end users to report on "unclassifieds, nulls, unused, irregulars, etc" in the same way they allow users to slice data by other business entity members. Database entries that programmers think of as "null" values can wind up having business context down the road (or even immediately). So storing these as NULLs in the database is myopic.
>
>I also mentioned multiple hierarchies - an issue that's coming up more and more frequently, with increased data acquisitions. These are usually not clean, and can have multiple levels of "unused", "unclassified", etc. The business community in the company might rely on these unclassifieds to resolve discrepancies or simply assure that numbers tie, for accounting requirements. What's the theme here again? ...the database must satisify the business requirements. The ramifications are someimes far more than developer embarrassment when someone learns that information can't be derived from a database.
>
>I am speaking from experience. And lots of it.
>
>This is also advocated by Ralph Kimball, one of the pioneers in data warehousing and dimensional modeling. Go read his book "Data Warehouse Toolkit - Dimensional Modeling", pages 47 and 48. Shall I tell him his ego is too big???
>
>And not that I felt compelled to - I searched 5 different database/data warehousing sites and found this topic discussed - with the recommendation of precisely what I recommended. Shall I tell them their egos are too big as well???
>
>I've said all I plan to say. It is a far better design practice to store an "unclassified" in every master file/business dimension, and use that as an FK in the transaction/fact tables. You can insult all you want, but this is the generally accepted practice - was this your way of expressing discontent in your last gig?
>
>So you can either go back and read what's been said by myself and Naomi, or you can just continue this path of hubris. If you have any hope of succeeding in this industry, you have got to pull your head up, stop getting so uptight about "who" the feedback is coming from, and start learning.

Thank you for the thorough reply. Now I get it. It still makes me uncomfortable to store a bogus value, but I can see there is a reason for doing it. I have had zero experience with data warehousing and didn't think of that at all.

It really wasn't personal. I would have had the same response to anyone else. What you said contradicted something I have always known.

BTW, I was succeeding in this industry when you were still in school. As this proves, though, there is always more to learn.