[kstrauser]

That's not right. "Null" means that the value is not known. Suppose you have a table for employees, and you want to record the last time they were paid. What do you put in that column for people who just started this morning? The alternatives are to use null, indicating that they haven't been, or to formulate a codebase-wide sentinel value like "0000-01-01" and then accounting for that in every single database operation everywhere.

Further suppose that you have an external function in your codebase to estimate how many paychecks you've paid to someone, but the author doesn't know about any "0000-01-01" conventions your office uses. Without that, you'd see that Joe New Guy has worked here about 2,020 years, so we've probably issued him about 48,000 checks. If only you'd used null, then that function would have calculated "today() - null", which in any sane language would raise a type exception and alert you to the problem.

Nulls are beautiful. They have meaning. Lots of people misuse them, but that doesn't mean they're not valid and useful.

[rileymat2]

> The alternatives are to use null, indicating that they haven't been, or to formulate a codebase-wide sentinel value like "0000-01-01" and then accounting for that in every single database operation everywhere.

Arguably, you might consider that it is a bad design. Perhaps there should be a paycheck table that you can sort by date, instead of a field in the employee table that is updated.

(Of course this is hypothetical, so it is hard to argue use cases)

But a paycheck table does help with the second example because now you don’t need to estimate number of paychecks either. Which will help with unpaid leave or sabbatical situations to give you an accurate number.

But this is what they are getting at with bad design. (Potentially) As far as normalization goes.

[kstrauser]

Oh, sure. Your proposal is a better plan for this specific situation.

I certainly won't argue that people come up with some bad designs! Heaven knows I've dealt with some of them. I just wanted to point out that there are situations where null is perfectly reasonable, even though there are plenty where it surely isn't.

[namibj]

But data protection might not allow you to keep this for longer than legally mandated, i.e., ~10 years. How do you handle this for long-time employees?

[oarabbus_]

this is 100% how I feel. It's mindboggling that some people here think NULL is "wrong" or should be avoided. Nulls are, as you said, beautiful.

[bena]

A quick question I use to demonstrate the usefulness of NULL is "What color is the elephant on my desk?"

That question doesn't have an answer because there is no elephant on my desk. It can't be represented by any color, the answer needs to indicate that there is no value.

[rileymat2]

The result may be null when asking the question, but the tables representing this reality do not need a null column.

(Specifically, not returning any rows when queried, which is different from null)

[oarabbus_]

Often in analytics you will create datasets which are an amalgamation of several upstream sources. For example, an Endangered Species database. You might have "wing length" and "number of eyes" as fields, and both of these need to be NULL for something like a sea urchin.

There's really no justification to avoid nulls as a principle.

[GauntletWizard]

Okay, but what if I `SELECT wood, lampshade, elephant_color FROM desks WHERE location = "office"`? How does it represent my mahogany office desk with a beach theme lampshade and no elephant on it, vs my teak bedroom writing desk with a pink elephant and white cream lampshade?

[rileymat2]

By normalizing into multiple tables.

It is hard to model without requirements but you probably need a table for type of desks, another for desk instances with location and another one to many linking table for items/colors.

Why? I like two lamps and no elephants. By normalizing, this new requirement is simple to represent compared to jamming more fields into the desk table.

Or if another person prefers penguins you don’t end up with penguin_color columns.

It is possible the one table design works, but the introduction of nulls is a smell that indicates maybe the design is a problem.

[kwhitefoot]

> That's not right. "Null" means that the value is not known.

According to my old SQL Server text book (written by the SQL Server team) NULL should be avoided because it as so many different interpretations (I think forty was mentioned).