Hacker News Reader

Comment by seveibar | Hacker News Reader

Having worked with a lot of government CSV/XLSX datasets (e.g. IPEDS college reporting), I can say I'd prefer something with proper foreign keys and less ambiguity in structure (people like to put descriptive cells at the top of spreadsheets)

View on HN

Grist is an sqlite-based spreadsheet format that captures relations well and discourages funky structure https://www.getgrist.com/blog - it works well for passing relational datasets around (disclosure, I work on it)

(the poster is associated with the product)

Proper foreign keys don't really suit the dumped data case. Tables are usually imported one by one, in virtually random order so dependencies are often missing at the import time. Even if they were not, checking keys on every record import would mean a huge load for big datasets - that's really slow. That's why foreign key control is usually switched off before importing.

Postgres at least can defer these checks, it's generally a best practice to disable rebuilding indexes while loading as well. It's not hard to simply load the tables in order so the keys are available. I would expect any decent unload utility to also create a script to load the data which should be in the right order.

I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.

Even without checks, there's a benefit to having documented links like this tied to the data release rather than a random website.

Reply on news.ycombinator.com