SQLite Forum

General question concerning database stucture and number of databases to use.
>I'd appreciate any guidance you could provide.

Review the staples of Relational database technology. In particular, read about data integration and the distinction between schema and instances.

**Data integration**

Just consider this: usually, DBMSs do not allow you to perform queries across databases. So, if you have one database per portfolio, you cannot (easily) perform queries across all portfolios. Besides, with different databases you will most likely end up storing duplicate and/or inconsistent data. There are other reasons to prefer integrated data, but for “a rather simple tool” these two should suffice.

**Schema vs instance**

A database schema is not just a bunch of “tables”: it describes a **language** (in a very formal sense) with which you describe **facts** (by means of *tuples* in *relations*, or, informally, as  “records” in “tables”). As a rule (with few exceptions), adding a new relation schema (i.e., a “table”, in common parlance) is justified only if you need to describe a new, distinct, fact type. Concretely, when you need to add data, say, about a new portfolio, you shouldn't have to create a new table, but add data to one or more existing, properly designed, tables that accurately capture all the relevant fact types about “portfolios”. Of course, that requires determining such fact types.

So, your problem essentially amounts to how to properly model your requirements as a (one) well-designed database. There is currently (only) one (ISO/IEC/IEEE) standard for Relational database design, which is [ISO 31320-2](https://www.iso.org/standard/60614.html) (note: only the “key-style” modeling described in the standard is relevant to Relational database design). Unfortunately, the ISO document is not freely available, but the relevant parts are nearly identical to the former US government's Federal Information Processing Standard (FIPS) PUB 184, which can be found [here](https://www.idef.com/wp-content/uploads/2016/02/Idef1x.pdf). The latter also contains a detailed description of a sound **methodology** for database design. Even if you decide not to adopt IDEF1X as your modelling methodology, I'd recommend that you read at least Annex A of the FIPS document, which clearly describes how to approach database design.

>Lastly, I'd like it to be easy for users to share the portfolios they've built with others, and to incorprate those portfolios into the user's database 

I assume, from your description, that each user has its own SQLite-backed app, and that you want users to share data between their respective databases. If that is the case, when your database is well-designed—in particular, if you have devised good relational keys—that becomes (almost) trivial.