Comment by MaxBarraclough
Comment by MaxBarraclough 7 hours ago
> 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_...
> 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.