[kccqzy]

What's difficult about them? I typically use nullable columns and then a check constraint to specify a custom condition for nullability. Columns belonging to the same alternative in the sum type must be all null or all not null. And then there's check only one active alternative.

[DaiPlusPlus]

This approach doesn’t scale when business requirements change regularly such that you need to add or remove columns to an existing table.

Adding new columns by creating a new table is easy and cheap and doesn’t involve downtime. Adding 100+ columns to an existing table because the spec said a relationship went from 1:0-1 to 1:1 is a pain.

(This can be avoided with creative design with deferrable constraints, something certain major RDBMS are still lacking, gah!)

[6nf]

> Adding 100+ columns to an existing table

Whaaaa 100+ columns? How often do you need to add 100 columns to an existing table?

I mean honestly that has to be as rare as chicken teeth.

[no-s]

>>Whaaaa 100+ columns? How often do you need to add 100 columns to an existing table? >>I mean honestly that has to be as rare as chicken teeth.

Haha, and yet it happens. One of the worst databases I ever dealt with was some ridiculous variant of Universal Model for a SCADA application, the databases were named D1, D2, D3, and so on; the tables T1, T2, T3, etc; the columns C1, C2, C3, you get the picture I hope...when they hit maximum column count they would create a new table and bizarrely rebalance renumbered columns between them. In database D0 there was a catalog with all of the details relating back to the actual model. The system was actually implemented at some defense contractors and power system operators, I remember someone at Lockheed whining about hitting the number of databases limit for Sybase.

Hopefully nobody has to deal with that crap any more.

[6nf]

Oof I feel your pain

[kccqzy]

Whoah I've never dealt with tables with more than 20 columns. Can't imagine the shops that require 100+ columns.

[throwaway894345]

Type safety And query semantics, mostly. You can use constraints to get some of this back, but it only goes so far. Ultimately there’s a reason statically typed programming languages developed sum types, and all of those reasons apply to databases as well because data is data.

[gfody]

you can avoid nullable cols by using separate physical tables for each concrete type with surrogate PKs from a common sequence. these can be concatenated back together in a view (with no performance hit if you're careful not to hide indexes and predicate pushdowns). FKs then can reference the appropriate concrete table and in case you need an FK on some union you can use an indexed view instead.

I've used this approach for modeling entities that are polymorphic in object-land and haven't felt particularly underequipped - more robust indexed view support would be nice but that's active research territory (one of the hardest problems in computer science, materialize.io looks promising!)