[SoftTalker]

> 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.

[Mikhail_Edoshin]

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]

> 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 ...

[holden_nelson]

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

[roblh]

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]

If you enjoyed this, you'd probably enjoy thedailywtf.com, which is full of stories like that.

[lelanthran]

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]

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.

[linolevan]

This is awesome. Got completely lost reading this and was struggling to figure out where I got this link from. Amazing story.

[bobson381]

This is like the functional ugly tier of buildings from "how buildings learn". Excellent stuff

[gz09]

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]

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]

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]

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]

> 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.

[diarrhea]

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?

[locknitpicker]

> 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.

Strong disagree. I'll explain.

Your argument would support the idea of adding a few columns to a table to get to a short time to market. That's ok.

Your comment does not come close to justify why you would keep the columns in. Not the slightest.

Tables with many columns create all sorts of problems and inefficiencies. Over fetching is a problem all on itself. Even the code gets brittle, where each and every single tweak risks beijg a major regression.

Creating a new table is not hard. Add a foreign key, add the columns, do a standard parallel write migration. Done. How on earth is this not practical?

[fiddlerwoaroof]

I’m not justifying the design but splitting a table with several billion rows is not a trivial task, especially when ORMs and such are involved. Additionally, it’s easier to get work scheduled to ship a feature than it is to convince the relevant players to complete the swing.

[locknitpicker]

> I’m not justifying the design but splitting a table with several billion rows is not a trivial task, especially when ORMs and such are involved.

I don't agree. Let me walk you through the process.

- create the new table - follow a basic parallel writes strategy -- update your database consumers to write to the new table without reading from it -- run a batch job to populate the new table with data from the old table -- update your database consumer to read from the new table while writing to both old and new tables

From this point onward, just pick a convenient moment to stop writing to the old database and call the migration done. Do post-migrarion cleanup tasks.

> Additionally, it’s easier to get work scheduled to ship a feature than it is to convince the relevant players to complete the swing.

The ease of piling up technical debt is not a justification to keep broken systems and designs. It's only ok to make a messs to deliver things because you're expected to clean after yourself afterwards.

[grey-area]

There are sometimes reasons this is harder in practice, for example let’s say the business or even third parties have access to this db directly and have hundreds of separate apps/services relying on this db (also an anti-pattern of course but not uncommon), that makes changing the db significantly harder.

Mistakes made early on and not corrected can snowball and lead to this kind of mess, which is very hard to back out of.

[magicalhippo]

> How on earth is this not practical?

Fine, but you still need to read in those 100+ fields. So now you gotta contend with 20+ joins just to pull in one record. Not more practical than a single SELECT in my opinion.

[sublinear]

You don't need to join what you don't actually need. You also need to be careful writing your queries, not just the schema. The most common ones should be wrapped in views or functions to avoid the problem of everyone rolling their own later.

Performance generally isn't an issue for an arbitrary number of joins as long as your indices are set up correctly.

If you really do need a bulk read like that I think you want json columns, or to just go all in with a nosql database. Even then, the above regarding indexing is still true.

[rpcope1]

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.

[woah]

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]

> 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

[unclad5968]

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

[pizza-wizard]

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.

[ambicapter]

What's in them?

[unclad5968]

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]

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...

[jayanmn]

Property1 to 20 or more is an example. There are better ways to do it but I have seen columns for storing ‘anything’

[Spivak]

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.

[adrianN]

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.

[vharuck]

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.

[magicalhippo]

> 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.

[nikhilsimha]

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.

[orthoxerox]

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.

[bob1029]

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]

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.

[wombatpm]

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.

[locknitpicker]

> 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]

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]

Yep pretty much, feldera is the engine we don't control what SQL people throw at us.

[bananamogul]

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]

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]

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.

[shakna]

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.