6 ms·

> But SQL schemas often look like this. Columns are nullable by default, and wide tables are common. Hard disagree. That database table was a waving red flag.

> But SQL schemas often look like this. Columns are nullable by default, and wide tables are common. Hard disagree. That database table was a waving red flag. I don't know enough/any rust so don't really understand the rest of the article but I have never in my life worked with a database table that had 700 columns. Or even 100.

decremental 8mo ago
[dead]
gz09 8mo ago
Hi, I'm the author of the article. As to your hard disagree, I guess it depends... While this particular user is on the higher end (in terms of columns), it's not our only user where column counts are huge. We see tables with 100+ columns on a fairly regular basis especially when dealing with larger enterprises.
- sublinear 8mo ago
  Can you clarify which knowledge domains those enterprises fall under with examples of what problems they were trying to solve? If it's not obvious, I agree with the hard disagree. Every time I see a table with that many columns, I have a hard time believing there isn't some normalization possible. Schemas that stubbornly stick to high-level concepts and refuse to dig into the subfeatures of the data are often seen from inexperienced devs or dysfunctional/disorganized places too inflexible to care much. This isn't really negotiable. There will be issues with such a schema if it's meant to scale up or be migrated or maintained long term.
  - fiddlerwoaroof 8mo ago
    Normalization is possible but not practical in a lot of cases: nearly every “legacy” database I’ve seen has at least one table that just accumulates columns because that was the quickest way to ship something. Also, normalization solves a problem that’s present in OLTP applications: OLAP/Big Data applications generally have problems that are solved by denormalization.
    - gz09 8mo ago
      Yep, this comment sums it up well. We have many large enterprises from wildly different domains use feldera and from what I can tell there is no correlation between the domain and the amount of columns. As fiddlerwoaroof says, it seems to be more a function of how mature/big the company is and how much time it had to 'accumulate things' in their data model. And there might be very good reasons to design things the way they did, it's very hard to question it without being a domain expert in their field, I wouldn't dare :).
      - locknitpicker 8mo ago
        > I can tell there is no correlation between the domain and the amount of columns. This is unbelievable. In purely architectural terms that would require your database design to be an amorphous big ball of everything, with no discernible design or modelling involved. This is completely unrealistic. Are queries done at random? In practical terms, your assertion is irrelevant. Look at the sparse columns. Figure out those with sparse rows. Then move half of the columns to a new table and keep the other half in the original table. Congratulations, you just cut down your column count by half, and sped up your queries. Even better: discover how your data is being used. Look at queries and check what fields are used in each case. Odds are, that's your table right there. Let's face it. There is absolutely no technical or architectural reason to reach this point. This problem is really not about structs.
        deleted 8mo ago
        [deleted]
        diarrhea 8mo ago
        Feldera speak from lived experience when they say 100+ column tables are common in their customer base. They speak from lived experience when they say there's no correlation in their customer base. Feldera provides a service. They did not design these schemas. Their customers did, and probably over such long time periods that those schemas cannot be referred to as designed anymore -- they just happened. IIUC Feldera works in OLAP primarily, where I have no trouble believing these schemas are common. At my $JOB they are, because it works well for the type of data we process. Some OLAP DBs might not even support JOINs. Feldera folks are simply reporting on their experience, and people are saying they're... wrong?
  - rpcope1 8mo ago
    I think you believe the average developer, especially on enterprise software where you see this sort of shit, is far more competent or ambitious than they actually are. Many would be horrified to see the number of monkeys banging out nasty DDL in Hibernate or whatever C# uses that have no idea what "normal forms" or "relational algebra" are and are actively resistant to even attempting to learn.
Mikhail_Edoshin 8mo ago
I saw tables with more than a thousand columns. It was a law firm home-grown FileMaker tool. Didn't inspect it too closely, so don't know what was inside I remember a phrase from one of C. J. Date's books: every record is a logical statement. It really stood out for me and I keep returning to it. Such an understanding implies a rather small number of fields or the logical complexity will go through the roof.
- p_v_doom 8mo ago
  > I saw tables with more than a thousand columns. I used to work in a company that had all the tags in their SCADA system feed into SQL tables. They had multiple tables (as in tens of tables), because they ran out of columns ...
