undefined | Better HN

0 pointskstrauser6y ago0 comments

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.

0 comments

7 comments · 3 top-level

rileymat26y ago· 2 in thread

> 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.

kstrauserOP6y ago

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.

namibj6y ago

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_6y ago· 2 in thread

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.

bena6y ago

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.

rileymat26y ago

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)

2 more replies

kwhitefoot6y ago

> 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).

j / k navigate · click thread line to collapse