I've devised the following schema:
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.
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?