CODEWITHSUNDEEP
sqlpostgresqlrelationshiprelation
winwin
winwin

Reputation: 1834

Symmetric relationship in postgres

I've been using Translation API, but it's recommended to cache traslations, because of unnecessary server trips, so I cache it in a postgres database.

So I created a table

CREATE TABLE translation_cache(
    src_lang_code CHAR(3),
    dst_lang_code CHAR(3),
    src VARCHAR,
    dst VARCHAR NOT NULL,
    PRIMARY KEY(src_lang_code, dst_lang_code, src)
);

Which means, that for every string src there is only one translation from src_lang_code to dst_lang_code. But this is a symmetric relation! dog (en) -> собака (ru); собака (ru) -> dog (en), and there is no other way!

Is there a beautiful way to implement such symmetric relations in postgres or should I go ugly and replicate data?

Upvotes: 1

Views: 334

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247545

There is no very pretty solution to that.

You can either search the table twice whenever you need to look up a translation, or you can add a trigger that adds a second mirror-image row whenever a row is inserted.

In the first case, you should add a unique constraint that doesn't allow the same dst for the same pair of languages.

Let me add that this translation model is overly simplistic. A word rarely corresponds to a single word in another language, so the relationship is usually not symmetric.

Upvotes: 1

Related Questions