Ask HN: Best practices for creating dynamic SQL Tables?
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
Comments
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/
Thank you for that! Are there any other resources for learning about SQL design patterns?
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.
To add to/reiterate the above, they use a separate table to store metadata. Whenever you see a situation in a webservice where there is a custom way to store data, it's likely that they either store metadata in a table, store is as a more lose format like JSON or XML, or a combination of both.
In my experience, most services don't make tables on the fly that are meant to hang around, since storing metadata can get you very far.
Thank you for that! How 'safe' is it to store notes data as JSON? So how can that be validated, compared to storing in table columns?