SQLite Forum

General question concerning database stucture and number of databases to use.
Login
>It appears that my thinking was rather backward, for I thought a larger number of tables containing fewer records would be more efficient in some manner.

If I may, your thinking is backward in another sense: you are caring about physical details *before* having resolved all the logical issues. What's most important, that your user's data is correct at all times or that they get garbage, but fast, responses? “Premature optimization is the source of all evils“ they say, and that applies to databases more than to anything else. My recommendation is: think about your data logically, designing as many “tables“ (relation schemas) as needed, no more, no less; then, trust SQLite to do its job egregiously. If at some point it fails you, only then investigate why and take corrective actions. You might be surprised at the end to discover how many of your assumptions turned out to be wrong. You might not need to take any corrective action, after all.

>The concept of a relation schema as opposed to a table being a generic empty container to hold records is very helpful.

The concept is not only helpful, it is essential to the Relational model (albeit way too many times ignored). Tables (more formally, relation schemas) are far from “generic containers” holding a bunch of “records”. They carry meaning: a precise semantics decided by the database designer. A relation schema corresponds to a predicate, which you must define and document; and each tuple corresponds to a fact, whose meaning is given by the predicate associated to the relation schema. I have seen many databases whose data nobody could understand or had different opinions about (!), such as event tables whose timestamps were meaningless because three different applications had three different notions of what an "event" was. That happens precisely because a database is treated as a closet (the engineering term for that being “persistence layer”). Again, treat your database schema as a *language* instead, and design a proper one for your application domain. Then, your queries will be simple, easy to understand, and well performing.

>In that respect, I don't need four tables to hold the data across all portfolios.

Well, that depends. You may need less, you may need more. From what I may infer about your application (you haven't provided many details), your database schema might be considerably more complex that one or two or four tables.

>If the JSON strings were parsed into separate columns, then there would be different facts and relationships,

No. Again, you are thinking in physical terms (the JSON data format). Think in logical  terms: what is a portfolio? How do you identify a portfolio and how do you distinguish it from another portfolio? How do you describe it (what are the relevant attributes)? Etc. The database descends from this kind of considerations, not from the byte format of your data source.

>but, in this simple case, it just makes more work for the application to stringify in Javascript to parse in C in order to write to SQLite database (and almost never use the data in C) and then retrieve and stringify it in C to send back to JavaScript to parse again.

You may certainly store some data as JSON blobs (SQLite has some support for JSON after all), but consider such a choice carefully. Is the content of such blobs something you won't ever need to query in more or less complex ways? You may corner yourself into a situation where you have to write ad hoc code (in the worst case, at the application level) to extract information from JSON fields, when a simple SQL query would have done the job, had the data been properly organized.