Comment by crazygringo

Comment by crazygringo 5 hours ago

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

Reply View 1 reply

crazygringo 4 hours ago

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

Reply View | 0 replies

SoftTalker 5 hours ago

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.

Reply View 1 reply

crazygringo 4 hours ago

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.

Reply View | 0 replies