unclad5968 8mo ago
It might not be common in typical software shops. I work in manufacturing and our database has multiple tables with hundreds of columns.
- ambicapter 8mo ago
  What's in them?
  - jayanmn 8mo ago
    Property1 to 20 or more is an example. There are better ways to do it but I have seen columns for storing ‘anything’
    - Spivak 8mo ago
      Sounds like a generic form of single table inheritance. I don't honestly see any other way to do it (punting to a JSON field is effectively the same thing) when you have potentially thousands of parts all with their own super specific relevant attributes. I've worked on multiple products that have had a concept of "custom fields" who did it this way too.
  - unclad5968 8mo ago
    Data from measurement tools. Everything about the tool configuration, time of measurement, operator ID, usually a bunch of electrical data (we make laser diodes) like current, potential, power, and a bunch of emission related data.
    - arethuza 8mo ago
      I think I'd rather work with very wide tables than the entity-attribute-value approach - which seems like a good idea but rapidly becomes a mess...
- pizza-wizard 8mo ago
  I’m working on migrating an IBM Maximo database from the late 90s to a SQL Server deployment on my current project. Also charged with updating the schema to a more maintainable and extensible design. Manufacturing and refurbishing domain - 200+ column tables is the norm. Very demoralizing.
holden_nelson 8mo ago
https://jimmyhmiller.com/ugliest-beautiful-codebase https://jimmyhmiller.com/ugliest-beautiful-codebase
- roblh 8mo ago
  I kinda love this. That sounds like an incredibly entertaining place to work for between 1 and 2 years in your late 20s and not a second longer.
  - tdeck 8mo ago
    If you enjoyed this, you'd probably enjoy thedailywtf.com, which is full of stories like that.
- bobson381 8mo ago
  This is like the functional ugly tier of buildings from "how buildings learn". Excellent stuff
- linolevan 8mo ago
  This is awesome. Got completely lost reading this and was struggling to figure out where I got this link from. Amazing story.
- lelanthran 8mo ago
  With AI "programmers", this will be the future: bugs galore and the things that do work, work by accident. I think this company was ahead of the curve.
- locknitpicker 8mo ago
  The blog post is an entertaining read, but I was left with the impression the author might have tried do embellish, particularly in it's disbelief angle. Take this passage: > The app relied on a SOAP service, not to do any servicey things. No, the service was a pure function. It was the client that did all the side effects. In that client, I discovered a massive class hierarchy. 120 classes each with various methods, inheritance going 10 levels deep. The only problem? ALL THE METHODS WERE EMPTY. I do not exaggerate here. Not mostly empty. Empty. > That one stumped me for a while. Eventually, I learned this was in service of building a structure he could then use reflection on. That reflection would let him create a pipe-delimited string (whose structure was completely database-driven, but entirely static) that he would send over a socket. Classes with empty methods? Used reflection to create a pipe-delimited string? The string was sent over the wire? Why congratulations, you just rediscovered data transfer objects, specifically API models.
