Re: quick FK question

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Wed, 23 Dec 2009 08:55:13 -0800 (PST)
Message-ID: <733530.38055.qm_at_web113204.mail.gq1.yahoo.com>

In a warehouse though, let's say you have a missing value in a fact table, and you know that (say based on some business rule) it will come later, then that really isn't a NULL value, it is defined as something that will arrive later. In that case, I'd argue that the column value is not valid if it's a NULL and that rather it should have some value that represents that it's "on it's way" but not here yet. All of this can be properly documented in associated metadata/documentation.

Just a thought, YMMV....

RF

 Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it! Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON! OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex) Oracle Database 11g New Features (Oracle Press) Oracle Database 10g New Features (Oracle Press) Other various titles
Blog: http://robertgfreeman.blogspot.com Check out my new blog series on installing Oracle Database 11gR2 on Windows using VMWare!

---

From: Yong Huang <yong321_at_yahoo.com>
To: Gints Plivna <gints.plivna_at_gmail.com> Cc: oracle-l_at_freelists.org
Sent: Wed, December 23, 2009 8:30:58 AM
Subject: Re: quick FK question

> As soon as fake value becomes normal business value problems might
> arise. I'd personally better deall with NULLs because this is
> WIDELY understand concept both by developers and DBMSes compared
> to some specific value representing NULL, which is -1 for project1,
> 0 for project2 and 187913267532 for project3.

I agree. When I do data modeling, I personally never use a fake value for a simple missing value (as for reason of avoiding outer joins). But if multiple special values are needed in the design, and the developers agree, I could use e.g. -1 for generally unknown, -2 for lost, -3 for ... In this case, a single NULL couldn't satisfy the business need, and an additional column for these special values would be a little too wasteful.

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 23 2009 - 10:55:13 CST