4 ms·

> 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, bu

> 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.
      - magicalhippo 1y ago
        > I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen would require 100-150 joins using a separate table per optional. I'm pretty sure the database would not love that.
        naasking 1y ago
        400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : int not null) table Entity_TextFields (ParentEntityId : int not null, FieldId : int not null, Value : varchar(MAX) not null) table Entity_DateFields (ParentEntityId : int not null, FieldId : int not null, Value : datetime not null)
- MaxBarraclough 1y ago
  Right, Date's idea didn't catch on. I'm not aware of any industry-strength RDBMS prohibiting storing null. Agreed that queries would tend be longer as you'd need joins, although views could help, especially for read operations. Regarding storage-efficiency and query speed, agreed that it could well hurt both, but it's going to depend. If a column holds null in almost all rows, we would expect it to be more space-efficient to use a separate table and a left outer join. Query speed could also improve for queries that don't reference the nullable column, as the 'main' table would be smaller in storage. (I'm assuming a rowstore here.)

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.
    - magicalhippo 1y ago
      > I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen would require 100-150 joins using a separate table per optional. I'm pretty sure the database would not love that.
      - naasking 1y ago
        400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : int not null) table Entity_TextFields (ParentEntityId : int not null, FieldId : int not null, Value : varchar(MAX) not null) table Entity_DateFields (ParentEntityId : int not null, FieldId : int not null, Value : datetime not null)
MaxBarraclough 1y ago
Right, Date's idea didn't catch on. I'm not aware of any industry-strength RDBMS prohibiting storing null. Agreed that queries would tend be longer as you'd need joins, although views could help, especially for read operations. Regarding storage-efficiency and query speed, agreed that it could well hurt both, but it's going to depend. If a column holds null in almost all rows, we would expect it to be more space-efficient to use a separate table and a left outer join. Query speed could also improve for queries that don't reference the nullable column, as the 'main' table would be smaller in storage. (I'm assuming a rowstore here.)