woah 8mo ago
No idea what these guys do exactly but their tagline says "Feldera's award-winning incremental compute engine runs SQL pipelines of any complexity" So it sounds like helping customers with databases full of red flags is their bread and butter
- gz09 8mo ago
  > it sounds like helping customers with databases full of red flags is their bread and butter Yes that captures it well. Feldera is an incremental query engine. Loosely speaking: it computes answers to any of your SQL queries by doing work proportional to the incoming changes for your data (rather than the entire state of your database tables). If you have queries that take hours to compute in a traditional database like Spark/PostgreSQL/Snowflake (because of their complexity, or data size) and you want to always have the most up-to-date answer for your queries, feldera will give you that answer 'instantly' whenever your data changes (after you've back-filled your existing dataset into it). There is some more information about how it works under the hood here: https://docs.feldera.com/literature/papers https://docs.feldera.com/literature/papers
nikhilsimha 8mo ago
It is very common to find tables with 1000+ columns in machine learning training sets at e-commerce companies. The largest I have seen had over 10000 columns.
bananamogul 8mo ago
That statement jumped out at me as well. I've worked as a DBA on tons of databases backing a wide variety of ERPs, web apps, analytics, data warehouses...700 columns?!? No.
- shakna 8mo ago
  You've never seen an SAP database where the business object had a couple hundred fields? Its pretty much required if you're touching international data.
randallsquared 8mo ago
I have seen tables (SQL and parquet, too) that have at least high hundreds of optional columns, but this was always understood to be a terrible hack, in those cases.
wombatpm 8mo ago
Not everyone understands normal form, much less 3rd normal form. I’ve seen people do worse with excel files where they ran out of columns and had to link across spreadsheets.
vharuck 8mo ago
https://apps.naaccr.org/data-dictionary/data-dictionary/version=26/chapter-view/data-descriptor-table/ https://apps.naaccr.org/data-dictionary/data-dictionary/vers... 771 columns (and I've read the definitions for them all, plus about 50 more that have been retired). In the database, these are split across at least 3 tables (registry, patient, tumor). But when working with the records, it's common to use one joined table. Luckily, even that usually fits in RAM.
- deleted 8mo ago
  [deleted]
orthoxerox 8mo ago
It's OLAP, it very common for analytical tables to be denormalized. As an example, each UserAction row can include every field from Device and User to maximize the speed at which fraud detection works. You might even want to store multiple Devices in a single row: current, common 1, 2 and 3.
shakna 8mo ago
Salesforce by default comes with some where your tables have 50 columns before you start tweaking anything. 100s is not unusual. Thousands happens before you realise.
locknitpicker 8mo ago
> Hard disagree. That database table was a waving red flag. Exactly this. This article is not about structs or Rust. This article is about poor design of the whole persistence layer. I mean, hundreds of columns? Almost all of them optional? This is the kind of design that gets candidates to junior engineer positions kicked off a hiring round. Nobody gets fired for using a struct? If it's an organization that tolerates database tables with nearly 1k optional rows then that comes at no surprise.
- grey-area 8mo ago
  The database table is someone else’s data. That’s why this company exists and is explained in the article. They don’t have the option to clean up the data.
  - gz09 8mo ago
    Yep pretty much, feldera is the engine we don't control what SQL people throw at us.
adrianN 8mo ago
If lots of columns are a red flag then red flags are quite common in many businesses. I’ve seen tables with tens of thousands of columns. Naturally those are not used by humans writing sql by hand, but there are many tools that have crazy data layouts and generate crazy sql to work with it.
bob1029 8mo ago
Some businesses are genuinely this complicated. Splitting those facts into additional tables isn't going to help very much unless it actually mirrors the shape of the business. If it doesn't align, you are forcing a lot of downstream joins for no good reason.
xarope 8mo ago
OLTP tables typically are normalized. But OLAP tables (data lake/warehouse stuff), for speed purposes, are intentionally denormalized and yes, you can have 100+ columns of nullable stuff.
magicalhippo 8mo ago
> I have never in my life worked with a database table that had 700 columns Main table at work is about 600, though I suspect only 300-400 are actively used these days. A lot come from name and address fields, we have about 10 sets of those in the main table, and around 14 fields per. Back when this was created some 20+ years ago it was faster and easier to have it all in one row rather than to do 20+ joins. We probably would segment it a bit more if we did it from scratch, but only some.

decremental 8mo ago

[dead]

gz09 8mo ago

Hi, I'm the author of the article. As to your hard disagree, I guess it depends... While this particular user is on the higher end (in terms of columns), it's not our only user where column counts are huge. We see tables with 100+ columns on a fairly regular basis especially when dealing with larger enterprises.

sublinear 8mo ago
Can you clarify which knowledge domains those enterprises fall under with examples of what problems they were trying to solve? If it's not obvious, I agree with the hard disagree. Every time I see a table with that many columns, I have a hard time believing there isn't some normalization possible. Schemas that stubbornly stick to high-level concepts and refuse to dig into the subfeatures of the data are often seen from inexperienced devs or dysfunctional/disorganized places too inflexible to care much. This isn't really negotiable. There will be issues with such a schema if it's meant to scale up or be migrated or maintained long term.
- fiddlerwoaroof 8mo ago
  Normalization is possible but not practical in a lot of cases: nearly every “legacy” database I’ve seen has at least one table that just accumulates columns because that was the quickest way to ship something. Also, normalization solves a problem that’s present in OLTP applications: OLAP/Big Data applications generally have problems that are solved by denormalization.
  - gz09 8mo ago
    Yep, this comment sums it up well. We have many large enterprises from wildly different domains use feldera and from what I can tell there is no correlation between the domain and the amount of columns. As fiddlerwoaroof says, it seems to be more a function of how mature/big the company is and how much time it had to 'accumulate things' in their data model. And there might be very good reasons to design things the way they did, it's very hard to question it without being a domain expert in their field, I wouldn't dare :).
    - locknitpicker 8mo ago
      > I can tell there is no correlation between the domain and the amount of columns. This is unbelievable. In purely architectural terms that would require your database design to be an amorphous big ball of everything, with no discernible design or modelling involved. This is completely unrealistic. Are queries done at random? In practical terms, your assertion is irrelevant. Look at the sparse columns. Figure out those with sparse rows. Then move half of the columns to a new table and keep the other half in the original table. Congratulations, you just cut down your column count by half, and sped up your queries. Even better: discover how your data is being used. Look at queries and check what fields are used in each case. Odds are, that's your table right there. Let's face it. There is absolutely no technical or architectural reason to reach this point. This problem is really not about structs.
      - deleted 8mo ago
        [deleted]
      - diarrhea 8mo ago
        Feldera speak from lived experience when they say 100+ column tables are common in their customer base. They speak from lived experience when they say there's no correlation in their customer base. Feldera provides a service. They did not design these schemas. Their customers did, and probably over such long time periods that those schemas cannot be referred to as designed anymore -- they just happened. IIUC Feldera works in OLAP primarily, where I have no trouble believing these schemas are common. At my $JOB they are, because it works well for the type of data we process. Some OLAP DBs might not even support JOINs. Feldera folks are simply reporting on their experience, and people are saying they're... wrong?
