My initial preference is for 'dim table'. The things you're specifying (hobby, mood) should not be stored in the schema, they are data and belong /in/ the tables. This design also allows you to merge all the tables together easily: just add a new column which is currently the name of the database. You may find this useful sometime in the future. However, some things about how the data is used may provide your answer.

What data do you need to read together ?

If you read something about one person, do you usually need to know everything about them ? If I need to read someone's hobby, do I probably also need to know their career ? Or is it more likely that if I read one person's hobby I need to know the hobbies of everyone else in that table ?

What's expensive in that system ?

Processing ? It runs on a portable device, or an embedded processor ? Speed is unusually important ?

Storage ? The read-only databases are accessed across a network ? The databases are stored on a tiny storage medium ?

Documentation or ease of maintenance ? Many people will read/approve/maintain your code, and must understand what it does very quickly ?

Code size ? Your code must work on many platforms, or is likely to be translated to another language soon ?

You may look at these and say "none of the above", or come up with something I didn't list. But the answers may answer your question.

Another option

Just to annoy you, I'll give you another option. Had you considered storing the attributes in JSON format, all in one column ? This would be flexible and fast in looking up everything about one person, but make it harder to find everyone who has been a jouster.

    create table person (name text, salary integer, attributes TEXT)

'ed',5,'{"career":"jouster","hobby":"running"}'
'susie',9,'{"career":"journalist","hobby":"poetry"}'

You can't actually use the above for input, because SQLite stores JSON as text, so you need to change the quotes. But it might make it easier for you to think about the option.

Regarding dynamic query building, maybe you shouldn't do that, I am surprised how many developers tend to do this when in reality, the number of queries they run against the database is quite limited.

Store your queries and reuse them, preferably in prepared form, this way you can even test them thoroughly before mixing them with your application code

I would have to calculate all combinations of when predicates, since for in expressions there can be a variable number of ? variables.

So that is not a reliable option unfortunately and hence why dynamic queries are needed. Otherwise I agree with you.

Unfortunately even with my proposed dim table, I would still not be able to prepare statements since I would use a dynamic list of variables in predicates containing in expressions.

So, that is why I didn't see any benefit to it. Yes, I read all of the dims all of the time, hence why I opted to denormalize them rather than having to continually join on dim to get the results.

Thanks for the suggestions.

Actually, I just realized one slick way of preparing statements with a variable IN clause is to insert the values into an in-memory temp table that you then query like where something in (select values from temp.values)

That way I don't have my issue of variable number of ? arguments to the prepared statement.