Not sure what you mean.

If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL.

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

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.

▲

MaxBarraclough 11 hours ago | parent | next [-]

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

▲

SoftTalker 11 hours ago | parent | prev [-]

No null is fine if you don’t know or there’s literally no value. But don’t interpret a null phone number to mean the customer doesn’t have a phone number. You can’t infer anything from that, other than you don’t have it.

	▲	crazygringo 10 hours ago \| parent [-]
		I'm not sure I agree. If I have a column for the ID of the customer's current active subscription, and that column is NULL, it seems perfectly fine to interpret that the customer has no active subscription. That is a valid inference. You don't need a separate has_active_subscription field. On the other hand, your phone number example is just common sense. The database doesn't represent the external world. The database just knows the customer didn't provide a phone number.