- rpcope1 8mo ago
  I think you believe the average developer, especially on enterprise software where you see this sort of shit, is far more competent or ambitious than they actually are. Many would be horrified to see the number of monkeys banging out nasty DDL in Hibernate or whatever C# uses that have no idea what "normal forms" or "relational algebra" are and are actively resistant to even attempting to learn.

Mikhail_Edoshin 8mo ago

I saw tables with more than a thousand columns. It was a law firm home-grown FileMaker tool. Didn't inspect it too closely, so don't know what was inside I remember a phrase from one of C. J. Date's books: every record is a logical statement. It really stood out for me and I keep returning to it. Such an understanding implies a rather small number of fields or the logical complexity will go through the roof.

p_v_doom 8mo ago
> I saw tables with more than a thousand columns. I used to work in a company that had all the tags in their SCADA system feed into SQL tables. They had multiple tables (as in tens of tables), because they ran out of columns ...

unclad5968 8mo ago

It might not be common in typical software shops. I work in manufacturing and our database has multiple tables with hundreds of columns.

ambicapter 8mo ago
What's in them?
- jayanmn 8mo ago
  Property1 to 20 or more is an example. There are better ways to do it but I have seen columns for storing ‘anything’
  - Spivak 8mo ago
    Sounds like a generic form of single table inheritance. I don't honestly see any other way to do it (punting to a JSON field is effectively the same thing) when you have potentially thousands of parts all with their own super specific relevant attributes. I've worked on multiple products that have had a concept of "custom fields" who did it this way too.
- unclad5968 8mo ago
  Data from measurement tools. Everything about the tool configuration, time of measurement, operator ID, usually a bunch of electrical data (we make laser diodes) like current, potential, power, and a bunch of emission related data.
  - arethuza 8mo ago
    I think I'd rather work with very wide tables than the entity-attribute-value approach - which seems like a good idea but rapidly becomes a mess...
pizza-wizard 8mo ago
I’m working on migrating an IBM Maximo database from the late 90s to a SQL Server deployment on my current project. Also charged with updating the schema to a more maintainable and extensible design. Manufacturing and refurbishing domain - 200+ column tables is the norm. Very demoralizing.

holden_nelson 8mo ago

https://jimmyhmiller.com/ugliest-beautiful-codebase https://jimmyhmiller.com/ugliest-beautiful-codebase

roblh 8mo ago
I kinda love this. That sounds like an incredibly entertaining place to work for between 1 and 2 years in your late 20s and not a second longer.
- tdeck 8mo ago
  If you enjoyed this, you'd probably enjoy thedailywtf.com, which is full of stories like that.
bobson381 8mo ago
This is like the functional ugly tier of buildings from "how buildings learn". Excellent stuff
linolevan 8mo ago
This is awesome. Got completely lost reading this and was struggling to figure out where I got this link from. Amazing story.
lelanthran 8mo ago
With AI "programmers", this will be the future: bugs galore and the things that do work, work by accident. I think this company was ahead of the curve.
locknitpicker 8mo ago
The blog post is an entertaining read, but I was left with the impression the author might have tried do embellish, particularly in it's disbelief angle. Take this passage: > The app relied on a SOAP service, not to do any servicey things. No, the service was a pure function. It was the client that did all the side effects. In that client, I discovered a massive class hierarchy. 120 classes each with various methods, inheritance going 10 levels deep. The only problem? ALL THE METHODS WERE EMPTY. I do not exaggerate here. Not mostly empty. Empty. > That one stumped me for a while. Eventually, I learned this was in service of building a structure he could then use reflection on. That reflection would let him create a pipe-delimited string (whose structure was completely database-driven, but entirely static) that he would send over a socket. Classes with empty methods? Used reflection to create a pipe-delimited string? The string was sent over the wire? Why congratulations, you just rediscovered data transfer objects, specifically API models.

