6 ms·

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

rplnt 1y ago
Interesting, I don't think I've seen that while NULLs are very common. I guess you would handle it in the application and not in the query, right?
- SoftTalker 1y ago
  I've seen it too, very often. But it's good if you can just keep NULL meaning NULL (i.e. "the absence of any value"), because otherwise you will eventually be surprised by behavior.
crazygringo 1y 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 1y 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_J._Date's_definition_of_1NF https://en.wikipedia.org/wiki/First_normal_form#Christopher_...
  - crazygringo 1y 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.
    - naasking 1y ago
      > 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.
      - crazygringo 1y ago
        Or maybe NULLs are actually a great solution here, and it's fine. The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It's a fundamentally overcomplicated idea. It's like a programming language where every variable that could be null had to be put in its own file. It would be terrible design.
        solumunus 1y ago
        Over 90% of my optionals have their own tables and it’s the cleanest and most maintainable database I’ve ever worked with. I will always design databases this way going forward. That’s my experience. I remember working on ERP systems with 40+ column tables, most of which were null. With no clear constraints on which options should or shouldn’t enable or make mandatory other options. This becomes incredibly obvious and natural when you group in additional tables. My tables are incredibly concise and the cache loves this.
        crazygringo 1y ago
        I don't know -- the 40+ columns feels simple and maintainable. Having a bunch of grouped optional values in other tables adds a ton of maintenance and query complexity. The "clear constraints" belong in business logic, whether in triggers at the database level or before queries get executed at the application level. Now, splitting up tables can produce performance optimizations. I'm not saying to never do it. But it's a tradeoff that increases complexity.
- SoftTalker 1y 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.
  - crazygringo 1y 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.
Breza 1y ago
I've found it's easier just to keep things NULL in many cases. Sure, if you have a student database with the column favorite_sport, maybe it makes sense to use the value "Not a sports fan." But think about an email list with the column unsubscribe_date. For active recipients, you'll want that field to be NULL.

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

rplnt 1y ago

Interesting, I don't think I've seen that while NULLs are very common. I guess you would handle it in the application and not in the query, right?

SoftTalker 1y ago
I've seen it too, very often. But it's good if you can just keep NULL meaning NULL (i.e. "the absence of any value"), because otherwise you will eventually be surprised by behavior.

crazygringo 1y 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 1y 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_J._Date's_definition_of_1NF https://en.wikipedia.org/wiki/First_normal_form#Christopher_...
- crazygringo 1y 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.
  - naasking 1y ago
    > 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.
    - crazygringo 1y ago
      Or maybe NULLs are actually a great solution here, and it's fine. The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It's a fundamentally overcomplicated idea. It's like a programming language where every variable that could be null had to be put in its own file. It would be terrible design.
      - solumunus 1y ago
        Over 90% of my optionals have their own tables and it’s the cleanest and most maintainable database I’ve ever worked with. I will always design databases this way going forward. That’s my experience. I remember working on ERP systems with 40+ column tables, most of which were null. With no clear constraints on which options should or shouldn’t enable or make mandatory other options. This becomes incredibly obvious and natural when you group in additional tables. My tables are incredibly concise and the cache loves this.
        crazygringo 1y ago
        I don't know -- the 40+ columns feels simple and maintainable. Having a bunch of grouped optional values in other tables adds a ton of maintenance and query complexity. The "clear constraints" belong in business logic, whether in triggers at the database level or before queries get executed at the application level. Now, splitting up tables can produce performance optimizations. I'm not saying to never do it. But it's a tradeoff that increases complexity.
SoftTalker 1y 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.
- crazygringo 1y 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.

Breza 1y ago

I've found it's easier just to keep things NULL in many cases. Sure, if you have a student database with the column favorite_sport, maybe it makes sense to use the value "Not a sports fan." But think about an email list with the column unsubscribe_date. For active recipients, you'll want that field to be NULL.