[tyre]

These are similar to arguments that people made about MongoDB. You can store anything! And then most people who used it realized that this is actually terrible, in most cases.

It looks like this is an artifact of when SQLite was written and the strong opinion of its author, less so a rigorous engineering principle. Reading this, it sounds like the author has been criticized a lot on this, is digging in their heels no matter what, and will find any supposed justification.

On the other hand, datatypes like JSON or HSTORE (in postgres) can handle what they are advocating for. But opt-in to YOLO typing is nearly always better than opt-out.

[zvrba]

I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar).

Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc.

It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.

[fauigerzigerk]

>It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.

Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.

You could of course have a JSON schema somewhere, but in my experience the whole point of representing the schema as data in the database is to support (limited) end-user driven schema changes.

I would use JSON to store data that complies with a schema that can be modified by third parties outside of my control.

[zvrba]

> Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.

The point is that "dynamism" isn't needed in this case. It's used to define fixed parameter sets for different components, so those components could just as well define a class and use json to (de)serialize it.

IOW: Defining new fields, classes and values is worthless unless the underlying component can interpret them. So code changes are needed anyway, and then class is way better and safer to use.