woah 8mo ago

No idea what these guys do exactly but their tagline says "Feldera's award-winning incremental compute engine runs SQL pipelines of any complexity" So it sounds like helping customers with databases full of red flags is their bread and butter

gz09 8mo ago
> it sounds like helping customers with databases full of red flags is their bread and butter Yes that captures it well. Feldera is an incremental query engine. Loosely speaking: it computes answers to any of your SQL queries by doing work proportional to the incoming changes for your data (rather than the entire state of your database tables). If you have queries that take hours to compute in a traditional database like Spark/PostgreSQL/Snowflake (because of their complexity, or data size) and you want to always have the most up-to-date answer for your queries, feldera will give you that answer 'instantly' whenever your data changes (after you've back-filled your existing dataset into it). There is some more information about how it works under the hood here: https://docs.feldera.com/literature/papers https://docs.feldera.com/literature/papers

nikhilsimha 8mo ago

It is very common to find tables with 1000+ columns in machine learning training sets at e-commerce companies. The largest I have seen had over 10000 columns.

bananamogul 8mo ago

That statement jumped out at me as well. I've worked as a DBA on tons of databases backing a wide variety of ERPs, web apps, analytics, data warehouses...700 columns?!? No.

shakna 8mo ago
You've never seen an SAP database where the business object had a couple hundred fields? Its pretty much required if you're touching international data.

randallsquared 8mo ago

I have seen tables (SQL and parquet, too) that have at least high hundreds of optional columns, but this was always understood to be a terrible hack, in those cases.

wombatpm 8mo ago

Not everyone understands normal form, much less 3rd normal form. I’ve seen people do worse with excel files where they ran out of columns and had to link across spreadsheets.

vharuck 8mo ago

https://apps.naaccr.org/data-dictionary/data-dictionary/version=26/chapter-view/data-descriptor-table/ https://apps.naaccr.org/data-dictionary/data-dictionary/vers... 771 columns (and I've read the definitions for them all, plus about 50 more that have been retired). In the database, these are split across at least 3 tables (registry, patient, tumor). But when working with the records, it's common to use one joined table. Luckily, even that usually fits in RAM.

deleted 8mo ago
[deleted]

orthoxerox 8mo ago

It's OLAP, it very common for analytical tables to be denormalized. As an example, each UserAction row can include every field from Device and User to maximize the speed at which fraud detection works. You might even want to store multiple Devices in a single row: current, common 1, 2 and 3.

shakna 8mo ago

Salesforce by default comes with some where your tables have 50 columns before you start tweaking anything. 100s is not unusual. Thousands happens before you realise.

locknitpicker 8mo ago

> Hard disagree. That database table was a waving red flag. Exactly this. This article is not about structs or Rust. This article is about poor design of the whole persistence layer. I mean, hundreds of columns? Almost all of them optional? This is the kind of design that gets candidates to junior engineer positions kicked off a hiring round. Nobody gets fired for using a struct? If it's an organization that tolerates database tables with nearly 1k optional rows then that comes at no surprise.

grey-area 8mo ago
The database table is someone else’s data. That’s why this company exists and is explained in the article. They don’t have the option to clean up the data.
- gz09 8mo ago
  Yep pretty much, feldera is the engine we don't control what SQL people throw at us.

adrianN 8mo ago

If lots of columns are a red flag then red flags are quite common in many businesses. I’ve seen tables with tens of thousands of columns. Naturally those are not used by humans writing sql by hand, but there are many tools that have crazy data layouts and generate crazy sql to work with it.

bob1029 8mo ago

Some businesses are genuinely this complicated. Splitting those facts into additional tables isn't going to help very much unless it actually mirrors the shape of the business. If it doesn't align, you are forcing a lot of downstream joins for no good reason.

xarope 8mo ago

OLTP tables typically are normalized. But OLAP tables (data lake/warehouse stuff), for speed purposes, are intentionally denormalized and yes, you can have 100+ columns of nullable stuff.

magicalhippo 8mo ago

> I have never in my life worked with a database table that had 700 columns Main table at work is about 600, though I suspect only 300-400 are actively used these days. A lot come from name and address fields, we have about 10 sets of those in the main table, and around 14 fields per. Back when this was created some 20+ years ago it was faster and easier to have it all in one row rather than to do 20+ joins. We probably would segment it a bit more if we did it from scratch, but only some.