5 ms·

> but having separate tables for every optional field would be an organizational and usability nightmare I think this indicates that declaring and managing sta

> 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)
      - magicalhippo 1y ago
        > 400 columns all of which are nullable screams "dynamic field set" to me Could have been, but no. And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so. And this is just the main table, we have many child tables already, though they're not nearly as wide. I think a more realistic split would have been to split the main table into maybe 10 tables or so at most. Still would result in a fair bit of subqueries for grids and such, but not that bad.
        naasking 1y ago
        > And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. Only if you insist on loading the data into a flat record type with all of those fields. You have a dynamically evolving datatype, so your class should also be dynamic: public enum FieldId { Field1, Field2, //... } public class YourRecord { public Dictionary<FieldId, string> TextValues { get; set; } public Dictionary<FieldId, int> IntValues { get; set; } public Dictionary<FieldId, DateTime> DateValues { get; set; } // ... } At most the number of subqueries increases by the number types your class needs. SQL has a fixed number of datatypes, and most apps use a small subset of these (dates, text, integral, floating point and decimal types, that's typically 5 at most). You could collapse these all into a single table with optional columns for each value too and then it's only one query. > And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so. That's a fairly small amount of data, I don't really see an issue here. Also, you're neglecting the fact that since the fields are independent, adding a field value doesn't involve locking and rewriting a large 400 column row to disk, but only appending a tiny 4 column row to a separate distinct table.
- naasking 1y ago
  > The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It sure is. Consider a database language that innately supported algebraic data types. Instead of: table Contact { int Id; Nullable<string> email; Nullable<string> phoneNo; } you have: type PhoneOrEmail = Phone<string> | Email<string> table Contact { int Id; PhoneOrEmail info; } This completely clarifies the relationships between the nullable columns (can both be null, or only one null?), and the database storage layer would manage how to actually store this. This is a direct consequence of SQL's semantics and how it implements the relational calculus.
  - crazygringo 1y ago
    That seems like a logical nightmare to me. When thinking about which fields get returned, which fields do you index over, etc. It's a nice idea in theory, but would add an astronomical level of complexity in practice.
    - naasking 1y ago
      If the database supports algebraic data types, you wouldn't have to think about which fields get returned, it understand the needed semantics and handles that for you. Like I said, these are SQL limitations.
      - crazygringo 1y ago
        You'd need to handle it in your code. The last thing I want is a database that returns different columns per row. No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.
        naasking 1y ago
        > The last thing I want is a database that returns different columns per row. Actually that's exactly what you'd want, because it saves you from running two different queries in those cases with properly normalized disjoint data sets, and moves more of the domain's constraints into the database schema where it belongs.

> 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)
    - magicalhippo 1y ago
      > 400 columns all of which are nullable screams "dynamic field set" to me Could have been, but no. And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so. And this is just the main table, we have many child tables already, though they're not nearly as wide. I think a more realistic split would have been to split the main table into maybe 10 tables or so at most. Still would result in a fair bit of subqueries for grids and such, but not that bad.
      - naasking 1y ago
        > And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. Only if you insist on loading the data into a flat record type with all of those fields. You have a dynamically evolving datatype, so your class should also be dynamic: public enum FieldId { Field1, Field2, //... } public class YourRecord { public Dictionary<FieldId, string> TextValues { get; set; } public Dictionary<FieldId, int> IntValues { get; set; } public Dictionary<FieldId, DateTime> DateValues { get; set; } // ... } At most the number of subqueries increases by the number types your class needs. SQL has a fixed number of datatypes, and most apps use a small subset of these (dates, text, integral, floating point and decimal types, that's typically 5 at most). You could collapse these all into a single table with optional columns for each value too and then it's only one query. > And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so. That's a fairly small amount of data, I don't really see an issue here. Also, you're neglecting the fact that since the fields are independent, adding a field value doesn't involve locking and rewriting a large 400 column row to disk, but only appending a tiny 4 column row to a separate distinct table.
naasking 1y ago
> The idea that having a separate table for every optional field is too unworkable isn't an issue with SQL. It sure is. Consider a database language that innately supported algebraic data types. Instead of: table Contact { int Id; Nullable<string> email; Nullable<string> phoneNo; } you have: type PhoneOrEmail = Phone<string> | Email<string> table Contact { int Id; PhoneOrEmail info; } This completely clarifies the relationships between the nullable columns (can both be null, or only one null?), and the database storage layer would manage how to actually store this. This is a direct consequence of SQL's semantics and how it implements the relational calculus.
- crazygringo 1y ago
  That seems like a logical nightmare to me. When thinking about which fields get returned, which fields do you index over, etc. It's a nice idea in theory, but would add an astronomical level of complexity in practice.
  - naasking 1y ago
    If the database supports algebraic data types, you wouldn't have to think about which fields get returned, it understand the needed semantics and handles that for you. Like I said, these are SQL limitations.
    - crazygringo 1y ago
      You'd need to handle it in your code. The last thing I want is a database that returns different columns per row. No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.
      - naasking 1y ago
        > The last thing I want is a database that returns different columns per row. Actually that's exactly what you'd want, because it saves you from running two different queries in those cases with properly normalized disjoint data sets, and moves more of the domain's constraints into the database schema where it belongs.