When you are following a chain of relations (a vector), you end up losing a lot of performance.
> not entirely sure what you mean by declarative
Guaranteed by the database semantics, not imperative code even when it runs on the DB.
I don't want to come off sounding like I think it's a bad idea to map graphs on RDBMSs, it's just inconvenient sometimes. Usually not enough to complicate your setup, adding another database, but rarely it is.
I'm not sure though about the suggested use case here, I really don't know it enough to make a suggestion. In such cases I always pick sqlite or postgres (depending on the client model) though. You can't be too wrong with those, and you are most of the times right :)
Not to mention the crazy extensibility of pg and the stuff you can do with it.
If you meant to prevent "all inserts are now [run this stored procedure / multi-statement operation / transaction with N steps]", then yeah - I agree that runs counter to "normal" use in most cases. It's certainly dramatically more error prone. Triggers don't need this though.
re chain of relations: I haven't poked too hard in this direction in SQLite, but I do totally believe its query planner isn't too sophisticated. And though you can get pretty good data locality with fancy design around the DB, you certainly won't get good locality just by inserting a bunch of data and running queries.
Also I have no idea why a separate table is a negative / constraint of some kind. It's a natural way to model it.