relation_id │ relatee_id
────────────┼───────────
1 │ x
1 │ y
> or normalize to yet another join table
This is also semantically incorrect. There needs to be exactly 2 rows there. You cannot in a declarative way, prevent that if you use this approach.
It's also a known dilemma, so we don't have to rediscover the wheel here.
Also I have no idea why a separate table is a negative / constraint of some kind. It's a natural way to model it.
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.
A simple example would be to link 3 brothers with the same relation: the worse solution would be dividing it in 3 different binary relations; the reality is that each brother is part of the “brother relation” set by way of sharing parents.
Semantic web I assume is full of such directional relations. Bidirecitonal relations are the exception. Things like "causes", "indicates", "enables", "prevents", ...
Granted, it might not be very nice to query.
What you described is, IIRC, also what some file-based graph DBs do in the background with sqlite and abstract away from you (I've seen a couple custom ones in some projects). Left with a RDBMS, you just need to do it yourself and analyze how much it costs.