5 ms·
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
- allwein 5y agoIt'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.
- jjice 5y agoTo 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.
- pandatigox 5y agoThank 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?
- dyeje 5y agoDynamic tables like you're describing are covered in the Metadata Tribbles chapter of the book SQL Antipatterns. https://pragprog.com/titles/bksqla/sql-antipatterns/ https://pragprog.com/titles/bksqla/sql-antipatterns/
- pandatigox 5y agoThank you for that! Are there any other resources for learning about SQL design patterns?