> This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common.

Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0]

> If you're suggesting mandatory additional has_address and has_customer_id fields, I would disagree. You'd be reinventing a database tool that already exists precisely for that purpose.

The way to do it without using a nullable column is to introduce another table for the 'optional' data, and use a left outer join.

[0] https://en.wikipedia.org/wiki/First_normal_form#Christopher_...

▲

crazygringo 8 hours ago | parent [-]

> The way to do it without using a nullable column

I mean, you could, but having separate tables for every optional field would be an organizational and usability nightmare. Queries would be longer and slower for no good reason. Not to mention a gigantic waste of space with all those repeated primary keys and their indexes.

And you could have databases that prohibited NULL values, but we mostly don't, because they're so useful.

	▲	naasking 2 hours ago \| parent [-]
		> but having separate tables for every optional field would be an organizational and usability nightmare I think this indicates that declaring and managing state is too onerous in SQL.