Also, the use of NULL is almost always for the wrong reasons. It is my
own personal belief (that others share) that a database is not
completely relational.

What I mean is, borrowing from an example someone (maybe here?) gave to
me... Let's say you have a People table. You want to keep misc facts
about certain people in there, such as name, address, and birthday. You
also may want to have a deathday, but you'll define birthday as being
NOT NULL, but deathday can be null.

The reason for this is deathday shouldn't REALLY be in the people table,
if you want to follow good relational rules. There should be a 1-to-1
relationship table out there somewhere named something like Deaths and
relate back to the People table. No entry means you aren't dead. When
doing a query like:

Select People.Person_ID, People.Birthday, People.Deathday
From People left join Deaths on People.Person_ID = Deaths.Person_Ref

.. it will return NULL for deathday for everyone who isn't dead. Simply
meaning, rather than making a whole seperate tables for deaths, you can
take a shortcut and put deathday into the People table, and allowing for
NULLs.

So my rule is, if you can seperate it out logically like that, then it
can be a good place to have it NULLable.

Jason

Helen Borrie wrote:

> At 07:57 PM 21/08/2005 +1000, you wrote:
>
>>women_lover_best wrote:
>>
>>>When should we use allow nulls..what null signifies?
>
>
> Null is not a value, it is a state. It signifies that the value is unknown.
>
>
>>If a column is
>>
>>>empty say example phonenumber of a customer..it means we dont have
>>>customers number..so we allow null..for such a column since it is not
>>>required..and when I access in my app null means we dont have customers
>>>phone number..is this the right interpretation?
>
>
> Yes, it is the right interpretation. Making the column nullable allows the
> possibility that some time the customer's phone number may be known.
>
>
>
>>This isn't an answer. It is a supplementary question.
>
>
> It's a thread hijack, actually.
>
>
>
>>If the table for phone numbers had a foreign key which linked it to the
>>customer table then would no phone number for a customer being
>>represented by an absence of a phone number record completely remove the
>>need for a decision to use nulls.
>
>
> You don't *decide* to use nulls. Every column is nullable by default
> unless you make it otherwise, by adding the NOT NULL attribute in the
> column's definition. Adding NOT NULL makes the column a "required
> field". Whether the table had a foreign key or not makes no difference -
> except in the unlikely event that the foreign key were the phone number, of
> course!
>
> If you decide to make a column the primary key, or an element of it, you
> don't have a choice. A primary key element has to be explicitly attributed
> NOT NULL.
>
> I don't know whether this answers the question or not, as I can't quite
> work out what the question means. Generally, if you don't have a reason to
> require a column to hold a value, then you don't have a reason to constrain
> it as NOT NULL. The thread-owner's example was a good one for
> demonstrating the value of keeping a column nullable. Not everyone has a
> phone number...
>
> ./heLen
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>