Tweeted twitter.com/StackDBAs/status/1181992845240090625
2 Added tags for visibility. Modified title to reflect the nature of the scenario at issue.
| link

Setting up relationships on a gaming schema with multiple locales

1
source | link

Setting up relationships on a schema with multiple locales

I've devised the following schema:

enter image description here

Where BotLocale holds locales like en-US, es-AR, etc. GameArtifact has a primary key which is artifact_id and GameClass with locale, identifier being the primary key.

How can I properly set up a relationship between the exclusive column of GameArtifact and identifier from GameClass taking into account that identifier will be repeated multiple times per row due to many locales?

This is how GameClass table looks like:

 id | locale | identifier  |      class
----+--------+-------------+------------------
    | en-US  | knight      | Knight
    | es-AR  | knight      | Caballero

One approach I thought for doing this is to separate GameClass into two tables, one which holds the identifier alone which would be referenced from GameArtifact and another table which holds the actual translations for a GameClass.

enter image description here

The issue in this approach would be that there's an extra table I'd need to JOIN when querying.

Is this the best way to achieve this or there's a better approach when handling this type of cases?