Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!uio.no!ntnu.no!not-for-mail From: Jon Heggland Newsgroups: comp.databases.theory Subject: Re: What does this NULL mean? Date: Mon, 12 Dec 2005 13:06:18 +0100 Organization: IDI/NTNU Lines: 44 Message-ID: References: <1134263104.493494.275810@o13g2000cwo.googlegroups.com> <1134271728.159733.37470@g43g2000cwa.googlegroups.com> <11po9j4qormaleb@corp.supernews.com> NNTP-Posting-Host: coleburn.idi.ntnu.no Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit X-Trace: orkan.itea.ntnu.no 1134389174 3184 129.241.111.99 (12 Dec 2005 12:06:14 GMT) X-Complaints-To: usenet@itea.ntnu.no NNTP-Posting-Date: Mon, 12 Dec 2005 12:06:14 +0000 (UTC) User-Agent: MicroPlanet-Gravity/2.60.2060 Xref: dp-news.maxwell.syr.edu comp.databases.theory:35070 In article <11po9j4qormaleb@corp.supernews.com>, finarfinjge@hotmail.com says... > > An operator does what are called 'rounds'. They do certain things at a > certain time. Most of these are manual checks of various circuit > parameters. Take one of these as an example: The amount of activated carbon > per tank ( I like this one because there is no automated method for this > measurement at this time ). They then write this number down. On occasion, > it is not possible to get the value. They are usually busy somewhere else. > This information (the fact that the operator missed a round) is indicated > by the blank cell in the hand written report sheet. I would interpret this > as a NULL value. How can I capture the same data (the missing information > IS the data) in a database? There are two kinds of things you want to capture: What the reading of the amount of activated carbon was in round X; and that round Y was missed. Those are two rather different kinds of fact---even though they both involve rounds. You *can* put both in the same table by using NULLs, but you have to be careful when you name and use this table. E.g. if you call it ROUNDS, it is easy to think that the number of rows equals the number of rounds made, but that is of course not the case---it equals the number of rounds that have /or should have/ been made. If you naively ask SQL for all distinct carbon amount measurements, it will include one NULL--- which is not a measurement. For every query, you must consider the impact of the NULLs. It is complicated, unintuitive and error-prone, and requires more documentation. If you instead use two tables, say MEASUREMENTS and MISSED_ROUNDS, the interpretation and use of the tables is very clear and straightforward, and you can still produce the empty fields for paper/screen reports. > Just to be clear, the empty cell in the > completed operator's report sheet IS USEFULL DATA. It isn't 0, it isn't > unknown, it is missing data. The fact that >>the data is missing<< is the > data I need to capture. The empty space on the report is a nice way of *presenting* the data to a human user (or for accepting input from one). That does not mean it is the best way of *representing* it in the database for further computation. -- Jon