4 ms·

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 w

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.
      - 1y ago
- Breza 1y ago
  This sounds like the kinds of healthcare claims data that I often analyze for work. Just in case you have a team of seven doctors working on you at the same time while you're in hospice, they keep a column for just about everything.

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.
    - 1y ago
Breza 1y ago
This sounds like the kinds of healthcare claims data that I often analyze for work. Just in case you have a team of seven doctors working on you at the same time while you're in hospice, they keep a column for just about everything.