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?

1 Answer 1


Your approach is the correct one if the typical workload consists of small transactions that access only a few rows at a time (OLTP).

The extra join won't hurt, because it will be a nested loop join. Databases are optimized for stuff like that.

Any small performance loss will be outweighed by the advantage of having foreign keys to guarantee data integrity.

  • Would the same apply if I need to make this multiple times? (I.e., 2 more nested loop joins?)
    – Dimbreath
    Sep 26, 2019 at 20:41
  • 1
    Yes. But of course you don't want more tables than necessary. Sep 26, 2019 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.