473,725 Members | 1,816 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

NULL vs. "Unknown Value"

I'm running into a situation that has me adding a value of "Unknown" to
a reference table. I am being pulled between two trains of thought, and
was curious to get other's input on in. I give an example below.

1-) Adding "Unknown" to a reference table is bad. Doing so effectively
changes the Nullability option of every FK that references the table to
a NULLable FK relation.

2-) Simply adding a "Not Known/Undetermined" value to the reference
table greatly simplifies things. No schema changes are required, and
programs that use the reference table to populate their drop-downs will
automatically see the new value.

Perhaps both approaches are good, but it would all depend of the
context, the criticality of other FKs that reference the table, how/when
the data is being used?

=============== =============== =============== =
EXAMPLE
=============== =============== =============== =

Assume two tables. Employee & EyeColor, as described below.

+============== =============== ======+
|Employee |
+----------------+------------------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NOT NULL |
+----------------+------------------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
+============== ==+============ ======+

+============== =============== =============== ========+
|EyeColor |
+---------------+------------------+-----------------+
|EyeColorId(PK) | EnglishName(AK1 ) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+---------------+------------------+-----------------+
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+============== =+============= =====+========= ========+

And let's say that an automated process is being built to import Eye
Colors from central database. In this process EyeColor may no longer be
available.

With solution #1, new (or existing) data is changed as follows:
+----------------+------------------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NULL |
+----------------+------------------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
|newemp | NULL |
+============== ==+============ ======+

+============== =============== =============== ========+
|EyeColor |
+---------------+------------------+-----------------+
|EyeColorId(PK) | EnglishName(AK1 ) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+---------------+------------------+-----------------+
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+============== =+============= =====+========= ========+

With solution #2, new (or existing) data is changed as follows:
+----------------+------------------+
|EmployeeId(PK) | EyeColorId (FK) |
|NOT NULL | NULL |
+----------------+------------------+
|marc | 1 |
|dan | 2 |
|sonya | 1 |
|newemp | 0 |
+============== ==+============ ======+

+============== =============== =============== ========+
|EyeColor |
+---------------+------------------+-----------------+
|EyeColorId(PK) | EnglishName(AK1 ) | FrenchName(AK2) |
|NOT NULL | NOT NULL | NOT NULL |
+---------------+------------------+-----------------+
| 0 | Unknown | Inconnu |
| 1 | Brown | Brun |
| 2 | Bloodshot | Rouge |
| 3 | Blue | Bleue |
+============== =+============= =====+========= ========+
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
3 3379
Marcus wrote:
I'm running into a situation that has me adding a value of "Unknown" to
a reference table. I am being pulled between two trains of thought, and
was curious to get other's input on in. I give an example below.

1-) Adding "Unknown" to a reference table is bad. Doing so effectively
changes the Nullability option of every FK that references the table to
a NULLable FK relation.

You could fix this with check constraints. A bigger issue is that null
is intended to mean unknown (although it gets used for absent, as well).
By using a real value for unknown, three-valued logic will only come up
when it's inappropriate (as I would say absent really is a value, and
should be treated as such in comparisons).

If the distinction between absent and unknown is important for your
application, I would leave the unknowns as null, and put a row into
the reference table for absent. This is cleanest with a identity
keys, because checking for a magic value that means absent will be
inevitable, and either 0 or -1 is a fairly obvious choice that can
be the same for all tables.

(To head off a repeat discussion, we'll acknowledge that some people --
Joe Celko foremost among them -- consider identity keys evil, as they
are sort-of reminiscent of file pointers. I consider meaningful keys
to be evil, as they violate the spirit of the 1NF and 2NF rules.
I don't think anyone is likely to be convinced here.)

Bill

Jul 20 '05 #2
William Cleveland wrote:
Marcus wrote:
I'm running into a situation that has me adding a value of "Unknown" to
a reference table. I am being pulled between two trains of thought, and
was curious to get other's input on in. I give an example below.

1-) Adding "Unknown" to a reference table is bad. Doing so effectively
changes the Nullability option of every FK that references the table to
a NULLable FK relation.

You could fix this with check constraints. A bigger issue is that null
is intended to mean unknown (although it gets used for absent, as well).
By using a real value for unknown, three-valued logic will only come up
when it's inappropriate (as I would say absent really is a value, and
should be treated as such in comparisons).

If the distinction between absent and unknown is important for your
application, I would leave the unknowns as null, and put a row into
the reference table for absent. This is cleanest with a identity
keys, because checking for a magic value that means absent will be
inevitable, and either 0 or -1 is a fairly obvious choice that can
be the same for all tables.

