Null vs Default Empty Value

[foxfox]

If you have a field, say email, which is optional for user, which one you prefer in defining the table's schema?

1. Null value
2. Default Empty value of ''

[phranque]

that's actually two separate questions:
- should the column allow NULL "values"? (which really means the value of the column is undefined.)
- should the default be NULL (if allowed) or empty?

it's really a matter of proper application rather than preference.
does it make sense for that column to have an undefined value? by default?

note that a NULL column will act differently in selects and sorts than an empty column.

[foxfox]

>> does it make sense for that column to have an undefined value? by default?

as my example above, you have a field to store user email address, but this is an optional field, so you would ...

1. define a field allow NULL value and put NULL value in it, or
2. do not allow NULL value, and put '' in it

which one is recommended?

>> note that a NULL column will act differently in selects and sorts than an empty column.

yes, this is what I want to know about

Thanks.

[phranque]

i can't answer the question for you and there is no universally correct answer.
does it make sense to be able to distinguish between a user who has not (yet) defined an email address and a user who has elected to keep it or change it to "blank" (empty)?
if there is no difference to you between undefined and empty, i would go with empty.
otherwise you will end up with a combination of undefined and empty values, which will have to be counted separately and recombined as needed.

[syber]

It's generally a good idea to avoid nulls if you can. Nulls often result in extra SQL code to account for them or to eliminate them. Most situations can be satisfied with a default.

[foxfox]

It's generally a good idea to avoid nulls if you can.

yes, so in which case, we should use NULL?

[phranque]

yes, so in which case, we should use NULL?

you should use NULL if and only if you need to distinguish between an empty value (which is not NULL) and an undefined value (which is NULL).
if you don't understand the difference you probably do not need to use NULL.

this means your column def should have "NOT NULL DEFAULT ''".

[foxfox]

In fact, I can think of some cases I should use NULL instead of empty value, e.g. the email field example above

1. If the email is unique, then empty value does not make sense
2. If the email is a FK of other table

What I concern is if it is possible to distingulish NULL value & empty value, but is it make sesne to have empty value,

e.g.

If I have a field which is FK of other table, say uid (UserID), this field is optional, I would prefer NULL instead of 0 since 0 UserID never exist in real world.

Any comments?

[syber]

If I have a field which is FK of other table, say uid (UserID), this field is optional, I would prefer NULL instead of 0 since 0 UserID never exist in real world.

An example of this would be an internal foreign key relationship. If in an employee table you have a field called reportsto which has the empid of the person that employee reports to, the president of the company would have a NULL for reportsto because he doesn't report to anyone else.

Another situation where you would need a NULL is for a date that hasn't happened yet, such as date_closed.