HN Explorer

Ask HN: Best practices for creating dynamic SQL Tables?

3 points by pandatigox ↗ HN
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

5 comments

[ 5.8 ms ] story [ 28.8 ms ] thread
allwein
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.

jjice
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.

pandatigox
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?