(To head off a repeat discussion, we'll acknowledge that some people --
Joe Celko foremost among them -- consider identity keys evil, as they
are sort-of reminiscent of file pointers. I consider meaningful keys
to be evil, as they violate the spirit of the 1NF and 2NF rules.
I don't think anyone is likely to be convinced here.)

Bill


One thing to consider is that when using joins, imagine a database with
two tables, Patients and Blood, if blood type is unknown and you use a
query to match up patients with bags of blood by blood type then the
results can be deadly. Extreme example I know but food for thought.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Jul 20 '05 #3
>> I'm running into a situation that has me adding a value of
"Unknown" to a reference table. <<

Did you mean "Referenced " table?
1-) Adding "Unknown" to a reference table is bad. <<
No it is not. Get a copy of DATA & DATABASES and read the chapters on
missing values and designing encoding schemes.

A good encoding scheme can have several kinds of missing values.
Example; ICD disease codes use 000.000 for "undiagnose d, just
admitted" and 999.999 for "admited, diagnosed and we don't know what
this crud is". Likewise a good survey form DB has {'yes', 'no', 'not
answered', 'N/A'}, the ISO sex codes are {0 =unknown, 1 =male, 2
=female, 9 =lawful person such as corporations, etc. }

Even Dr. Codd had two kinds of NULLs in the second version of the
Relational model (missing value and missing attribute). SPARC listed
over 20 kinds of missing values in a 1975 paper.
Perhaps both approaches are good, but it would all depend of the

context, .. <<

Bingo! The NULL is portable and it has particular behavior. I use
NULLs in (start_time, finish_time) duration pairs as an "eternity"
symbol because I can say "COALESCE (finish_time, CURRENT_TIMESTA MP)"
to get a truly current report. But time is a continuum and encodings
are discrete sets of values.

I'd probably make 'Unknown' the DEFAULT to take advantage of the DRI
actions, too.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3237
by: Denzil | last post by:
Hi, I am retrieving my DB values and setting them in the MyRS::DoFieldExchange(CFieldExchange* pFX) function. One of these records is a Long datatype and could be "Null" in the DB table. The issue is when the DB value is retrieved it is fine for all integers but for a "Null" value it returns a junk long value and not a pre-defined value, say.
8
2033
by: Calan | last post by:
I have a server-side ASP script that dynamically creates an input form from a database table. The table contains a field name, the table where values are stored, type of input control, value for a label, etc. What I need to do is create a JS validation routine that will check each control for valid input, regardless of what the control name is. If it is a "select", it needs to verify the index is > 1. If it is an "input", it needs to...
1
7706
by: JBrow | last post by:
Hi everyone, I have a form with several fields. One represents the primary key. If you leave it blank and try to save the record, the forms displays the following error message - "Index or Primary Key cannot contain a null value". I understand why it is doing this, but I'm wondering if I can program a friendlier message. Any help would be great. Many Thanks!
0
2267
by: Dana | last post by:
I am using the XMLTextWriter to build an XML string and pass it to the XMLDocument. When I get the data from SQL Server, some of the values passed to the XML are NULL in the database. When I try and run an update to database using the same XML string, (using SQL parameters to pass the selectsinglenode), the XML always shows the NULL values as an empty string "". This is then updating the database with an empty value rather than keeping...
3
1614
by: Karunakararao | last post by:
Hi all Presently i am sending data to database filed like this "EquipmentFilterDevPrimaryId = 0" i need Instead of "0" (NULL)i need store the data null value how can i pass this null value pls check that
12
13909
by: Emi Lu | last post by:
Hello all, I have a question about "date" & "timestamp" types in PostgreSQL. I want to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for them. However, it seems that PostgreSQL does not support it. Could someone helps me please? The example table: T1 (col1 varchar(7) not null,
9
10738
by: Klaus Johannes Rusch | last post by:
IE7 returns "unknown" instead of "undefined" when querying the type of an unknown property of an object, for example document.write(typeof window.missingproperty); Has "unknown" been defined as a valid return value for the typeof operator in a later version of ECMAScript or is this a JScript "feature"? -- Klaus Johannes Rusch
7
38016
by: John | last post by:
Hi Everyone, I'm having this extremely annoying problem with Internet Explorer 6, giving me an error message saying "unknown runtime error" whenever I try to alter the contents of a <divelement using innerHTML. Now, I've researched this problem on the web, and found many references to it, but none of them quite addressed my specific situation, and since my experience with JavaScript is limited, I was not able to adapt the solutions I...
4
2905
by: justice750 | last post by:
Hi All, I am using a FormView control. The allows me to update records in the database. However, when a database field is null I can not update the field on the form. It works fine when the field is not a null value. I am not using any code behind (C#) to bind the data or manipulate the data. I have read that when there is a null value in the database that there is no record in the "dataset". Can anyone show me how to bind a value in the...
0
8886
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8751
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9401
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9257
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6010
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4781
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3218
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2632
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2155
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.