Nulls Nullified (2005) (opens in new tab)

A simple fix would be to add a dialect option that makes any operation on a potentially null value a syntax error. This is possible because in SQL it's almost always possible to determine if a value X could potentially be NULL or not. If it could be NULL, something like (X == y) or (X + y) should generate a syntax error. Instead you should have to write (X is not null and X == y) or ISNULL(X + y, 0).

What doesn't make sense is to treat a nullable type the same as non-nullable type causing errors everywhere (Java), or make it so special that it basically has completely separate logic and operators attached (SQL)

The problem is the way it's handled in many programming languages, where it can blend so easily with legitimate value and blow the whole program off at execution time.

It's a practical problem, not a theoretical one.

And it's not the same as union types. The division of two numbers always returns a number, and nothing else, it's just that, for some values, it's not defined.

We can all agree that the "silently accept null" approach is, by now, a quadrillion dollar mistake.

But having a safe "or null" union type as the _only_ union type in your language, isn't as far fetched as you make it sound.

It's the good old "allow zero, one or an infinite amount of any feature" rule. Sometimes "one" is the right choice.

And the same principle applies to values. A plain value is one value. A list/collection is an arbitrary number of values. And `null` is no value. Some would, rightly, say that `void` is no value, so `null` it's really a value representing no value. And then you will also want to represent zero-or-one value, which is where the -or-null type comes in.

This is a more fundamental union than just "a Foo or a Bar" which is always a value, then we're just arguing over type.

You can always use an `Option` class instead. If you have classes. But if you're going to use `Option.none` to represent the value of an uninitialized variable, you're going to build it into the language anyway, and then you might as well admit to that one union type.

Nulls are a special case though - even if null was not allowed in base tables, you would still need them in outer joins.

(actual question) how does 5NF apply here?

I thought that this case could be handled null-free by introducing a separate table

people_middlenames (human_id PK, middlename NOT NULL);

Is it already 5NF? I thought 5NF requires more complex structure of data, like explained in Wikipedia, for example.

Thanks,

You need something to represent lack of a value on LEFT JOINs. The author acknowledges as much, the two things he seems to be complaining about are:

1. It's impossible to distinguish between true lack of value from LEFT/RIGHT JOIN and a value that was directly set as NULL.

- Interestingly, this is exactly why JavaScript has both null and undefined, a design decision that many people also complain about and grumble that it should have been unified into one.

2. There's no reason for NULL to not equal NULL, which makes NULL a headache to handle and requires special IS NULL/IS NOT NULL operators to test for NULL-ness.

- Postgres 15 now addresses this with the DDL option UNIQUE NULLS NOT DISTINCT.

Finally, the author claims to have a solution that solves all these problems but sadly locked it behind a paywall on a website which is now defunct.

> Until recently, there was no logically correct, relational solution to missing data. We offer an outline of a possible such a solution in Practical Database Foundations paper #8, “The Final NULL in the Coffin,” which also summarizes the problems with NULLs.

http://www.dbdebunk.citymax.com/page/page/1396241.htm

> This website has been cancelled.

> Click here to go to the 5-minute website builder.

https://web.archive.org/web/20041209115415/http://www.dbdebu...

> ORDERING AND PRICING

> Delivery will be in PDF format. We strongly recommend to upgrade to the latest version of Acrobat reader and to have the following fonts installed: Verdana, Courier, Arial Narrow, Arial Black and Wingdings, so we don't have to embed these fonts and enlarge the files.

Representing unknown/non-existent without allowing an explicit NULL value is can be modelled by the property being another entity, you have a child table that lists the values of the property so where you would store a NULL otherwise there simply exists no row in that table. In SQL this means extra joins and in most (all?) SQL implementations this results in (sometimes significantly) lower performance. The big argument there is whether this points to a problem in the theory (if arguing that NULL should not be “stored”), in SQL as an implementation of relational database theory, or in the implementations of SQL…

[I'm aware my terminology is all over the place, as most people's is: when talking about relational theory rather than SQL as a speicific implementation of it I should use tuple not row, relation not table, etc., but getting that right only serves to confuse people (a great many of those with a more self-taught and/or field trained programming background background than one that involves any computer science study) who know only SQL and have thus far not needed to be aware of the theory or history].