Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.comcast.com!news.comcast.com.POSTED!not-for-mail NNTP-Posting-Date: Wed, 08 Aug 2007 05:58:41 -0500 From: "sinister" Newsgroups: comp.databases.theory Subject: Practical considerations of dealing with two meanings of NULLs Date: Wed, 8 Aug 2007 06:58:35 -0400 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 Message-ID: Lines: 24 X-Usenet-Provider: http://www.giganews.com NNTP-Posting-Host: 69.251.227.152 X-Trace: sv3-HyIkVST5PARU/9grQ8/SyuybvSUyCESlo2Er9pa8rrzrB7XEeTMBnVL33CwxjixRRN51XLRDFaU1EpR!SWUdzUA3uFAAR/oKXaz6wzMdVcnjAJNGMevzNMCydpHG5Oh4q3tUCVUqz+wnTb6O15cmcpySEpD7!vKH/imqiw9bU7CHPSHFhGvmFB02G28I= X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.35 Bytes: 2345 Xref: usenetserver.com comp.databases.theory:166136 X-Received-Date: Wed, 08 Aug 2007 10:44:40 EDT (text.usenetserver.com) One simplistic method of trying to distinguish multiple meanings possibly associated with NULL is to associate particular non-NULL meanings with those values. For example, I might want to store both "unknown" and "known, but not yet filled in by data entry clerk." I could associate NULL to one and a particular non-NULL value to the other, or non-NULL values to both. If the data at issue are e.g. strings or something categorical, this is OK. (The only problem I can think of is the case where (in the interface---I'm using a webserver for that) I give users a selectbox of predetermined choices, and there's an associated textbox where they can put in "Other" (non-predetermined values)---what's to stop them from overlooking the predetermined choice "Unknown" and typing in "don't know" in the textbox?) But if the data are numerical, it's messy. It's easy if the data are known to be nonnegative, because then one can reserve particular nonnegative values for this (e.g. "-1 means not filled in by user," "-2 means known"). But this method just seems ugly and kludgey to me. Is there any clean method of dealing with this?