On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden <jklow...@schemamania.org> wrote:
> You're making an efficiency argument here, or maybe > ease-of-implementation assertion. For me, inserting one header row or > 20 is the same coding effort (still need a loop). I think transaction > throughput would be about the same if COMMIT is applied only to whole > messages. > Not quite - if all the headers are encoded client-side in a blob (actually a string in this case), then they are always together in the database, there is no row management needed per header, there is no index needed on the message id, etc. DB normalization is nice, but "small" arrays of child data can simply be embedded in the row, gaining schema simplicity and efficiency. There are some conditions needed for this to work well: * data should be "small" (you can't page single row results) * data should mostly be needed together (overfetching is bad) * the only reference to the data should be from the parent object (can't reference row contents) Nowhere in my DB course at uni was this possibility covered. In this case, there are only a few headers that really matter, and their meaning can be encoded separately (sender, thread id etc), and then the headers are kept for reference. Given that, ISTM that textbook SQL 101 advice is in order. JSON should > wait until your assumptions are tested. > Well, that's sort of true, but it's easier to add a JSON field than creating extra tables. > Perhaps. It's still introducing an extraneous technology to the user's > problem domain. > Everything you need to manipulate JSON is available in SQLite, including pretending it's a table. So if push comes to shove, you can pretend that the JSON is a table and have the same "SQL surface" as before in the application. DBMSs are used for all kinds of purposes by people well trained and > not, to good effect and bad. The number who don't understand the basic > theory of what they're working with far exceeds those that do. Half of > them are below average, and the average isn't very high. > Actually, half of them are below median. Depending on the distribution, most of them could be above average ;-) > I'm sure you'll understand if popular opinion doesn't impress me. > It shouldn't - but this isn't a popular opinion. This is a trade-off between schema simplicity, storage layout and speed of some operations. I'd argue that in this particular case, a JSON field is beneficial for simplicity, speed and storage space. > Simpler systems are better, I'm sure you'd agree. > I agree. That's why I like full-stack JavaScript, SQLite, and JSON fields. I'm sure these are not all choices you would make, but for me, these are simple. JOINs etc are hard. Wout. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
