[lmm]

Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data.

Delivery addresses for a system that supports both physical and digital products - you want a type-level distinction between physical and digital addresses, but an order might be being shipped to either.

Subscription vs free trial - they're different kinds of thing, but you want to store more details (e.g. expiry date) than just an enum of one or the other.

[jayd16]

>Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data.

What I've seen most often is you have to deal with account merging but lets say you do really want either/or...

Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd have login_type and login_id columns? You know which table to hit by type using the id?

[lmm]

> Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd have login_type and login_id columns? You know which table to hit by type using the id?

You can do that but it's a bodge. E.g. you won't be able to have the foreign key constraints you'd normally have on that login_id column. And good luck writing a query that actually does something differently for each case - you'd have to do something like multiple left joins and there's no way to check you've handled all the cases and not done one of them twice.

[jayd16]

You can have a table or view of all the ids across your implementations and FK into that.

As for how you would model it in the application, in this case you can just normalize across all the possible columns and have the orm build out your mapped object.

[lmm]

> You can have a table or view of all the ids across your implementations and FK into that.

That's just moving the problem around. That table would have to have a bunch of nullable columns, and there's no way to express the constraint that either these columns are non-null or those columns are non-null, or these columns are non-null when this enum has this value.

I'm guessing you've never seriously used a language with first-class sum types. Yes, you can use hacks to represent sum-typed data in languages that don't have proper sum types, but it's always going to be a hack. It's like saying C has OO support because you can always construct virtual function tables by hand.