Best practices for creating dynamic SQL Tables?

Question

Anki allows creation of custom flashcard types[0] and I was wondering how that was done programmatically. I know about CREATE TABLE in SQL, but is it unsafe to define a table on the fly? I tried checking Anki's source code[1], but there doesn't seem to be any mention of how this is achieved.Thanks in advance[0]: https://docs.ankiweb.net/editing.html#adding-a-note-type [1]: https://github.com/ankitects/anki/tree/ca0374782e709b6877162328945b55181cdb2445/rslib/src/storage/notetype

dyeje · Accepted Answer

Dynamic tables like you're describing are covered in the Metadata Tribbles chapter of the book SQL Antipatterns.https://pragprog.com/titles/bksqla/sql-antipatterns/

allwein · Answer

It's not done using dynamic SQL tables.The key insight is to note that FIELDS table actually defines the name, ordering, and config for each of the fields of a given note type, and that the actual notes data is stored in the Notes table in the DATA column, most likely as JSON.

Best practices for creating dynamic SQL Tables?

Dynamic tables like you're describing are covered in the Metadata Tribbles chapter of the book SQL Antipatterns.
https://pragprog.com/titles/bksqla/sql-antipatterns/

It's not done using dynamic SQL tables.
The key insight is to note that FIELDS table actually defines the name, ordering, and config for each of the fields of a given note type, and that the actual notes data is stored in the Notes table in the DATA column, most likely as JSON.